Google Apps Script, Google Sheets
Have you ever experienced this warning error when trying to set values or paste a range of values back into your Google Sheet with Google Apps Script?
The number of columns in the data does not match the number of columns in the range. The data has 6 but the range has 9. (line 52, file “test”)
Yeap. One of your row lengths is not as long as the width of the column range you set.
This generally occurs when you are manipulating data into a new format before setting it back into your Google Sheet.
Let’s look like some dodgy data I’ve prepared that I want to paste or set backing my spreadsheet.
|
1 2 3 4 5 6 7 8 9 |
var dodgy2dArray = [ [1,2,3,4,5,6], [1,2,3,4,5,6,7,8,9], [1,2,3,4,5,6], [1,2,3], [1,2,3,4,5,6,7,8], [1,2,3,4,5,6,7,8,9], [1] ]; |
As you can see, I’ve got a 2d array for data with 7 rows and with a max row width of 9. This means our data will extend to nine columns at its max.
Now, your first instinct might be that if I take a few chips out of square peg, it should still fit into a square hole. I mean, I’m only subtracting from the square, right?
Then you go off blazing a path through the code, hoody hanging low over your head, punching away at your bespoke mechanical keyboard.
|
1 2 3 4 5 6 |
function stoopid(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getRange(1,1,7,9); range.setValues(dodgy2dArray); }; |
With victory within your grasp, you hit run and your world comes crashing down.
