Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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:

debug data

like image 242
Nico Avatar asked Apr 09 '20 07:04

Nico


1 Answers

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.

like image 199
TheMaster Avatar answered Sep 21 '22 02:09

TheMaster