Home > Enterprise >  Exception: The number of columns in the data does not match the number of columns in the range Impor
Exception: The number of columns in the data does not match the number of columns in the range Impor

Time:05-22

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.

Reference:

  • Related