遍历Google Spreadsheet中的工作表并使用getSheetName() [英] Looping through sheets in Google Spreadsheet and using getSheetName()

问题描述

我正在编写一个脚本，以循环浏览一个电子表格中的每个工作表，并将数据从特定单元格复制到另一电子表格中的相应工作表中.但是，我在以下代码的第18行出现错误，表明它无法调用null的getLastRow方法.我使用了几行Logger.log行来检查我的变量，并查看targetSheet返回为null.有什么建议可以解决我的问题吗?

I'm writing a script to loop through each sheet in one spreadsheet and copy data from specific cells into a corresponding sheet on another spreadsheet. I am getting an error on line 18 of the below code, however, stating that it can't call the getLastRow method of null. I used a couple of Logger.log lines to check my variables and see that targetSheet is coming back as null. Any advice on what I've got wrong?

//Export each sheet's daily data to another sheet *Test Version*
function exportReports() {
  var sourceSS = SpreadsheetApp.getActiveSpreadsheet();
  //Open Back Production Record *Test Version*
  var targetSS = SpreadsheetApp.openById("1ZJKZi-UXvqyGXW9V7KVx8whxulZmx0HXt7rmgIJpUY4");
  var allSourceSheets = sourceSS.getSheets();

  //For-Loop to loop through hourly production sheets, running the move data for-loop on each
  for(var s in allSourceSheets){

    var loopSheet = allSourceSheets[s];
    var loopSheetName = loopSheet.getSheetName();
    var targetSheet = targetSS.getSheetByName(loopSheetName);
    Logger.log(s);
    Logger.log(loopSheet);
    Logger.log(targetSheet);
    Logger.log(loopSheetName);
    var targetRow = targetSheet.getLastRow()+1;
    var currentDate = Utilities.formatDate(new Date(), "GMT-5", "MM/dd/yy");

    targetSheet.getRange(targetRow, 1).setValue(currentDate);
    //For-Loop to move data from source to target
    for(var i=6;i<=10;i++){
      var sourceRange = sourceSheet.getRange(i, 2);
      var targetRange = targetSheet.getRange(targetRow, i-4);
      var holder = sourceRange.getValue();

      targetRange.setValue(holder);
    }
  }    
}

推荐答案

根据

Per the documentation on getSheetByName, if the target sheet name does not exist, then you get null as a return value.

getSheetByName(name)
返回具有给定名称的工作表.

getSheetByName(name)
Returns a sheet with the given name.

如果多张纸具有相同的名称，则返回最左边的一张.如果没有具有给定名称的图纸，则返回null.

If multiple sheets have the same name, the leftmost one is returned. Returns null if there is no sheet with the given name.

因此，目标工作簿中不存在名称为loopSheetName指定的所需工作表.也许有人在源工作簿中创建了新工作表，或重命名了现有工作表.

So, the desired sheet with name specified by loopSheetName does not exist in the target workbook. Perhaps someone has created a new sheet, or renamed an existing sheet in the source workbook.

您没有要求它，但是您也可以通过以下方式提高复制代码的性能:将输入读取为多行范围数组，创建一个行数组以保存结果，然后将其写入一次:

You haven't asked about it, but you can improve the performance of your copy code as well, by reading the inputs as a multi-row range array, creating a row array to hold the results, and writing that once:

var sourceData = sourceSheet.getRange(6, 2, 5, 1).getValues(); // (6,2) through (10, 2)
var output = [];
// Transpose row array to column array (could use other functions, but this is easier to understand)
for(var i = 0; i < sourceData.length; ++i) { output.push(sourceData[i][0]); }
targetSheet.getRange(targetRow, 2, 1, output.length).setValues([output]); // i0 = 6 -> 6 - 4 = 2

这篇关于遍历Google Spreadsheet中的工作表并使用getSheetName()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！