I have the following script which uses importjson to call data from API urls and then store them into a sheet.
function test() {
let startcell = "Test!A1"
const parameters = [
{
url: 'https://api.x.immutable.com/v1/orders?buy_token_address=0xccc8cb5229b0ac8069c51fd58367fd1e622afd97&direction=asc&order_by=buy_quantity&page_size=9&sell_metadata={"proto":["1"],"quality":["Meteorite"]}&sell_token_address=0xacb3c6a43d15b907e8433077b6d38ae40936fe2c&status=active',
},
{
url: 'https://api.x.immutable.com/v1/orders?buy_token_address=0xccc8cb5229b0ac8069c51fd58367fd1e622afd97&direction=asc&order_by=buy_quantity&page_size=9&sell_metadata={"proto":["22"],"quality":["Meteorite"]}&sell_token_address=0xacb3c6a43d15b907e8433077b6d38ae40936fe2c&status=active',
},
];
const ss = SpreadsheetApp.getActive();
let data = []
parameters.forEach(spec => {
data.push(ImportJSON(spec.url));
});
data = data.flat()
ss.getRange(startcell)
.offset(0, 0, data.length, data[0].length)
.setValues(data);
}
Error: Exception: The number of columns in the data does not match the number of columns in the range. The data has 3 but the range has 24.
Explanation: This error appears when the data's columns from one url are more or less than the other ones. For example, the first URL in the above code returns 24 columns, while the second URL returns 3 columns, and this causes the error.
What I want to achieve: I want the script to work with both urls no matter the number of columns.
CodePudding user response:
From your showing script and your error message, I'm worried that in your script, the length of all columns in data
might not be the same. If my understanding is correct, how about the following modification?
From:
data = data.flat()
ss.getRange(startcell)
.offset(0, 0, data.length, data[0].length)
.setValues(data);
To:
data = data.flat()
// --- I added below script.
const max = Math.max(...data.map(r => r.length));
data = data.map(r => r.length < max ? [...r, ...Array(max - r.length)] : r);
// ---
ss.getRange(startcell)
.offset(0, 0, data.length, data[0].length)
.setValues(data);
- By this modification, the length of all columns in
data
becomes the same.