Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Google script Display array with rows of different sizes

I’m using ImportJSON script that is well know to run an API and retrieve data.
This works fine, I’ve used it many times before.
However this particular API I’m running returns data that has different size rows, so I get the error:

Error
Exception: The number of columns in the data does not match the number of columns in the range. The data has 10 but the range has 17.

My code (with url hidden since it contains API credentials)

dataArray = ImportJSON(url)  
shTest.getRange(1,1,dataArray.length,dataArray[0].length).setValues(dataArray);

Is there a way for me to display the results on my sheet, without looping through the array and rebuilding it so that all rows have the same amount of columns?

It’s doing what it’s supposed to do, and getting the correct data, just as I said correct data results in differing size rows.

Tags:

2

Answers


  1. 0

    From the question

    Is there a way for me to display the results on my sheet, without looping through the array and rebuilding it so that all rows have the same amount of columns?

    Yes, it’s possible, but I encourage you not to do that. If you still want to do that, you must add one row at a time instead of adding all the rows at once. Depending on your use case, you might use SpreadsheetApp.Sheet.appendRow(rowContent), use SpreadsheetApp.Range.setValues(data) or use the Advanced Sheets Service.

    Login or Signup to reply.
  2. 0

    Although I’m not sure about your actual values, from your error message, I guessed that all arrays in the 2-dimensional array dataArray do not have the same length. So, as another approach, how about the following modification?

    From:

    dataArray = ImportJSON(url)  
shTest.getRange(1,1,dataArray.length,dataArray[0].length).setValues(dataArray);

    To:

    dataArray = ImportJSON(url); 

// --- I added the below script. ref: https://github.com/tanaikech/UtlApp?tab=readme-ov-file#uniform2darray
const maxLen = Math.max(...dataArray.map((r) => r.length));
dataArray = dataArray.map((r) => [...r, ...Array(maxLen - r.length).fill(null)]);
// ---

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

    In this modification, all arrays in a 2-dimensional array, dataArray, are of the same length by the inserted script.

    Reference:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search