Exception: The number of columns in the data does not match the number of columns in the range ImportJson API call issue

user_10540966 0
Viewed 47

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=%7B%22proto%22%3A%5B%221%22%5D%2C%22quality%22%3A%5B%22Meteorite%22%5D%7D&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=%7B%22proto%22%3A%5B%2222%22%5D%2C%22quality%22%3A%5B%22Meteorite%22%5D%7D&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.

google-apps-script google-sheets
1 Answers

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.

Reference:

user_71086530
Related