google-sheetsgoogle-apps-script

getLastRow() in Apps Script returns error: "This operation is not allowed on cells in typed columns."


I have a simple sheet with 30 rows and 8 columns and getLastRow() returns this strange error:

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

My code below is a function that syncs two sheets.
Compared to the studentsheet, the importsheet has one new row and a deleted row.

When I run the code the new student is correctly added to the studentsheet using the first for loop.
Then the code wants to continue with deleting the deleted row.
But before I could do anything the line

importrows = importSheet.getLastRow(); 

Generates the error:

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

In the first lines of the code I'm using the same line of code without a problem:

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);
}

Could someone help me?


Solution

  • importrows = importSheet.getLastRow();

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

    The error you quote doesn't sound like what you'd get from the trying to access the Sheet.getLastRow() method. It is possible that the error is caused by another operation and thrown later when a cache is flushed.

    Remove any tables and merged cells you have in the spreadsheet and see if the code works then.