Accessing Google Form from Apps Script tied to the same Google Spreadsheet

Question

I have a Google Spreadsheet with which I've created a Google Form. The spreadsheet has two sheets: one with form responses, and one with configuration data. Attached to the spreadsheet is a form-response-triggered Apps Script script that uses the configuration data to make an API call. This script runs exactly as I desire.

I have written spreadsheet-change-triggered script which attempts to update choices for one of the form's questions. However, using FormApp.openById or FormApp.openByUrl fails with "You do not have permission to call ...".

Is there some way to give the spreadsheet-change-triggered script permissions to the form?

Or is there a better setup for what I need here: keep information sync'd between a form and the script making api calls based on the form responses?

Code:

function onEdit(e) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Do some work to get the configuration data.  This works fine.

  // Now try to get access to the form to update it
  var responseSheet = activeSpreadsheet.getSheetByName('Form Responses');
  // var form = FormApp.getActiveForm();  // returns null
  var form = FormApp.openByUrl(responseSheet.getFormUrl());
  ...
}

The final line fails with:

Execution failed: You do not have permission to call openByUrl

You've authorized the script with a Google account. Make sure this account has the correct permissions to edit the form you're trying to open. This error gives me the idea that the account has insufficient rights. — Casper
@Casper I haven't done any authorization yet. How does one do that in this context? This script seems to be called a "bound script". Perhaps they cannot be authorized to edit forms (even though they are triggered by the form and can see it's response)? — HoosierDaddy
Possible duplicate of Google Sheets OnEdit() - You do not have permission to call showModalDialog — Kos

Elsa Elsa · Accepted Answer · 2017-12-22T05:40:39

You should install trigger for onEdit(). When use methods which need authorization in onEdit(), it has to be installed as a trigger. When you run this script, you can install the trigger for onEdit(). Also you can install manually. You can read about triggers at here and here.

function installtrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

After install the trigger, try again.

Accessing Google Form from Apps Script tied to the same Google Spreadsheet

1 Answers