CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
L.Klein
L.Klein

Reputation: 56

Google app script load unknown dimensioned array into a spreadsheet gets contradictory messages

  let mysteryArr = [["question1","","","","","","","",""]
    ,["apple","banana","cantalope","date","grapes","apricot","tangerine","orange","pineapple","prune"]
    ,[24,16,8,20,8,8,4,16,12,4]]

  let maRows, maCols;
  if (mysteryArr.length != '')   {
    maRows = mysteryArr.length;
    console.log('num rows: ' + maRows);
  }  else  {
    console.log('No array');
    return;
  }

  for (i = 0 ; i < maRows ; i++ ) {
    if (mysteryArr[i].length != '')   {
      maCols = mysteryArr[0].length;
      console.log('num cols in row ' + i + ' is ' + maCols);
      console.log(JSON.stringify(mysteryArr[0] ) );
    }  else  {
      console.log('Array was 1D');
      return;
    }
  }
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1');
  if ( sheet == '' )    {
    ss.insertSheet('Sheet1');
  }  else  {
    sheet.clear();
  }

Message details Exception: The number of columns in the data does not match the number of columns in the range. The data has 9 but the range has 10. (line 47, file "array2Sheet1")

How can these both be true? What is the correct way to determine the dimensions of an unknown array and load it into a spreadsheet?

Upvotes: 1

Views: 125

Answers (3)

Cooper
Cooper

Reputation: 64040

Equalize Length of All Rows of the array:

function equalize(arr) {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  let m=0;
  arr.forEach(r=>{if(r.length>m)m=r.length;});
  let orr=arr.map(r=>{while(r.length<m){r.push('');}return r;});
  sh.getRange(1,1,orr.length,m).setValues(orr);
}

Upvotes: 1

Marios
Marios

Reputation: 27348

Solution:

The first row in your data has 9 elements but it should have 10 as the other two rows. Adding a new element will fix the issue:

   let mysteryArr = [["question1","","","","","","","","",""] // a new "" is added here
    ,["apple","banana","cantalope","date","grapes","apricot","tangerine","orange","pineapple","prune"]
    ,[24,16,8,20,8,8,4,16,12,4]]
      
  const shtRng = sheet.getRange(1, 1, mysteryArr.length, mysteryArr[0].length);
  shtRng.setValues(mysteryArr);

Issue:

You can also see the issue in the lengths.

  • mysteryArr[0].length has a length of 9.

But the greatest size in your array is 10 because the other rows contain 10 elements:

  • mysteryArr[1].length has a length of 10,
  • mysteryArr[2].length has a length of 10.

setValues will assume the greated size to be the correct one and hence the discrepancy. Every row in the data must have the same length.

Upvotes: 2

RayGun
RayGun

Reputation: 491

According to Google's Docs - range.setValues - The size of the two-dimensional array must match the size of the range.

mysteryArr[i] must have the same length for all i.

Upvotes: 1

Related Questions