SpreadsheetApp: getRange and setValues

0
votes

I would like to use Google SpreadsheetApp to write a 2D array into a sheet. Trying

sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);

leads to the following error:

"The number of rows in the data does not match the number of rows in the range. The data has 4 but the range has 3."

How is it possible to get an error like this from this line of code? I use the dimensions of the array to specify the range in the spreadsheet, so how can it be that they don't match?

(The line is part of https://github.com/alpatania/sassafras (function appendData_ in supporting_code).)

2
javascriptarraysgoogle-apps-scriptgoogle-sheets
This simply means that the number of row in the getRange() is not equal to the number of rows that exist in setValues(). So array2d.length = 3 rows but, for some reason, array2d has 4 rows - which does seem odd. The only way to be more specific is if you would share a copy of your spreadsheet (less private or confidential data, of course). Then, we can see the actual data and also compare it to the code when array2d is/was created. – Tedinoz
You can help yourself (and us) by breaking up your multi-statement line into two parts, and logging the intermediate states. Log the values of the array lengths (and the elements of the array), log the Range dimensions, and so on. Stackdriver logging is your friend, i.e. console.log({message:"the data", arraylen: array2d.length, .....}) – tehhowch
Thanks all! Following @tehhowch's suggestion, I logged various rows of the array and found that not all rows had the same length as the first row. So carlesgg97 is on point with his answer – Alice Schwarze

2 Answers

1
votes

When calling

sheet.getRange(sheet.getLastRow() + 1, 1, array2d.length, array2d[0].length).setValues(array2d);

You are just checking the length of the first row of your 2D-Array (using array2d[0].length). However, different rows may have different lengths as well. You can see below a short fragment of code that reproduces your issue:

Issue reproduction

In order to continue troubleshooting your issue you may find the following hyperlinks relevant:

0
votes
function insert2DArrayIntoSheet() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var array=[[1,2,3],[4,5,6],[7,8,9]];
  var rg=sh.getRange(1,1,array.length,array[0].length);
  rg.setValues(array);
}