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 = "insertgenertic@email.com";
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);
}
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);
// ...
}
form.setDestination(FormApp.DestinationType.SPREADSHEET, ssId);
ssId is supposed to be the id of a Spreadsheet not a sheet