Script returning set value error despite working in logger. The parameters (number[]) don't match

Question

I am quite literally at my wits end. I have this code that I put together to manage files that will be submitted through a Google form. The files are XLS and the conversion to sheets works perfectly.

The bottom function is to pull in the last 9 columns and the 1st column from each file. This seems to also work according to logger.log

However when I go to consolidate the data into a single file it all falls apart with the error:

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

I cannot figure out what is wrong....

function myFunction() {
  //fileID
  

  var folder = DriveApp.getFolderById("fileID");
  var filesToConvert = DriveApp.getFolderById("fileID").getFilesByType(MimeType.MICROSOFT_EXCEL);
    while (filesToConvert.hasNext()){ Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: "FileID"}]}, filesToConvert.next().getId());}
  
  var filesIterator = folder.getFiles();
  var file;
  var filetype;
  var ssID;
  var combinedData = [];
  var data;



  while(filesIterator.hasNext()){
    file = filesIterator.next();
    filetype = file.getMimeType();
   if (filetype === "application/vnd.google-apps.spreadsheet"){
      ssID = file.getId();
      data = getDataFromSpreadsheet(ssID)
      combinedData = combinedData.concat(data);
      
    }//if ends here
  }//while ends here

  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Combined");
  ws.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData);
}




function getDataFromSpreadsheet(ssID) {

  var ss = SpreadsheetApp.openById(ssID);
  var ws = ss.getSheets()[0];
  var data = ws.getRange(4, ws.getLastColumn()-9, ws.getLastRow(), 10).getValues();
  var dataTwo  = ws.getRange(4, 1, ws.getLastRow(), 1).getValues();
  var dataThree = [];

for (let i in dataTwo) {

  let dataIndex = i;
  let startTime = data[dataIndex]

    dataThree.push( [dataTwo[i], startTime].join(','))
};

return dataThree;

}

it seems that `combinedData` is a single 1D array but `setValues` accept a 2D array. Show us what is in the `combinedData` array, otherwise there is no way we can help you. — Marios, Jan 05 '22 at 16:33
Hi soMario, thanks for that. Combinedata comes from the funciton at the bottom. GetDataFromSpreadsheets. It takes DataThree which is a combination of data and dataTwo. Unless I have accidentally created a 1D array through the push() I used to combine data and dataTwo? — Vincent Ryan, Jan 05 '22 at 16:42
try `dataThree.push( [[dataTwo[i], startTime].join(',')])` instead — Marios, Jan 05 '22 at 16:52

score 2 · Accepted Answer · answered Jan 05 '22 at 17:00

2

It seems that combinedData is a single 1D array but setValues accept a 2D array.

Replace:

dataThree.push( [dataTwo[i], startTime].join(','))

with:

dataThree.push( [[dataTwo[i], startTime].join(',')])

since you want to get an array of arrays.

answered Jan 05 '22 at 17:00

Marios

26,333
8
32
52

Script returning set value error despite working in logger. The parameters (number[]) don't match

1 Answers1