Can I copy a script from a Form to the linked spreadsheet?

Question

I have a form template which I will be duplicating as needed, modifying, and sending out for responses. I have written a script for the linked responses spreadsheet which rearranges the response data in a specific way. The script itself works fine, and it only needs to run on my account.

The problem is that the script is tied to the spreadsheet, not the form; but it’s the form and not the spreadsheet that gets duplicated. I tried linking my script to the form template and using the code below to create a linked spreadsheet and copy the script over. I set up a trigger to run this function on form submit, but the trigger disappears when the form template is duplicated. This code is largely copied from this answer: https://stackoverflow.com/a/48353155/12131953 with a few lines added at the beginning to create the linked spreadsheet.

function copyScript() {
  //create destination spreadsheet
  var form = FormApp.getActiveForm();
  var ss = SpreadsheetApp.create(form.getTitle() + " (Responses)");
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());

  var srcProjectId = "###my project id###"; // Source project ID
  var dstGoogleDocsId = ss.getId(); // Destination spreadsheet ID

  var baseUrl = "https://script.googleapis.com/v1/projects";
  var accessToken = ScriptApp.getOAuthToken();

  // Retrieve filename of bound-script project.
  var srcName = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + srcProjectId, {
    method: "get",
    headers: {"Authorization": "Bearer " + accessToken}
  }).getContentText()).title;

  // Retrieve bound-script project.
  var obj = UrlFetchApp.fetch(baseUrl + "/" + srcProjectId + "/content", {
    method: "get",
    headers: {"Authorization": "Bearer " + accessToken}
  }).getContentText();

  // Create new bound script and retrieve project ID.
  var dstId = JSON.parse(UrlFetchApp.fetch(baseUrl, {
    method: "post",
    contentType: 'application/json',
    headers: {"Authorization": "Bearer " + accessToken},
    payload: JSON.stringify({"title": srcName, "parentId": dstGoogleDocsId})
  }).getContentText()).scriptId;

  // Upload a project to bound-script project.
  var res = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + dstId + "/content", {
    method: "put",
    contentType: 'application/json',
    headers: {"Authorization": "Bearer " + accessToken},
    payload: obj
  }).getContentText());
}

Then I tried to create the trigger programmatically when the duplicated form is opened, but as far as I can tell creating installable triggers is outside the authorization of the simple trigger onOpen.

function onOpen(); {
   ScriptApp.newTrigger("copyScript").forForm(FormApp.getActiveForm()).onFormSubmit().create();
.
.
.
}

I am not a developer and am self-taught on this stuff; I’m pretty comfortable with the scripting aspect but have no familiarity at all with APIs and web deployments.

So my question is: Is there a way to copy a script to a new, form-linked spreadsheet from the form? I’m also fine with a solution that somehow applies my script (maybe as a standalone) to all new spreadsheets, because the code creates a menu option; so making the script an add-on may be the answer here, but that seems like overkill for a script that only one person is ever going to run.

Welcome. Have a look at Enum DestinationType in the "Forms Service" documentation. It includes an example of creating a new spreadsheet and then setting the form destination to that spreadsheet. — Tedinoz

ziganotschka ziganotschka · Accepted Answer · 2019-09-30T12:39:39

Instead of rebounding your old script from the spreadsheet to the form, leave as it is, but add the following script to your Form:

function onOpen() {
  FormApp.getActiveForm().setDestination(FormApp.DestinationType.SPREADSHEET, 'ID of your destination spreadsheet'); 
}

Run this script once manually in order to give necessary permissions to the script
Copy your form as much as you like - each copy will contain a copy of the script
The script will fire onFormOpen and set the destination of the respective form copy to the same spreadsheet - the one containing your rearranging script
Form responses from different forms will be automatically inserted in different sheets of the spreadsheet

Can I copy a script from a Form to the linked spreadsheet?

1 Answers

Instead of rebounding your old script from the spreadsheet to the form, leave as it is, but add the following script to your Form: