Apps Script中getLastRow()报错：类型化列单元格不允许此操作

我有一个30行8列的表格，通过Apps Script编写的sync函数同步'Import Students'和'Students'两个工作表。对比Students表，Import Students表存在一行新增数据和一行已删除数据。

运行代码时，第一个for循环可正常将新学生数据添加到Students表，但在执行删除操作前，调用importrows = importSheet.getLastRow();时触发错误：

"This operation is not allowed on cells in typed columns."

但在代码开头调用完全相同的importSheet.getLastRow();语句却没有问题。完整代码如下：

function sync() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var importSheet = ss.getSheetByName('Import Students');
  var studentSheet = ss.getSheetByName('Students');
  var cell = importSheet.getRange("B2").getValue();
  if (cell == "") {
    ui.alert("No data to synchronize."); // maybe importrange has not loaded the data yet
    return;
  }
  var importrows = importSheet.getLastRow();
  var studentrows = studentSheet.getLastRow();
  var importRange = importSheet.getRange(2, 4, importrows - 1, 2); // range of all names and telnrs of import data
  var studentRange = studentSheet.getRange(2, 4, studentrows - 1, 2); // range of  all names and telnrs of student data
  var copyRange;
  var import_data = importRange.getValues();
  var student_data = studentRange.getValues();
  var searchnr;
  var laststudentrow;
  var maxstudentrow;
  var found_row;
  var i;
  var stepcell;
  var validList = [];
  var cursus;
  var rule;
  var newstudents = 0;
  var deletedstudents = 0;
  var updatedstudents = 0;
  var rowchanged = false;
  var coursechanged = false;
  var importrowRange;
  var studentrowRange;
  var importrow = [];
  var studentrow = [];

  var cur_date = new Date();
  var cur_date_str = Utilities.formatDate(cur_date, "GMT+1", "dd-MM-yyyy")

  //check and add new students

  for (i = 0; i < importrows - 1; i++) {
    searchnr = import_data[i][1];
    found_row = array_search(student_data, searchnr, 2);
    if (found_row == -1) { // new row found
      // add row to students sheet
      copyRange = importSheet.getRange(i + 2, 1, 1, 8);
      laststudentrow = studentSheet.getLastRow();
      maxstudentrow = studentSheet.getMaxRows();
      if (laststudentrow == maxstudentrow) {
        studentSheet.insertRowsAfter(maxstudentrow, 1); // add 1 row to sheet
      }
      copyRange.copyTo(studentSheet.getRange(laststudentrow + 1, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      newstudents = newstudents + 1;
      stepcell = studentSheet.getRange(laststudentrow + 1, 8); // cell met Step;
      cursus = studentSheet.getRange(laststudentrow + 1, 7).getValue();
      validList = getValidRangeForCourse(cursus);
      if (validList.length > 0) {
        rule = SpreadsheetApp.newDataValidation().requireValueInList(validList, true).build();
        stepcell.setDataValidation(rule);
      } else {
        stepcell.clearDataValidations();
      }
    }
  }

  //check and add delete students, that are not longer in the registration
  //check and update existing students

  //importrows = importSheet.getLastRow();  
  studentrows = studentSheet.getLastRow();
  importRange = importSheet.getRange(2, 1, importrows - 1, 7); // range of all names and telnrs of import data
  studentRange = studentSheet.getRange(2, 1, studentrows - 1, 7); // range of  all names and telnrs of student data
  import_data = [];
  student_data = [];
  import_data = importRange.getValues();
  student_data = studentRange.getValues();

  for (i = studentrows - 1; i > 0; i--) { // check rows from bottom to top
    searchnr = student_data[i - 1][4];
    found_row = array_search(import_data, searchnr, 5);
    if (found_row == -1) { // student not found
      // delete student from students sheet
      studentSheet.deleteRow(i + 1);
      deletedstudents = deletedstudents + 1;
    }
    else {
      // check if existing student data has changed
      rowchanged = false;
      coursechanged = false;
      for (j = 0; j < 8; j++) {
        if (student_data[i - 1][j] != import_data[found_row][j]) {
          rowchanged = true;
          if (j == 6) coursechanged = true;
        }
      }
      if (rowchanged) {
        importrowRange = importSheet.getRange(found_row + 2, 1, 1, 8);
        importrow = importrowRange.getValues();
        studentrowRange = studentSheet.getRange(i + 1, 1, 1, 8);
        studentrowRange.setValues(importrow);
        updatedstudents = updatedstudents + 1;
      }
      if (coursechanged) { // wijzig stepvalidatie
        stepcell = studentSheet.getRange(i + 1, 8); // cell met Step;
        cursus = import_data[found_row][6];
        validList = getValidRangeForCourse(cursus);
        if (validList.length > 0) {
          rule = SpreadsheetApp.newDataValidation().requireValueInList(validList, true).build();
          stepcell.setDataValidation(rule);
        } else {
          stepcell.clearDataValidations();
        }
      }
    }
  }
  ui.alert("Result of synchronization:\nNew students = " + newstudents + "\nUpdated students = " + updatedstudents + "\nDeleted students = " + deletedstudents);
}

问题原因与解决方法 #

原因分析 #

这个错误的核心是操作触发了Google表格中带有类型约束的列限制，比如列设置了严格的数据类型（日期、数字、特定选项），或者是通过IMPORTRANGE导入的表格自带源表的类型规则。代码开头调用getLastRow()没问题，但新增数据后再次调用报错，本质是：

• 同步过程中仅修改Students表，Import Students表的行数并未变化，重新调用getLastRow()属于冗余操作
• 冗余的getLastRow()调用可能因表格内部状态（比如类型列的临时锁定）触发冲突

解决步骤 #

1. 复用初始获取的行数变量
   直接删除注释掉的importrows = importSheet.getLastRow();语句，因为Import Students表在同步流程中不会被修改，开头获取的importrows完全可用。

2. 修复范围列数错误
   删除阶段的importRange和studentRange定义中，列数写的是7，但表格实际是8列，修正为8确保获取完整数据：

   importRange = importSheet.getRange(2, 1, importrows - 1, 8);
   studentRange = studentSheet.getRange(2, 1, studentrows - 1, 8);
   

3. 检查Import Students表的列约束
   打开Import Students表，选中所有列，右键选择「数据验证」，确认没有意外添加的严格类型限制；如果是IMPORTRANGE导入的表格，确保源表的列类型不会导致跨表操作冲突。

修改后的关键代码片段 #

//check and add delete students, that are not longer in the registration
//check and update existing students

// 移除冗余的getLastRow()调用
studentrows = studentSheet.getLastRow();
// 修正列数为8，匹配表格实际结构
importRange = importSheet.getRange(2, 1, importrows - 1, 8); 
studentRange = studentSheet.getRange(2, 1, studentrows - 1, 8);
import_data = importRange.getValues();
student_data = studentRange.getValues();

内容的提问来源于stack exchange，提问作者Reyer Sneller