# Google Sheets built-in "On form submit" trigger

**URL:** <https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542>\
**Category:** Ask for Help\
**Created:** [November 30, 2019, 9:09pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542 "2019-11-30T21:09:45Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [December 30, 2020, 1:27am UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/21 "2020-12-30T01:27:02Z")

</div>

I assume you can still use an on change trigger, take out the row number of the row which triggers the script and use those values to send an email/SMS.

You can use Zapier/Integromat to do the same thing with emailing/Twilio.

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [December 30, 2020, 1:41am UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/22 "2020-12-30T01:41:21Z")

</div>

Yes, I have it working with Zapier/Twilio but there is a 2-3 minute lag. It’s the lag that is driving me to want the GAS to work.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 30, 2020, 1:53am UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/23 "2020-12-30T01:53:08Z")

</div>

> [@gp9293](#):
>
> New challenge. Sheet has many records, but my app will let the user check a box on a particular record for a particular column. Eg, Task is now complete, check the box. After user checks the box, I want to pull data from that row and send emails and SMS based on field values in the row. I looked at filters and loops, but feel like I am missing something conceptually. As always, guidance appreciated.

Once the email/SMS is sent, then presumably another box is checked?  
Your script can use this, ie: (pseudocode)

```
if (send_email is checked && email_sent not checked) then
  send email
  check email_sent box
end if

```

So you would need to read the entire sheet and check each row one by one.  
A word about that…  
The temptation is to create a loop and do a series of `getRange().getValues` calls, one for each row.  
Don’t do that - it’s very slow (ask @Eric_Penn 😉).  
Much better to read the _entire_ sheet into memory using a single `getDataRange()` call, and then do the processing in memory. This is orders of magnitude faster, especially if your sheet has any more than a few hundred rows.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [December 30, 2020, 3:03am UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/24 "2020-12-30T03:03:35Z")

</div>

Emailing works fine, text message would require you to have the carrier’s emailing template.

> <https://stackoverflow.com/questions/30957550/send-sms-using-google-scripts-on-google-spreadsheet>

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [December 30, 2020, 3:54pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/25 "2020-12-30T15:54:30Z")

</div>

Some data consider:

SMS triggered by onChange with direct connection to Twilio: almost instantaneous SMS received.

SMS triggered by Sheets-Zapier-Twilio: 2-3 minutes

SMS triggered by sendTxt approach using “[number@txt.att.net](mailto:number@txt.att.net)”: 60-90 seconds

May not sound like a lot, but it feels like a lifetime for certain apps that come to life with the real-time SMS. I will continue to try to make the onChange direct to Twilio method work for my different use cases and will report back I as learn more.

Thank you for the input.

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [December 30, 2020, 4:03pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/26 "2020-12-30T16:03:50Z")

</div>

I hear what you are saying about reading the entire range. But, let’s say the sheet has 100 rows and each row has a yes/no column for “accepted.” At a given point in time, there may be 60 columns with the default value of “no” and 40 columns that have been “checked” and are “yes.” When a user checks the box on a row (one of the 60 that are “no”) and converts it to a “yes,” I need to pull that row of data, which includes a cellphone number so I can send an SMS that indicates “accepted” has been changed to “yes.” Ideas welcome. Thank you.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 30, 2020, 4:19pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/27 "2020-12-30T16:19:45Z")

</div>

Do you record the fact that the SMS has been sent?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 30, 2020, 4:42pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/28 "2020-12-30T16:42:42Z")

</div>

