Setting a Google Form destination to it responses via google script-CodePudding

I'm trying to set up a script that creates a form based on a sheet (which works) then I want it to submit the responses to a specific sheet so I can have have scripts interact with it, manually connecting the forms to the sheet won't be practical as multiple form could be created every day

when I run the script this error code appears

Exception: Unexpected error while getting the method or property setDestination on object FormApp.Form.

below is my code, any help will be apricated

function bug() {

  var sheet = SpreadsheetApp.getActive().getSheetByName('TC');
  var value = sheet.getRange("A3").getValue();
  var em = sheet.getRange("B3").getValue();
  var cos = sheet.getRange("E3").getValue();
  var name = sheet.getRange("D3").getValue();
  var sup = sheet.getRange("C3").getValue();

  var form = FormApp.create(value);
  var item = form.addMultipleChoiceItem();
  item.setHelpText("Name: "   name  
                 "\n\nEmail: "   em  
                 "\n\nQuote No: "   value  
                 "\n\nProject: "   sup  
                 "\n\nTotal Cost: "   cos  
                 "\n\nBy selecting approve you agree to the cost and timeframe specified by the quote and that the details above are correct")
                 .setChoices([item.createChoice('Approve'), item.createChoice('Deny')]);
  var item = form.addCheckboxItem();
  item.setTitle('What extras would you like to add on?');
  item.setChoices([
        item.createChoice('Damp-Course'),
        item.createChoice('Wrap'),
        item.createChoice('Taco')
    ]);
  Logger.log('Published URL: '   form.getPublishedUrl());
  Logger.log('Editor URL: '   form.getEditUrl());
  Logger.log('ID: '   form.getId());

  var ssId = sheet.getSheetId();
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ssId);

  var SendTo = "[email protected]";

  var link = form.getPublishedUrl();
  var message = "Please follow the link to accept you Quotation "   form.getPublishedUrl();

 //set subject line

 var Subject = 'Quote'   value   'Confirmation';

  const  recipient = em;
  const subject = "Confirmation of order"
  const url = link

GmailApp.sendEmail(recipient, subject, message);

}

CodePudding user response：

form.setDestination(FormApp.DestinationType.SPREADSHEET, ssId);

ssId is supposed to be the id of a Spreadsheet not a sheet

example

spreadsheet id

getSheetId

CodePudding user response：

You cannot send form responses to a previously existing sheet. When you link your form to a destination spreadsheet, it will always create a new sheet to store those responses, no matter whether you use Apps Script to set the destination. You can only choose whether it will be an existing spreadsheet or a new one.

Because of this, you have to provide the spreadsheet id when calling Form.setDestination(type, id). Since you are providing the sheet id instead, you are getting an error.

You should do something like this:

function bug() {
  // ...
  const spreadsheetId = SpreadsheetApp.getActive().getId();
  form.setDestination(FormApp.DestinationType.SPREADSHEET, spreadsheetId);
  // ...
}

Related: