Google-sheets – What can break the relationship between the onFormSubmit function and a spreadsheet that is attached to a google form

google sheetsgoogle-appsgoogle-apps-scriptgoogle-forms

I had a working Google form, Google spreadsheet, and GAS script which would be called when the form was submitted. It worked fine until the did (one?) of the following things:

Cleared out the Responses
Moved the sheet and the form into a folder on Google Drive by themselves (and a Google Document)
Modified the form a bit, moving questions around, adding them and such.

When I submit the form, the data still shows up in the sheet but my .gs script is not called any longer for some reason.

The script sends mail, but I also wrote a function to check my quota for sending mail from it and it says I still have some quota left for the day I can still send 89 emails:

function test() {
 var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
 Logger.log("Remaining email quota: " + emailQuotaRemaining);
}

The script is 173 lines long, it doesn't seem like it's that big.

I already have the Resources->Current Project's Triggers setup to send an email immediately.

Is there any way to check why the onFormSubmit function is not being called?

Best Answer

There's a defect listed on the Google Apps Script issue tracker.

We'll have to wait it out to see when a project member responds to it, I'm assuming.

Related Solutions

Google-sheets – onOpen doesn’t execute

Simple Triggers, such as onOpen, are subject to certain restrictions:

They cannot access services that require authorization.

They can modify the file they are bound to, but cannot access other files because that would require authorization.

They may or may not be able to determine the identity of the current user, depending on a complex set of security restrictions.

You were trying to get the email of EffectiveUser (which may or may not be possible) and also to access another spreadsheet (which is certainly impossible).

(If you wonder why this restriction is in place, imagine what would happen if someone shared with you a document with onOpen attached, which would access your other files.)

The solution is to use an installable trigger.

Google-sheets – How to get information from Google Sheets to send it via form emailer

You need a script for this, triggered by form submission and using the method MailApp.sendEmail to send email (it's preferable to use MailApp over GmailApp, so that the script is only authorized to send email, not to read or modify the contents of your inbox.)

For example, suppose this is the sheet with form submissions, where I added column Confirmed to keep track of what's been confirmed already.

+---+---------------------+----------+----------------+-----------+
|   |          A          |    B     |       C        |     D     |
+---+---------------------+----------+----------------+-----------+
| 1 | Timestamp           | Response | Email          | Confirmed |
| 2 | 10/30/2015 20:19:36 | jjj      | user@gmail.com |           |
+---+---------------------+----------+----------------+-----------+

The script given below should have a trigger added from the script editor via Resources > This project's triggers... Specifically, it should be "from spreadsheet > on form submit".

function formEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var range = sheet.getDataRange();
  var values = range.getValues();
  var width = values[0].length;
  for (var i = values.length - 1; i >= 0; i--) {
    if (values[i][width-1]) {
      break;
    }
    var email = values[i][2];
    var subject = 'Form submission confirmed';
    var body = 'Your response was: ' + values[i][1];
    MailApp.sendEmail(email, subject, body);
    range.getCell(i+1, width).setValue('yes');
  }
}

This reads the form submissions from bottom up, until the last confirmed one. After each email is sent, the entry in "Confirmed" column is set to 'yes'.

Related Topic