Google Spreadsheet script error : "Cannot convert (class) ... to Object[][]"

Question

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?

Answer 1

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