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.
1
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:
- map()
1
Brad Jasper’s ImportJSON() is often used as a custom function to bring data directly into the spreadsheet. When a custom function returns a 2D array that has rows of varying length, Google Sheets automatically allocates a range of cells that is wide enough to hold the longest row.
So you should be able to use ImportJSON() in a formula without without any coding, like this:
=ImportJSON("https://...the long URL that gets the data...")
See the ImportJSON() GitHub page for more info.
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.