如何合并电子表格并在新文档中设置其选项卡顺序

How to merge Spreadsheets and Set their tab order in the new document

本文关键字：文档 设置 选项 顺序 何合并 合并 电子表格 新文档      更新时间：2023-09-26

我使用下面的代码在Gdrive上的文件夹中组合电子表格。我正试图弄清楚如何组合的表，但知道他们在什么顺序。例如，如果我在一个名为SS1、SS2、SS3、SS4的文件夹中有4个文件。我如何确保统一表中的4个选项卡将是SS1, SS2, SS3, SS4，而不是像我的代码目前所做的一些随机顺序。例如，我的代码可能会吐出像SS2, SS1, SS4, SS3这样的顺序。

function mergeSheets() {
  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFoldersByName(SOURCE).next();
  /* Get all spreadsheets that resided on that folder */
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  /* Create the new spreadsheet that you store other sheets */  
  var newSpreadSheet = SpreadsheetApp.create("Merged Sheets");
  /* 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); 
    }
  }      
}

您可以做的是将提取的文件的结果(您的while循环)存储在数组中，然后对数组进行排序，最后附加在数组中排序的工作表(作为数组的值排序)。

这里你的代码重写了我的想法(没有测试它，所以可能有一些bug)

function mergeSheets() {
  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFoldersByName(SOURCE).next();
  /* Get all spreadsheets that resided on that folder */
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  /* Create the new spreadsheet that you store other sheets */  
  var newSpreadSheet = SpreadsheetApp.create("Merged Sheets");
  /* Iterate over the spreadsheets over the folder */
  var spreadsheetsList = [];
  while(spreadSheets.hasNext()) {
    var sheet = spreadSheets.next();
    spreadsheetsList.push([sheet.getName(),sheet.getId()]);
  }
  spreadsheetsList.sort(function(a,b){return a[0].localCompare(b[0])});
  for(var i =0; i< spreadsheetsList.length; i++){
    /* Open the spreadsheet */
    var spreadSheet = SpreadsheetApp.openById(spreadsheetsList[i][1]);
    /* Get all its sheets */
    for(var y in spreadSheet.getSheets()) {
      /* Copy the sheet to the new merged Spread Sheet */
      spreadSheet.getSheets()[y].copyTo(newSpreadSheet); 
    }
  }      
}
相关文章：