Google Sheet Script - Applying 2d array with different size sub arrays to Sheet-CodePudding

I've parsed a json response from an API to a 2d array and now that I've built the array I want to display it on my sheet however This is my dataarray It has 413 rows and those rows have varying amounts of data (row 0 is the header row with 67 fields, but not every row has all 67 fields of data in it)

This is the code I'm using to try and write the data to my sheet (shProductData is a variable I defined earlier in the code to identify my sheet)

shProductData.getRange(1,1,dataArray.length,dataArray[0].length).setValues(dataArray);

However I get the error:

Exception: The number of columns in the data does not match the number of columns in the range. The data has 40 but the range has 67.

It writes the header row to my sheet first but then fails on the next one. Is there any way around this? Or am I going to somehow make my sub arrays all be 67 in size?

CodePudding user response：

You can add empty cells at the end of short rows in the data this way:

var data = [
    [1,2,3],
    [4,5],
    [6,]
]

// get max length of rows in the data
var max_length = Math.max(...data.map(x => x.length));

// add empty cells at end of short rows
data.forEach(row => { while (row.length < max_length) row.push('') } )

console.log(data); // output: [ [ 1, 2, 3 ], [ 4, 5, '' ], [ 6, '', '' ] ]