How do you resolve a "The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues" error

Question

I am getting this error:

"The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues."

in my Google Apps Script when I try to write an array of values to a sheet.

Below is a shortened (simplified) version of code. The actual code runs through about 10,000 records.

The error is generated in the last line, when the setValues is called.

I know I'm missing something super simple here.

function writeArrayToSheet() {

  var ss = SpreadsheetApp.openById("Spreadsheet_ID");
  var orderSheet = ss.getSheetByName("Sheet_Name");
  var vTable = orderSheet.getRange(1,6,5,11).getValues(); //Raw data 
  var vWriteTable = []; //Data that will be written to sheet
  var updateTime = new Date();
  var i = 0;
  var vSeconds = 0;


  while (i < 5 && vTable[i][0] != "") {

    //Logic section that calculated the number of seconds between

    if (vSeconds == 0) {
      vWriteTable.push("");
    } else {
      if (vTable[i][6] < certain logic) {
        vWriteTable.push("Yes");
      } else {
        vWriteTable.push("");
      }      
    }        
    i = i + 1;
  } // End while

  orderSheet.getRange(1,20,vWriteTable.length,1).setValues(vWriteTable);
} //End Function

This is what vWriteTable looks like when debugging:

Diaper

Answer 1

setValues accepts(and getValues() returns):

• 1 argument of type:
• Object[][] a two dimensional array of objects

It does NOT accept a 1 dimensional array. A range is always two dimensional, regardless of the range height or width or both.

If A1:A2 is the range, then corresponding values array would be like:

• [[1],[3]]

Similarly, A1:B1 would be

• [[1,2]]

A1:B2 would be

• [[1,2],[3,4]]

Notice how the two dimension provides direction and that it is always a 2D array, even if the height or width of the range is just 1.

Solution:

Push a 1D array to make the output array 2D.

Snippet:

vWriteTable.push(/*Added []*/["Yes"]);

More information:

For a more detailed explanation of arrays in google sheets, checkout my answer here.

Thaumatrope

Could someone edit this answer to provide the actual solution? I don't know what to do with the snippet. – Genital

@Genital what the snippet means is that each time you do a push it has to be as a 2D array. So if the array you are building is 1D you need to add additional square brackets to make it 2D. When applied to the original question, this means you replace the following lines of code as follows: vWriteTable.push(""); --> vWriteTable.push([""]); and vWriteTable.push("Yes"); --> vWriteTable.push(["Yes"]); – Hosmer

For dynamic values=>var yourVariable=<some dynamic value>; =>vWriteTable.push([yourVariable]); ( instead of just vWriteTable.push(yourVariable); ) – Cult

Answer 2

You can simply way do like this put value in square brackets []:

in your case: WriteTable.push([""]) or WriteTable.push(["yes"])

Nidia

Answer 3

getValues returns object[rows][columns]

1 row object [[1...n]] - this maybe confusing b/c it may not look like 2D but it is 2D object[1 row][n columns]

2 row object [[1...n],[1...n]] - object[2 rows][n columns]

etc [[1...n],...,[1...n]] - etc

If you need to setValues to rectangular range with n rows and m columns, create EXACTLY n by m array 1st, then fill it with your values and send it to the MATCHING range in your spreadsheet. Here is some of my code that might help.

Getting values from 1st m cells in n-th row and pushing them in simple 1D array:

    var arr = [];         
    var dataRow = sheet.getRange(n,1,1,m).getValues();
    for (var j=0; j<dataRow.length; j++){
      arr.push(dataRow[0][j]);
    }

to replace them with new values from 1D array arr:

//create 2D array with just 1 row and m columns
    var dataRow = new Array(1);
    dataRow[0] = new Array(arr.length);
//fill it with values
    for(var j=0;j<arr.length;j++){
        dataRow[0][j]=arr[j];
    }
//send it to the spreadsheet
    sheet.getRange(n,1,1,arr.length).setValues(dataRow);

Union