javascript - 如何创建一个新的 gsheet 并其中已有脚本？

Question

我有一个系统，可以自动将文件上传到 Gdrive，Gdrive 会搜索这些文件，将它们组合起来，并将模板表添加到组合表中。

在模板 gsheet 中，我需要能够引用其他选项卡内的单元格，而不使用选项卡名称(选项卡名称将一直更改)。我知道如何做到这一点的唯一方法是在 gsheet 中使用 google apps 脚本。问题是我生成模板的方式是打开模板并将所有值复制到新创建的组合 gsheet 中的新选项卡，并且它不会复制 google apps 脚本。

即使我可以使用代码自动将脚本添加到新工作表中，我仍然需要该脚本在该工作表上自动运行，因为我有另一个主工作表，它查找所有新创建的文件并添加来自所有文件的摘要信息他们。

这是我的 Gdrive 脚本(如果有帮助的话)。

/* Head Master Info >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> */

/* Top Level Google Drive Folder ID */
var TopDriveFolderID = "0B2rN5b8fW77ldXZXOXFLZGlSamc";
var BaseNumberOfFilesInFolder = 4;
var NewSSAnalyzerSheetId = "1VyL8XL1x41H6yetcrwfUhVcch-DUsngY4-bHCbo-_xM";

/* End Head Master Info >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

/* Body >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/

/* Functions to iterate through all sub folders */
function listFolders() {
  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 **
    
    /* SpreadSheet Combiner >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/    
    
    /* Name of combined Spreadsheet*/
    var newSpreadSheetName = DriveApp.getFolderById(folderId).getName();

    /* Retrieve the desired folder */
    var myFolder = DriveApp.getFolderById(folderId);
    
    /* Get all spreadsheets that resided on that folder */
    var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
    var spreadSheetName = myFolder.getName();
    //    Logger.log(spreadSheetName);
    
    /* 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 '"+ newSpreadSheetName+ "' has.");
    if (cnt > BaseNumberOfFilesInFolder){     
      Logger.log("No file needs to be created")
    }else{
      
      /* Create the new spreadsheet that you store other sheets */ 
      var newSpreadSheet = SpreadsheetApp.create(newSpreadSheetName);
      
      
      /* Iterate over the spreadsheets over the folder */
      while(spreadSheets.hasNext()) {
        
        var sheet = spreadSheets.next();
        
        /* Open the spreadsheet */
        var spreadSheet = SpreadsheetApp.openById(sheet.getId());
        
        
        /* Get all its sheets */
        for(var y in spreadSheet.getSheets()) {
          
          /* Copy the sheet to the new merged Spread Sheet */
          spreadSheet.getSheets()[y].copyTo(newSpreadSheet);
          
          
          /* 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);
          
        }
      } 
      
      /*Code to add our base analysis template to the new combined template that was created;*/
      //Grabs the template
      var sss = SpreadsheetApp.openById(NewSSAnalyzerSheetId); //replace with source ID
      var ss = sss.getSheetByName('Analysis Tab'); //replace with source Sheet tab name
      var range = ss.getRange('A1:E6'); //assign the range you want to copy
      var data = range.getValues();
      
      //Copies the template info into the current newly created sheet
      var tssId = newSpreadSheet.getId();
      var tss = SpreadsheetApp.openById(tssId); //replace with destination ID
      var ts = tss.getSheetByName('Sheet1'); //replace with destination Sheet tab name
      ts.getRange(1,1,6,5).setValues(data); //you will need to define the size of the copied data see getRange()
      

      Logger.log("New File was created with the name - " + newSpreadSheetName)
    }
  };
  
  
  /* End SpreadSheet Combiner >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>*/       
  
  
  getSubFolders(child); 
  
}


 /* Necessary Function to help the above loop through the sub folders */
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 */

Answer 1

不使用 SpreadsheetApp.create()，而使用 DriveApp.getFileById().makeCopy() 怎么样？然后，当您制作电子表格时，您就可以在电子表格中找到您想要的任何脚本。