The number of columns in the data does not match ...

[Adeel Malik]

Hi all, 

I am using an external REST API which sends me a huge response and the nature of data after parsing is like below

[

[1,2,3,4], 

[1,2,3,4,5], 

[1,2,3,4,5,6]

]

i.e. each row has different length and If I use .setValues method to insert data all at once, it gives me column mismatch error: 

"Exception: The number of columns in the data does not match the number of columns in the range. The data has 5 but the range has 6. (line 4, file "test") "

Alternatively, I have used .appendRow(data) function but data returned is huge and script times out after 6 mins. Is there any solution to this problem please? 

Here is a sample program to recreate the issue: 

function test() {

   const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test Sheet");

   const data = [[1,2,3,4,5], [1,2,3,4,5], [1,2,3,4,5,6]]

   sheet.getRange(2, 1,data.length, data[2].length).setValues(data)

}

[Jean-Luc Vanhulst]

A range is always square. 

data[2] in your example is [1,2,3,4,5,6] (the last row.) But row[0[ and row[1] are 5 wide. So solve this by adding empty elements  

 const data = [[1,2,3,4,5,""], [1,2,3,4,5,""], [1,2,3,4,5,6]];

or do the getRange() .. SetValues() in a loop per array element:

   const data = [[1,2,3,4,5], [1,2,3,4,5], [1,2,3,4,5,6]];
   data.forEach( function(row, i){
      sheet.getRange(2+i, 1,1, row.length).setValues([row]);
   } );

 

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6db23314-023a-4222-9e3e-947c33fd3e24n%40googlegroups.com.

[Adeel Malik]

That's an excellent advice Jean. Thanks for that.

In case anyone needs a solution in the future, I managed to fill the rows with empty values with the following code; 

function writeToSheetDynamically() {

 const data = [[1,2,3,4,5], [1,2,3,4,5], [1,2,3,4,5,6], [1,2,3]];

  const maxLength = [];

  

  for ( let row of data ){

    maxLength.push(row.length)

  }

  const maxValue = Math.max(...maxLength)

  

  for (let row of data){

    if(row.length < maxValue) {

      let len = row.length;

    for ( let i = 0; i < maxValue - len; i++ ){

            row.push("")

        }

}

}

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2")

  sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).clear({contentsOnly: true, formatOnly: true})

  sheet.getRange(2, 1, data.length, data[0].length).setValues(data)

}

Suggestions on the code are welcome ☺️