As far as I’m aware, when Glide changes data in your sheet, it will only ever trigger an On Change event, even if it’s just (as you describe) a value being changed in a single cell. As you’ve probably seen from the [docs](https://developers.google.com/apps-script/guides/triggers/events?hl=en), the On Change event doesn’t have a `range` attribute, so there is no way to directly see which cell was changed. So I don’t think there is any getting away from processing the whole sheet.

What I would do is add another column that indicates whether or not an SMS has been sent. So then when you process the sheet, you look for rows where “accepted” is true and “SMS Sent” is false. For any that you find, you send an SMS and set “SMS Sent” to true.

If you’re worried about processing time, I wouldn’t. As long as you’re doing a single read (`getValues()`) and a single write (`setValues()`), then processing a sheet of 100 rows (or even 1000) should take less than a second.

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [December 30, 2020, 4:48pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/29 "2020-12-30T16:48:09Z")

</div>

Ok, I think I see the approach. Will see if I can implement. Thank you

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 30, 2020, 4:58pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/30 "2020-12-30T16:58:03Z")

</div>

Actually, just thinking about this a bit more - if you write everything back in one go then you risk a race condition. ie. if 2 users happen to send an update at the same time.

So it’s probably safer to just flag the rows that have changed as you’re processing the data, and then update the “SMS Sent” flags one at a time once you’re done. Assuming that only one or a few rows will have changed each time, the additional processing overhead should be negligible.

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [December 31, 2020, 1:12pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/31 "2020-12-31T13:12:02Z")

</div>

Trying to set up the new column to say “Done” once a text message has been sent. The following code not working.

````auto
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var s = ss.getSheetByName("Bets2");
   var r = s.getActiveCell();
      if(r.getColumn() == 7 && r.getValue() == 'TRUE') {
      var nextCell = r.offset(0, 25);
      nextCell.setValue("Done");
 }
}```

Any ideas appreciated.
````

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 31, 2020, 1:23pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/32 "2020-12-31T13:23:33Z")

</div>

A bit difficult to say without more context, but a couple of comments…

It seems a bit odd to be using `getActiveCell()` here. That’s going to return the currently active cell in the sheet, which could be anywhere.

So the the column that contains “accepted” is column number 7, and you want to change the value in column 32 to “Done”. Is that correct?

Maybe if you can show me your script in its entirety, I can offer some better advice.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 31, 2020, 1:54pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/33 "2020-12-31T13:54:00Z")

</div>

@gp9293 - here is an example of how I would do it…

```
function check_sms() {
  var sheetname = 'SMS'; // The name of the sheet to check
  var check_col = 6; // The column number that contains "Accepted"
  var flag_col = 9; // The column number that should be changed to "Done"
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  var data = sheet.getDataRange().getValues();
  data.shift(); // Discard the header row
  var rows_to_flag = [];
  var row = 2;
  while (data.length > 0) {
    var this_row = data.shift();
    // NB. Arrays are zero-based, so we use col number -1
    if (this_row[check_col-1] == 'Accepted' && this_row[flag_col-1] != "Done") {
      // Call send SMS function here
      rows_to_flag.push(row);
    }
    row++;
  }
  
  if (rows_to_flag.length > 0) {
    rows_to_flag.forEach(function (row) {
      sheet.getRange(row,flag_col).setValue('Done');
    });
  }
}

```

If I’m understanding correctly, you should almost be able to use that as it is - just change the values of the variables on the first 3 lines.

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [December 31, 2020, 2:53pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/34 "2020-12-31T14:53:57Z")

</div>

So this would trigger on an onEdit, correct? If the edit changed the value of column 7 to “Accepted” then the send SMS function would run and then the value of column 24 would be changed to 'Done." Am I understanding the logic? Does the script stop if there is no SMS function? eg, In testing, I go into the sheet and change a column 7 to “Accepted.” Doing this does not change column to 'Done" since am not running the sendSMS function. Make sense?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 31, 2020, 3:07pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/35 "2020-12-31T15:07:10Z")

</div>

You need to create an On Change (not On Edit) trigger that would fire it when a change is made.

I would wrap it in another function that first checks which sheet was changed, before calling it. This would ensure that it isn’t called unnecessarily. Something like this:

```
function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  if (sheetname == 'SMS') { // Change this to the name of your sheet
    check_sms();
  }
}

