javascript - Google Apps 脚本值错误

Question

每次运行代码后，我都会收到一条错误消息，指出“第 35 行的值错误”。该代码实际上一直运行一次，但随后创建了第二个名为“undefined”的文件，并且似乎是此错误的一部分。在我的代码中，我没有让脚本创建多个电子表格，我们只让它创建一个。如果我能找到解决这个问题的方法，我将不胜感激。代码如下:

function ssCreation(spreadsheetName, spreadsheetId1, sourceSheetName1,
    spreadsheetId2, sourceSheetName2,
    spreadsheetId3, sourceSheetName3,
    spreadsheetId4, sourceSheetName4,
    spreadsheetId5, sourceSheetName5,
    spreadsheetId6, sourceSheetName6,
    spreadsheetId7, sourceSheetName7,
    spreadsheetId8, sourceSheetName8,
    spreadsheetId9, sourceSheetName9,
    spreadsheetId10, sourceSheetName10,
    spreadsheetId11, sourceSheetName11) {

    var create = SpreadsheetApp.create(spreadsheetName).getId();
    var renameSheet = SpreadsheetApp.openById(create).renameActiveSheet('1');
    for (var i = 1; i < 14; i++) {
        var sheetName = 1 + i;
        var insertTheSheet = SpreadsheetApp.openById(create).insertSheet(i);
    }

    var ss1 = SpreadsheetApp.openById(spreadsheetId1);
    var ssd1 = SpreadsheetApp.openById(create);
    var sourceSheet1 = ss1.getSheetByName(sourceSheetName1);
    var sourceData1 = sourceSheet1.getDataRange().getValues();
    var destSheet1 = ssd1.getSheetByName("1");
    destSheet1.getRange(destSheet1.getLastRow() + 1, 1, sourceData1.length, sourceData1[0].length).setValues(sourceData1);

    var ss2 = SpreadsheetApp.openById(spreadsheetId2);
    var ssd2 = SpreadsheetApp.openById(create);
    var sourceSheet2 = ss2.getSheetByName(sourceSheetName2);
    var sourceData2 = sourceSheet2.getDataRange().getValues();
    var destSheet2 = ssd2.getSheetByName("Sheet2");
    destSheet2.getRange(destSheet2.getLastRow() + 1, 1, sourceData2.length, sourceData2[0].length).setValues(sourceData2);

    var ss3 = SpreadsheetApp.openById(spreadsheetId3);
    var ssd3 = SpreadsheetApp.openById(create);
    var sourceSheet3 = ss3.getSheetByName(sourceSheetName3);
    var sourceData3 = sourceSheet3.getDataRange().getValues();
    var destSheet3 = ssd3.getSheetByName("Sheet3");
    destSheet3.getRange(destSheet3.getLastRow() + 1, 1, sourceData3.length, sourceData3[0].length).setValues(sourceData3);

    var ss4 = SpreadsheetApp.openById(spreadsheetId4);
    var ssd4 = SpreadsheetApp.openById(create);
    var sourceSheet4 = ss4.getSheetByName(sourceSheetName4);
    var sourceData4 = sourceSheet4.getDataRange().getValues();
    var destSheet4 = ssd4.getSheetByName("Sheet4");
    destSheet4.getRange(destSheet4.getLastRow() + 1, 1, sourceData4.length, sourceData4[0].length).setValues(sourceData4);

    var ss5 = SpreadsheetApp.openById(spreadsheetId5);
    var ssd5 = SpreadsheetApp.openById(create);
    var sourceSheet5 = ss5.getSheetByName(sourceSheetName5);
    var sourceData5 = sourceSheet5.getDataRange().getValues();
    var destSheet5 = ssd5.getSheetByName("Sheet5");
    destSheet5.getRange(destSheet5.getLastRow() + 1, 1, sourceData5.length, sourceData5[0].length).setValues(sourceData5);

    var ss6 = SpreadsheetApp.openById(spreadsheetId6);
    var ssd6 = SpreadsheetApp.openById(create);
    var sourceSheet6 = ss6.getSheetByName(sourceSheetName6);
    var sourceData6 = sourceSheet6.getDataRange().getValues();
    var destSheet6 = ssd6.getSheetByName("Sheet6");
    destSheet6.getRange(destSheet6.getLastRow() + 1, 1, sourceData6.length, sourceData6[0].length).setValues(sourceData6);

    var ss7 = SpreadsheetApp.openById(spreadsheetId7);
    var ssd7 = SpreadsheetApp.openById(create);
    var sourceSheet7 = ss7.getSheetByName(sourceSheetName7);
    var sourceData7 = sourceSheet7.getDataRange().getValues();
    var destSheet7 = ssd7.getSheetByName("Sheet7");
    destSheet7.getRange(destSheet7.getLastRow() + 1, 1, sourceData7.length, sourceData7[0].length).setValues(sourceData7);

    var ss8 = SpreadsheetApp.openById(spreadsheetId8);
    var ssd8 = SpreadsheetApp.openById(create);
    var sourceSheet8 = ss8.getSheetByName(sourceSheetName8);
    var sourceData8 = sourceSheet8.getDataRange().getValues();
    var destSheet8 = ssd8.getSheetByName("Sheet8");
    destSheet8.getRange(destSheet8.getLastRow() + 1, 1, sourceData8.length, sourceData8[0].length).setValues(sourceData8);

    var ss9 = SpreadsheetApp.openById(spreadsheetId9);
    var ssd9 = SpreadsheetApp.openById(create);
    var sourceSheet9 = ss9.getSheetByName(sourceSheetName9);
    var sourceData9 = sourceSheet9.getDataRange().getValues();
    var destSheet9 = ssd9.getSheetByName("Sheet9");
    destSheet9.getRange(destSheet9.getLastRow() + 1, 1, sourceData9.length, sourceData9[0].length).setValues(sourceData9);

    var ss10 = SpreadsheetApp.openById(spreadsheetId10);
    var ssd10 = SpreadsheetApp.openById(create);
    var sourceSheet10 = ss10.getSheetByName(sourceSheetName10);
    var sourceData10 = sourceSheet10.getDataRange().getValues();
    var destSheet10 = ssd10.getSheetByName("Sheet10");
    destSheet10.getRange(destSheet10.getLastRow() + 1, 1, sourceData10.length, sourceData10[0].length).setValues(sourceData10);

    var ss11 = SpreadsheetApp.openById(spreadsheetId11);
    var ssd11 = SpreadsheetApp.openById(create);
    var sourceSheet11 = ss11.getSheetByName(sourceSheetName11);
    var sourceData11 = sourceSheet11.getDataRange().getValues();
    var destSheet11 = ssd11.getSheetByName("Sheet11");
    destSheet11.getRange(destSheet11.getLastRow() + 1, 1, sourceData11.length, sourceData11[0].length).setValues(sourceData11);

}

