Google Script/Spreadsheet issue, here.
Am trying to copy data from one cell and put it into the first empty cell in column E. When I perform the following, I get ‘Range not found’ on line 15:
function operationsLag() { var report = SpreadsheetApp.getActiveSheet(); var reportValues = report.getDataRange().getValues(); var lastRow = getFirstEmptyRow('E'); var income = "Income"; for (var i = 0; i < reportValues.length; i++) { var row = reportValues[i]; if (row[0] == income) { report.getRange(lastRow).setValue(row[1]); } } } function getFirstEmptyRow(columnLetter) { var rangeA1 = columnLetter + ':' + columnLetter; var report = SpreadsheetApp.getActiveSheet(); var column = report.getRange(rangeA1); var values = column.getValues(); var ct = 0; while ( values[ct][0] != "" ) { ct++; } return (ct+1); }
I’ve tried replacing report.getRange(lastRow).setValue(row[1]);
with something like report.getRange('E5').setValue(row[1]);
and it works fine, so there must be something wrong with my find empty cell/row function.
Can anybody help at all?
Advertisement
Answer
Issue:
The getRange(a1Notation) function with one argument accepts a string
and that is the reference cell notation e.g. E5
and this is why getRange('E5')
works correctly.
On the other hand lastRow
is not an a1Notation
but a single integer
number. The script can’t find which cell you are referring to from something like that getRange(2)
because that can be any column (if 2
is assumed to be a row number).
Solution:
You can use template literals to construct the a1Notation
based on lastRow
.
Replace:
report.getRange(lastRow).setValue(row[1]);
With:
report.getRange(`E${lastRow}`).setValue(row[1]);
or use the getRange(row, column) version where 5
is column E
(the 5th
column).
report.getRange(lastRow,5).setValue(row[1]);