Scripts triggering other scripts

Question

I have several Google Forms that use an onFormSubmit trigger to push the result of their submissions into one "master" Google Sheet (different file) using setValues to consolidate all the responses to the forms. This "master" spreadsheet does some data-processing on the results of the forms in additional columns, and kicks the data to another spreadsheet. However, I can't think of a way to connect the form submissions to the "master" spreadsheet's script.

I had initially assumed that the setValues function in the forms' onFormSubmit (which I use to push the results of the forms to the "master") would trigger the onEdit in the "master" sheet, but a quick Google search shows that this isn't the case. Because they're in different projects, I don't know if the Forms' onFormSubmit script can call a function in the "master" spreadsheet project. I've read that the onChange() trigger doesn't work for doing this, despite a Google dev stating that it does.

I don't want to use a timer-based updater. Are there any tips on having one sheet's script kick off another sheet's script automatically?

Mogsdad Mogsdad · Accepted Answer · 2015-10-26T16:15:31

As you've already discovered, Event Triggers are a very elegant way to invoke a script. The unfortunate down-side of applying them for inter-script communication is that there is no specific trigger that supports IPC. There has been an open request for such functionality since 2012 (for scripts bound to the same document), Issue 2231.

Your options are to harness an existing trigger or to simulate one with a web app.

Existing spreadsheet triggers for IPC

The Open event is obviously not applicable, so we'll skip that.

Form Submission (useful)

You say the script in your "master" spreadsheet "does some data-processing on the results of the forms in additional columns, and kicks the data to another spreadsheet." If that data is going as a row, then you could programmatically submit a form response to the subservient spreadsheet. That would allow you to use a form submission trigger function there to initiate any further processing.

For example, this function would copy two answers from the "master" form and simulate submission to the sub-form:

/**
 * Master form submission trigger function. Processes "master" form responses and
 * passes data on to subForm by simulating a subForm submission.
 *
 * From: https://stackguides.com/questions/33347541
 */
function onSubmit( e ) {
  // Base URL of secondary processing script's form ("subForm")
  var subFormID = "--Sub-Form-ID--";

  // Process master form submission here...

  // In this example, we're just passing values from Master through to the sub-form.
  var subForm = FormApp.openById(subFormID);
  var items = subForm.getItems();
  var subResponse = subForm.createResponse()
                           .withItemResponse(items[0].asTextItem().createResponse(e.values[1]))
                           .withItemResponse(items[1].asTextItem().createResponse(e.values[2]));

  // Simulate a user form submission with manufactured response.
  subResponse.submit();
}

Here, the sub-form has two text items, and it does not matter what the master form item types are. Since the sub-form isn't open for human interaction, this simplifies the code.

onEdit (useless)

As you've encountered, this trigger responds only to manual edits of cell contents, so it's not relevant for IPC.
onChange (useless)

This trigger responds to changes in spreadsheet structure, as well as content (edits), but not when they are made programmatically. Until Issues 2751, 3269, and most importantly 4568 get fixed, you won't be able to use onChange triggers for communicating between scripts.

Web App

The form submission trigger function in the master script could update the secondary spreadsheet via a web app attached to the sub sheet. An example of such a web app is shown in How to post to Google Docs Form directly.

Assuming the sub-script is using that script as-is, the master form handler could do this:

/**
 * Master form submission trigger function. Processes "master" form responses and passes data on
 * to subSheet by calling a web app.
 *
 * From: https://stackguides.com/questions/33347541
 */
function onSubmit( e ) {
  // Base URL of secondary processing script's form ("subForm")
  var subSheetWebAppURL = "https://script.google.com/macros/s/--APP-ID--/exec";

  // Process master form submission here...

  // In this example, we're just passing values from Master through to the sub-sheet.
  var a = UrlFetchApp.fetch(subSheetWebAppURL
                            + "?col1="+e.values[1]
                            + "&col2="+e.values[2]
                           );
}

In this approach, it would be the web app that would do whatever processing was desired in the sub-sheet, before storing the data.

Scripts triggering other scripts

1 Answers

Existing spreadsheet triggers for IPC

Web App