Answer 1

与其将 23 个参数传递给函数来创建新电子表格，然后从其他 11 个电子表格复制数据，为什么不将复制另一个函数外包出去，然后将源工作表信息传递给该函数呢？这样，您只需针对每个复制操作的代码部分进行调试，而不是当前版本中的 11 个重复项。下面我整理了一种实现此目的的方法:重复的 6 行数据复制代码现在是 copyData() 函数中的 5 行。

我们创建目标电子表格，并在将电子表格对象传递给 copyData() 时仅引用它一次。数组 sheetsList[] 是一种键=>值对列表。因此，为了从源复制到目标，我们为 sheetsList[] 中的每个 key=>value 对调用一次 copyData() 。我们将“spreadsheetIdN”(键)、“sourceSheetNameN”(值)与 destinationSpreadsheet 一起传递给 copyData() 对象以及目标电子表格中将接受来自源的数据的工作表的名称。

希望这有帮助。

function copyData(sourceSpreadsheetID, sourceSheetName, destSpreadsheet, destSheetName){
    // sourceSpreadsheetID - ID of the source spreadsheet (string)
    // sourceSheetName - name of the sheet in the source spreadsheet (string)
    // destSpreadsheet - the destination spreadsheet object
    // destSheetName - name of the destination sheet (string)
    var srcSS = SpreadsheetApp.openById(sourceSpreadsheetID);
    var sourceSheet = srcSS.getSheetByName(sourceSheetName);
    var sourceData = sourceSheet.getDataRange().getValues();
    var destSheet = destSpreadsheet.getSheetByName(destSheetName);
    destSheet.getRange(destSheet.getLastRow() + 1, 1, sourceData.length, sourceData[0].length).setValues(sourceData);
}

function doDataCopy(){
  // create the destination spreadsheet & store it as a Spreadsheet object that we can pass to copyData()
  var destinationSpreadsheet = SpreadsheetApp.create("a spreadsheet");
  // the source spreadsheets & sheets as sort of (key, value) pairs
  var sheetsList = ["spreadsheetId1", "sourceSheetName1",
                    "spreadsheetId2", "sourceSheetName2",
                    "spreadsheetId3", "sourceSheetName3",
                    "spreadsheetId4", "sourceSheetName4",
                    "spreadsheetId5", "sourceSheetName5",
                    "spreadsheetId6", "sourceSheetName6",
                    "spreadsheetId7", "sourceSheetName7",
                    "spreadsheetId8", "sourceSheetName8",
                    "spreadsheetId9", "sourceSheetName9",
                    "spreadsheetId10", "sourceSheetName10",
                    "spreadsheetId11", "sourceSheetName11"
                    ];
  for(var i = 0, lim = (sheetsList.length / 2); i < lim; ++i){
    destinationSpreadsheet.insertSheet("Sheet" + (i+2), i+1); // <= we have to skip the name "Sheet1" as the newly created sheet already has a "Sheet1"
    // As we're treating sheetsList[] as (key,value) pairs, the index of each 'key' is i*2 & each 'value' is (i*2)+1
    // Also we can pass the destinationSpreadsheet object as is & copyData() can work on it directly
    copyData(sheetsList[i*2], sheetsList[(i*2)+1], destinationSpreadsheet, "Sheet" + (i+1)); 
  } 
}