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

Question

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?

Yuri Khristich · Accepted Answer · 2022-02-26 13:27:24Z

1

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, '', '' ] ]

answered Feb 26, 2022 at 13:27

Yuri Khristich

14.6k3 gold badges11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Liew Over a year ago

Amazing, that worked perfectly thank you!! I can't figure out, what does x=>x.length part of the math function do? (would like to understand how the code works)

Yuri Khristich Over a year ago

data.map(x => x.length) returns the array of lengths of all rows of the data: [3,2,1]. Then it takes max value from the array with Math.max(3,2,1) -> 3

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related