```

Then you create an On Change trigger that fires the above function. Once you’ve done that, any edits that are made to that sheet should fire it.

> [@gp9293](#):
>
> If the edit changed the value of column 7 to “Accepted” then the send SMS function would run and then the value of column 24 would be changed to 'Done." Am I understanding the logic?

Yes, except the column numbers are different in my example.

> [@gp9293](#):
>
> In testing, I go into the sheet and change a column 7 to “Accepted.” Doing this does not change column to 'Done" since am not running the sendSMS function. Make sense?

Correct. But you can test it by changing one of those values and then run the script manually.

> [@gp9293](#):
>
> Does the script stop if there is no SMS function?

The script will stop as soon as it’s finished processing. If there is no SMS function, it will still set any rows that qualify as “Done”, but no SMS will be sent (obviously).

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [January 4, 2021, 1:11pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/36 "2021-01-04T13:11:07Z")

</div>

Script works! Now am having trouble nesting the sendSMS function. This will send the SMS to notify user of the “Accepted” change. I have the email working (at the bottom of the script), but cannot get the SMS to trigger. The “function sendSMS” block is code that works for me in a different script, but I must be improperly nesting it or something. Any advice appreciated.

```auto
  var sheetname = 'Bets2'; // The name of the sheet to check
  var check_col = 13;
  // The column number that contains "Accepted"
  var flag_col = 24; // The column number that should be changed to "Done"
  var cellB_col = 11;
  var cellA_col = 5;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  //testing to see if script ran
  var range = sheet.getRange("r2");
  var data = range.getValue();
  range.setValue(++data);
  //end testing
 
  var data = sheet.getDataRange().getValues();
  data.shift(); // Discard the header row
  var rows_to_flag = [];
  var row = 2;
  while (data.length > 0) {
    var this_row = data.shift();
    // NB. Arrays are zero-based, so we use col number -1
    if (this_row[check_col-1] == 'YES' && this_row[flag_col-1] != "Done") {
      if (rows_to_flag.length > 0) {
    rows_to_flag.forEach(function (row) {
      sheet.getRange(row,flag_col).setValue('Done');
    });
      
      // Call send SMS function here

   function sendSMS(to, body) {}
 
  var messages_url = "https://api.twilio.com/.....";
 
  var payload = {
    "To": "1562xxxxxxx",
    "Body" : "Sample text for the SMS." ,
    "From" : "1410xxxxxxx"
  };

  var options = {
    "method" : "post",
    "payload" : payload
  };

  options.headers = { 
    "Authorization" : "Basic " + Utilities.base64Encode("AC291dxxxxxxxee")
  };
 UrlFetchApp.fetch(messages_url, options);
}

//Test if email works
      MailApp.sendEmail('gpillari@gmail.com', this_row[cellA_col-1], this_row[cellB_col-1]);
 //End Test

      rows_to_flag.push(row)
      console.log(range, check_col, flag_col, cellB_col)
    }
    row++;
  }
```

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 4, 2021, 1:19pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/37 "2021-01-04T13:19:28Z")

</div>

Instead of nesting the entire function, you just need to include a call to it.  
So you should have something like:

```
// Call send SMS function here
sendSMS(to, body);

```

Note however that your sendSMS function expects two parameters, so you need to define and populate those variables before you call the function. So just prior to the function call, you might have something like:

```
var to = 'xxxxxx'; // A value already captured from the sheet?
var body = 'Text of message';
```

---

<div class="post-metadata">

**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\
**Post date:** [January 4, 2021, 1:53pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/38 "2021-01-04T13:53:46Z")

</div>

Your knowledge of scripts is great 👍 brilliant.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 4, 2021, 2:02pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/39 "2021-01-04T14:02:23Z")

</div>

hahaha, thanks. But that’s not really true. Like most things, I am continually reminded that the more I learn about Apps Scripting, the more I find out that I don’t know 😉

---

<div class="post-metadata">

**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\
**Post date:** [January 4, 2021, 2:10pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/40 "2021-01-04T14:10:17Z")

</div>

And because of this approach you know a lot more than you think you know 😁

[Previous page](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542.md?page=1)

[Next page](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542.md?page=3)