Search code examples
javascriptgoogle-apps-scriptgoogle-apps

How to copy the values in a row from one gsheet and add it to another gsheet


I feel like i'm missing something very simple here. All i'm having trouble with is getting the values of the row I want from the specified file name and adding the into the active spreadsheet. I know the culprit is between lines 67 and 73, because the error Gsheets gives me is a "can not find function 'getId'". Any help is appreciated.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Master Analysis Functions')
  .addItem('Update Sheet', 'updateSheet')
  .addSeparator()
  .addSubMenu(ui.createMenu('Sub-menu')
              .addItem('Second item', 'menuItem2'))
  .addToUi();
}

function updateSheet() {
  
  /* Top Level Google Drive Folder ID & General Settings */
  var TopDrivefolderId = "0B2rN5b8fW77ldXZXOXFLZGlSamc";
  var BaseNumberOfFilesInFolder = 5;
  var filesInFolderBeforeTemplate = 4;
  /* End General Settings */
  
  var parentFolder = DriveApp.getFolderById(TopDrivefolderId);
  var childFolders = parentFolder.getFolders();
  
  while(childFolders.hasNext()) {
    var child = childFolders.next();
    //    Logger.log(child.getName() + " |Drive_ID: " + child.getId());
    var folderId = child.getId();
    // **the folderId Variable is also the folder ID,
    //    hence they are used interchangeably **   
    
    /* Name of combined Spreadsheet*/
    //var tradeName = Drive App.getFolderById(folderId).getName();
    var tradeName = DriveApp.getFolderById(folderId).getName();
    
    /* Check if a file needs to be created or if one already exists*/  
    var theFolder = DriveApp.getFolderById(folderId);
    var files = theFolder.getFiles();
    var fname = theFolder.getName();
    
    var cnt = 0;
    var file;
    
    /* Check the quantity of files in a folder to see if you need to combine the sheets and create a file*/  
    while (files.hasNext()) {
      cnt++;
      file = files.next();
      //Logger.log(file.getName());
      if (cnt > BaseNumberOfFilesInFolder) {
        Logger.log("File already exists")
        break;
      } 
      ;
    };
    
    // If a file needs to be created this checks it and creates it
    Logger.log(cnt + " is the number of files '"+ tradeName+ "' has.");
    if (cnt > BaseNumberOfFilesInFolder){     
      Logger.log("The Data for " +tradeName+ " is already in the sheet.")
    } else if(cnt == filesInFolderBeforeTemplate){
      Logger.log("ERROR: You need to run the template adder script to add the template to " +tradeName)
      var sheet = SpreadsheetApp.getActiveSheet();
      sheet.appendRow(["ERROR: You need to run the template adder script to add the template to " +tradeName]);
      continue;
    }else{
       /* Create the new spreadsheet to verify this trade is in the master sheet */ 
      var newSpreadSheet = SpreadsheetApp.create("Added to Master");
      //var spreadSheets = folderId.getFilesByType("application/vnd.google-apps.spreadsheet");
      var dataTemplateSheet = theFolder.getFilesByName(tradeName);
      var dataTemplateID = dataTemplateSheet.getId();
      var sheetDataName = getSheet(dataTemplateID);
      var data = sheetDataName.getDataRange('A20:AE68').getValues();

      var sheet = SpreadsheetApp.getActiveSheet();
      sheet.appendRow([tradeName,data]);
      
      
      /* In order to move the file to the folder we want and because 
          google considers the SpreadSheet a Google Spreadsheet
          instead of a file, we have to convert the SpreadSheet to a file in
          order to move it.Thats what the next 2 lines of code do.*/
          var getNewSSid = newSpreadSheet.getId();
          var SStoGFile = DriveApp.getFileById(getNewSSid);
          /* Actually moving the file*/
          DriveApp.getFolderById(folderId).addFile(SStoGFile);
          
          /* Deleting the duplicate file that was created in the process*/
          var rootFolder = DriveApp.getRootFolder();
          DriveApp.getRootFolder().removeFile(SStoGFile);
      
    }  
    getSubFolders(child); 
    
  };

};  
  
  function getSubFolders(parent) {
    parent = parent.getId();
    var childFolder = DriveApp.getFolderById(parent).getFolders();
    while(childFolder.hasNext()) {
      var child = childFolder.next();
      Logger.log(child.getName());
      getSubFolders(child);
    }
    return;
  }
  /* End iterate through Sub Folders */
  
  
  function menuItem2() {
    SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
    .alert('You clicked the second menu item!');
  };


Solution

  • getFilesByName() returns a FileIterator.

    If you are certain there will always be just one file with that name you can

    var dataTemplateSheet = theFolder.getFilesByName(tradeName).next();
    

    Otherwise you'll have to loop through them with while (dataTemplateSheet.hasNext()) and somehow determine which is the correct one.