google-apps-script - 根据列值将 Google 表格拆分为多个选项卡

Question

我使用了先前的答案（感谢 kessy！）根据列中的值将 7000 左右的行拆分为 40 个左右不同的选项卡。我在另一个几乎相同的文件上运行了相同的脚本，我收到错误“TypeError：无法读取 null 的属性 'getRange'（第 5 行，文件“代码”）”。我尝试了一个大大简化的文件并得到了同样的错误。非常感谢任何帮助使其正常工作。

        function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // This var will contain all the values from column C -> Room
  var columnRoom = sheet.getRange("C:C").getValues();

  // This var will contain all the rows
  var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

  //Set the first row as the header
  var header = rows[0];

  //Store the rooms already created
  var completedRooms = []

  //The last created room
  var last = columnRoom[1][0]


  for (var i = 1; i < columnRoom.length; i++) {    

    //Check if the room is already done, if not go in and create the sheet
    if(!completedRooms.includes(columnRoom[i][0])) {

      //Set the Sheet name = room (except if there is no name, then = No Room)
      if (columnRoom[i][0] === "") {
        var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("No Room");
      } else {
        var currentSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(columnRoom[i][0]);
      }


      //append the header
      currentSheet.appendRow(header);
      currentSheet.appendRow(rows[i]);
      completedRooms.push(columnRoom[i][0])
      last = columnRoom[i][0]
    } else if (last == columnRoom[i][0]) {

    // If the room's sheet is created append the row to the sheet


      var currentSheet = SpreadsheetApp.getActiveSpreadsheet()
      currentSheet.appendRow(rows[i]);
    }

  }

}

标签： google-apps-scriptgoogle-sheets

Answer 1

我不确定您的目标到底是什么，但根据您收到的错误消息，您似乎没有正确获得活动表。相反，我建议您按名称指定工作表。假设您要获取的工作表的所需名称是Sheet1。然后，在函数的第一行中，您可以替换它：

var sheet = SpreadsheetApp.getActiveSheet();

有了这个：

var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');

我还通过删除所有不必要的SpreadsheetApp.getActiveSpreadsheet()调用对您的代码进行了一些优化：

function myFunction() {
  
  var ss = SpreadsheetApp.openById("SpreadsheetId");
  var sheet = ss.getSheetByName('Sheet1');

  // This var will contain all the values from column C -> Room
  var columnRoom = sheet.getRange("C:C"+sheet.getLastRow()).getValues();

  // This var will contain all the rows
  var rows = sheet.getDataRange().getValues();

  //Set the first row as the header
  var header = rows[0];

  //Store the rooms already created
  var completedRooms = []

  //The last created room
  var last = columnRoom[1][0]

  for (var i = 1; i < columnRoom.length; i++) {    

    //Check if the room is already done, if not go in and create the sheet
    if(!completedRooms.includes(columnRoom[i][0])) {

      //Set the Sheet name = room (except if there is no name, then = No Room)
      if (columnRoom[i][0] === "") {
        var currentSheet = ss.insertSheet("No Room");
      } else {
        var currentSheet = ss.insertSheet(columnRoom[i][0]);
      }

      //append the header
      currentSheet.appendRow(header);
      currentSheet.appendRow(rows[i]);
      completedRooms.push(columnRoom[i][0])
      last = columnRoom[i][0]
    } else if (last == columnRoom[i][0]) {

    // If the room's sheet is created append the row to the sheet
      sheet.appendRow(rows[i]);
    }
  }
}