0

If someone could help me understand getRange please. I have read many responses on here and the documentation here but am still confused as to how it works. (I'm a beginner coder as you can see)

How do I reference a specific row in a data set with getRange?

  • I have a table of data in A1:D10
  • I would like to copy one row A5:D5

I have tried the below but get an error. "number of rows in data does not match the number of rows in the range". Could someone explain how to reference a specific row?

 function copyPaste() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName("Sheet1");

// this is me referencing that row

  var sourceRange = source.getRange("A5:D5");

// the rest

  var data = sourceRange.getValues();
  var lr = sourceRange.getLastRow();
  var lc = sourceRange.getLastColumn();
  var target = ss.getSheetByName("Sheet2")

target.getRange(target.getLastRow()+1,1,lr,lc).setValues(data);
  }
Mardi-Louise Van Heerden
  • 133
  • 2
  • 9
  • Does this answer your question? [Sheet.getRange(1,1,1,12) what does the numbers in bracket specify?](https://stackoverflow.com/questions/11947590/sheet-getrange1-1-1-12-what-does-the-numbers-in-bracket-specify) This question has **147k** views by the way and appears as one of the top threads in google. It is really hard to miss it. – Marios Nov 11 '20 at 12:32
  • I did look at this, thank you. I tried to implement it, but could not understand why my script did not work. However I realise now (after getting an answer here) that my problem was not that I didn't understand getRange - but that I misunderstood how to reference my source range and use of getLastColumn. – Mardi-Louise Van Heerden Nov 11 '20 at 14:36

1 Answers1

1

Issue:

You should provide the number of rows and columns when calling getRange(row, column, numRows, numColumns).

You are providing the indexes of the last row and column of A5:D5 (that is, lr=5, lc=4), when you should provide the number of rows and columns of the source range (lr=1, lc=4). Because of this, the source and destination ranges don't match dimensions, and you are getting this error.

Solutions:

Therefore, this can be fixed by changing these two lines:

var lr = sourceRange.getLastRow();
var lc = sourceRange.getLastColumn();

To these ones:

var lr = sourceRange.getNumRows();
var lc = sourceRange.getNumColumns();

An alternative option would be to use the data dimensions (using length):

target.getRange(target.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

Reference:

Iamblichus
  • 18,540
  • 2
  • 11
  • 27