Reputation: 646
I have a on dimensional array of 13 dates that I'd like to put in a spreadsheet row.
When I debug this code:
if (arrDates.length > 0) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var destSheet = spreadsheet.getSheetByName("Employee Capacity Detail");
destSheet.getRange("C2").offset(0,0,arrDates.length).setValues(arrDates);
}
I get this error message: "Cannot convert (class)@7c02952c to Object[][]." on this line:
destSheet.getRange("C2").offset(0,0,arrDates.length).setValues(arrDates);
I've also tried this:
destSheet.getRange(1, 3, 1, 14).setValues(arrDates);
and this:
var destRange = destSheet.getRange("C2:P2");
destRange.setValues(arrDates);
And get the same error.
In the debugger I can see that arrDates is populated with 14 dates as expected.
A google search for this error message has not turned up anything. Does anyone have any ideas for why I am getting this error, or what I might do to troubleshoot further?
Upvotes: 6
Views: 10582
Reputation: 1
Make sure that the number of rows in the array being sent to setValues matches those indicated in getRange.
I got this error when I had 2 too many rows in my array. Not sure why I didn't get the OTHER message I sometimes see about the mismatch between the range height and the number of rows in the array.
Upvotes: 0
Reputation: 24629
https://developers.google.com/apps-script/reference/spreadsheet/range#setValues(Object)
The key here is that when you are populating (or retrieving data from) a spreadsheet range, you must always "set" a two-dimensional array, even if the range is one row high. In Javascript, this will be an "array of array(s)", where the outer array represents the rows, and the inner array(s) represents the cells in each row.
Presumably your arrDates array was like this:
[date1, date2, date3...]
when it needed to be like this:
[[date1, date2, date3...]]
which you achieved with the new Array(arrDates)
statement, or you could also just use:
destSheet.getRange(1, 3, 1, 14).setValues([arrDates]));
Upvotes: 16
Reputation: 646
Here is what I had to do to get it to work:
destSheet.getRange(1, 3, 1, 14).setValues(new Array(arrDates));
Any explanations welcome!
Upvotes: 3