Is there a way to have a Form submit responses to a specific sheet in a spreadsheet?

Question

I'm aware of the following line: form.setDestination(FormApp.DestinationType.SPREADSHEET, ss1.getId());

but I'm trying to get the form submissions to appendRow/submit to a specific sheet within spreadsheet ss1. Any way to do that within google app scripts? The above just points to spreadsheet ss1 and creates a "Form Responses" sheet within that spreadsheet.

Thanks in advance for the guidance!

P.S. For a little more background, I'm trying to set up a form to send expense data to my budget google sheets file, which has a "current month.year" format for the target sheet name.

Would you use an Add-on? There is at least on Add-on that can do this. — Alan Wells

TheAddonDepot TheAddonDepot · Accepted Answer · 2017-06-09T11:31:53

You can write a custom event handler function (in the Script Editor for the Form NOT the Spreadsheet's Script Editor) and bind it to the Form submit event via an installable trigger (see documentation).

Within the body of the function you can reference the spreadsheet and the target sheet where you want to pass the form's data. However, you have to know the Form Service APIs and how to extract and format the form's respondent data manually since you won't have the benefit of having a "linked" spreadsheet bound to the form.

So you could write an event handler function (from the Form's Script Editor) as follows:

var SPREADSHEET_ID = "[Your spreadsheet ID]",
    SHEET_NAME = "[Your sheet name]";

function onFormSubmit(e) {
    var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME),
        form = FormApp.getActiveForm();

    // Extract and format form data and inject it into the sheet 
}

Once you have that function in place you create an installable trigger to bind it to the form submit event in the form's Script Editor via the Edit menu command in the tool bar. Here is a link to the steps you'll need to follow to create the trigger.

Hope that helps.

Is there a way to have a Form submit responses to a specific sheet in a spreadsheet?

1 Answers