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?
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.