Cannot find function setValues in object Spreadsheet

Question

This error message is driving me crazy. It's probably so simple but I'm new to this. I appreciate any help I can get.

This program is for equipment inspections submitted through a google form by our employees. It should take the raw data from the submissions spreadsheet, check to see if there is a spreadsheet specific to the piece of equipment - if there is, it should make a new sheet with the date of the inspection, if there isn't, it should make a new spreadsheet with the title of the unit number of the equipment. I would then like the raw data to be copied to the new spreadsheet/sheet.

After this part is done I need to write code for formatting the raw data on the new sheet in to a more readable and user-friendly format - but I'm not quite there yet.

The issue is when I am trying to insert the heading values in to the new sheet (row 17) - "TypeError: Cannot find function setValues in object Spreadsheet. (line 17, file "Code")"

Here is the code I have so far:

    function onFormSubmit() {
  // onFormSubmit
  // get submitted data
  var ss = SpreadsheetApp.openById("1PieNQjjgjsZIEhTwmzGN9BcUXPSscWnWnnYL8XvjpTA");
  var sheet = ss.getSheetByName("Submissions");
  var row = sheet.getLastRow();
  var Col = sheet.getLastColumn();
  var headings = sheet.getRange(1,1,1,Col).getValues();
  var lastRow = sheet.getRange(row, 1, 1, Col);
  var UnitNumber = sheet.getRange(row,3,Col).getValue(); 
  // check if username has sheet
  if(ss.getSheetByName(UnitNumber)){
    var DrillSheet = ss.getSheetByName(UnitNumber);
    // if not make
  } else {
   var DrillSheet = SpreadsheetApp.create(UnitNumber); 
    DrillSheet.getRange(1,1,1,Col).setValues(headings);
  }
  // copy submitted data to Drill sheet
  DrillSheet.appendRow(lastRow.getValues()[0]);
  DrillSheet.appendRow(['=CONCATENATE(B6," ",B5)']);
  DrillSheet.appendRow(['=TRANSPOSE(B1:2)']);
}

Thank you in advance for your help.

Consider adding the error message, so your case can be understood more easily. — Megajin
Range vs Sheet vs Spreadsheet - they are different classes. — tehhowch

Brian Brian · Accepted Answer · 2018-08-30T13:03:05

When you create the new spreadsheet, you need to define which individual sheet you want the headings to be added to. You need to get the sheet object with .getSheetByName(). The first sheet is always called "Sheet1", so you can get it by name in the script. A simple fix would be to change line 17 to:

DrillSheet.getSheetByName('Sheet1')getRange(1,1,1,Col).setValues(headings);

Cannot find function setValues in object Spreadsheet

1 Answers