CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Christopher Birks
Christopher Birks

Reputation: 29

'Range not found' when trying to input value into first empty cell

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?

Upvotes: 1

Views: 61

Answers (1)

Marios
Marios

Reputation: 27348

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

Upvotes: 1

Related Questions