Issues using setValues function

Question

I keep getting an error message when using the setValues() function to export an array to Google sheets. I have tried many different methods for creating my 2D array, but I still keep getting the same errors. Sometimes my code will run (the array will export to the spreadsheet) but I will still get an error.

I originally used the setValue() function in a for loop but the code would time out because it ran too long. So I tried dumping all my data into a 2D Array and feeding that to the spreadsheet all at once.

Tried creating the Array as an empty 2D array

  var outputArray = [[]]

and using .push to populate the data into it

Tried creating the empty Array using the function:

function create2DArray(rows) {
  var arr = [];

  for (var i=0;i<rows;i++) {
     arr[i] = [];
  }

  return arr;
}

and adding the data in by rows (inside of a for loop that iterates by rowNumber)

outputArray[rowNumber] = [data1, data2, data3,...]

Used the same function above for creating empty array and created intermediate array and then put that into output array

outputArrayIntermediate[0] = data1;
outputArrayIntermediate[1] = data2;
outputArrayIntermediate[2] = data3;
outputArrayIntermediate[3] = data4;...

outputArray[rowNumber] = outputArrayIntermediate;

Here is where the error keeps happening

var setRows = outputArray.length;
 var setColumns = outputArray[0].length

  revenueSheet.getRange(2,1,setRows,setColumns).setValues(outputArray);

When I include the setColumns variable I get the error: "The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 11." This will still populate the data to the spreadsheet.

When I do not include the setColumns variable I get the error: "The number of columns in the data does not match the number of columns in the range. The data has 11 but the range has 1."

Answer 1

Is there ever an instance where one row has more columns than another row? For instance if 'row' 1 in your data as 5 columns (outputArray.length = 5) and row 2 has 6, then the data needs a range with 6 columns.

If this is the case, here are some solutions in order of simplicity:

1. If there is no important data to the right of where you are inserting you data you can use revenueSheet.getMaxColumns() in your .getRange().setValues().

2. Iterate through the data set to find the row with the longest length and set that as the number of columns. To do this see this answer for a few options.

Answer 2

You only need [] not [[]], because you are pushing arrays into outputArray, and push makes sure it will always expand when needed.

Try this:

outputArray = [];
outputArray.push([data1, data2, data3]);
outputArray.push([data4, data5, data6]);     

revenueSheet.getRange(2,1,outputArray.length,outputArray[0].length).setValues(outputArray);

Answer 3

Perhaps this will help you:

function createSSData(numrows,numcols) {
  var numrows=numrows || 20;
  var numcols=numcols || 20;
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var values=[];
  for(var i=0;i<numrows;i++) {
    values[i]=[];
    for(var j=0;j<numcols;j++) {
      values[i].push(Utilities.formatString('row: %s,col: %s',i+1, j+1));
    }
  }
  sh.getRange(1,1,values.length,values[0].length).setValues(values);
}