Run spreadsheet function from google form script

Question

I want to call a user spreadsheet function from the google form submission to update some information in the spreadsheet.

All scripts are on the same google drive.

Is there a way to do that ?

Code in my google form:

function updateSheet() {
  spreadsheet = SpreadsheetApp.openById('daedaeddea');
  spreadsheet.myFunction();
}

function onOpen() {
 var form = FormApp.openById('deadeadaedae');
 ScriptApp.newTrigger('updateSheet')
     .forForm(form)
     .onFormSubmit()
     .create();
}

Spreadsheets with attached forms have an onFormSubmit trigger which can be used to run a script. Call it from the spreadsheet, not the form. — Brian
In my case, it is not a spreadsheet with attached forms. The spreadsheet is independant from the forms. — eleandar
A macro script (function in google script) defined in a spreadsheet script project. — eleandar

noogui noogui · Accepted Answer · 2017-05-14T14:27:37

You can definitely connect Google Forms with Spreadsheets. There's a sample in Quickstart: Managing Responses for Google Forms on how to do this using the onFormSubmit trigger.

Here's a snippet:

function onFormSubmit(e) {
  var user = {name: e.namedValues['Name'][0], email: e.namedValues['Email'][0]};

  // Grab the session data again so that we can match it to the user's choices.
  var response = [];
  var values = SpreadsheetApp.getActive().getSheetByName('Conference Setup')
     .getDataRange().getValues();
  for (var i = 1; i < values.length; i++) {
    var session = values[i];
    var title = session[0];
    var day = session[1].toLocaleDateString();
    var time = session[2].toLocaleTimeString();
    var timeslot = time + ' ' + day;

    // For every selection in the response, find the matching timeslot and title
    // in the spreadsheet and add the session data to the response array.
    if (e.namedValues[timeslot] && e.namedValues[timeslot] == title) {
      response.push(session);
    }
  }
  sendInvites_(user, response);
  sendDoc_(user, response);
}

Run spreadsheet function from google form script

1 Answers