# 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:** 3

<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, 2:32pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/41 "2021-01-04T14:32:08Z")

</div>

Feels like I am on the one-yard line but just do not know enough yet about GAS syntax, etc.

Here is the call as you instructed:

````auto
   var to = '14086567070';
   var body = 'sendSMS test';
   sendSms(to,body);

And here is the sendSms function in a separate script:

```function sendSms(to, body) {
  var messages_url = "https://api.twilio.com/2010-04-........";

  var payload = {
    "To": to,
    "Body" : body,
    "From" : "14104313008"
  };

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

  options.headers = { 
    "Authorization" : "Basic " + Utilities.base64Encode("AC291d5ff9701......"
  };

  UrlFetchApp.fetch(messages_url, options);
}

Twilio keeps kicking it back. The "to" and "body" parameters do not seem to be passing. Appreciate the help and once this hurdle is crossed, I will go through the full GAS tutorials and training. Thank you.
````

---

<div class="post-metadata">

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

</div>

I’ve not used Twilio before, but a quick glance at their [API Docs](https://www.twilio.com/docs/usage/api) suggests that all parameters should be lower-cased. So what happens if you use:

```
var payload = {
    "to": to,
    "body" : body,
    "from" : "14104313008"
  };

```

?

---

<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 6, 2021, 9:39pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/43 "2021-01-06T21:39:12Z")

</div>

I was able to get the parameters to pass between the scripts. Everything works. Agree on Rock Star status for Darren. Thank you.

---

<div class="post-metadata">

**Author:** ![Nitesh\_Aggarwal](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nitesh_aggarwal/32/19941_2.png) [@Nitesh\_Aggarwal](https://community.glideapps.com/u/Nitesh_Aggarwal)\
**Post date:** [January 24, 2021, 8:48pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/44 "2021-01-24T20:48:23Z")

</div>

@gp9293 Can you please share code … mine is not working with if(e.changeType == ‘INSERT\_ROW’), BUT without it its working fine …  
My whole code :  
function onChange(e){  
if(e.changeType == ‘INSERT\_ROW’){  
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);  
sh.getRange(“B10”).setValue(100)  
}  
else {  
return;  
}  
}

var sheet = SpreadsheetApp.getActive();  
ScriptApp.newTrigger(“onChange”)  
.forSpreadsheet(sheet)  
.onChange()  
.create();

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [January 24, 2021, 9:10pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/45 "2021-01-24T21:10:10Z")

</div>

Glide can’t directly call a function with a parameter. You need to set up an onChange trigger in the google sheet that calls the function. That is the only trigger that will work other than a time based trigger. Unfortunately any change in the sheet or via glide will execute the onChange trigger, so you would have to change your logic to determine if a row is new or not. Maybe set a flag in a column if the new row has been processed by the script and then skip any rows that have the value set. The function can be named anything you want, but cannot have any parameters like (e).

What are you trying to achieve with your script? Is is something that could be done in Glide instead? Looks like you are just setting a single value in a single cell?

---

<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 24, 2021, 11:08pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/46 "2021-01-24T23:08:21Z")

</div>

As @Jeff_Hager says, make sure you are running the trigger.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/96552_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 25, 2021, 6:33am UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/47 "2021-01-25T06:33:36Z")

</div>

> [@Jeff\_Hager](#):
>
> The function can be named anything you want, but cannot have any parameters like (e).

umm, that’s not true.  
There _is_ an event object that can be accessed when an onChange event has fired.  
You can use it to discover (for example) the `changeType` and the `source` (the latter isn’t documented, but it _is_ available - I use it regularly to discover which sheet and/or range triggered the event)

In this case…

> [@Nitesh\_Aggarwal](#):
>
> if(e.changeType == ‘INSERT\_ROW’){

That’s only ever going to be true if the change resulted in a new [row being added to the bottom of the sheet.](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/14)

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [January 25, 2021, 12:25pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/48 "2021-01-25T12:25:45Z")

</div>

Really? I thought the triggers didn’t pass an object. I thought that an object was only passed if you are working directly within the sheet. Might have to play around with that again.

---

<div class="post-metadata">

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

</div>

Simple test…

Add the following as an onChange trigger:

```
function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  var change = event.changeType;
  console.log([sheetname, change]);
}

```

Then do something in the app that causes a new row to be added, and check the logs. This is what I see:

`Jan 25, 2021, 8:36:54 PM	Debug	['Sheet3', 'EDIT']`

---

<div class="post-metadata">

**Author:** ![Nitesh\_Aggarwal](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nitesh_aggarwal/32/19941_2.png) [@Nitesh\_Aggarwal](https://community.glideapps.com/u/Nitesh_Aggarwal)\
**Post date:** [January 25, 2021, 1:29pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/50 "2021-01-25T13:29:46Z")

</div>

@Jeff_Hager @Darren_Murphy The above code I sent is placed in Script Editor in Google sheet itself and not sent through glide. Yes I am placing a value in a cell through script. But I want this value on a single cell and not after that …  
I tried onEdit as well that executed on each edit in a sheet but I want only when New Row is added via Glide app … I tried set column Action, but it is not working and somebody already reported a bug for that (Add row & Set column) Add row places a row in Glide table, but did not add row in Google sheet…

---

<div class="post-metadata">

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

</div>

> [@Darren\_Murphy](#):
>
> > [@Nitesh\_Aggarwal](#):
> >
> > if(e.changeType == ‘INSERT\_ROW’){
> 
> That’s only ever going to be true if the change resulted in a new [row being added to the bottom of the sheet.](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/14)

@Nitesh_Aggarwal this ⬆ is most likely the reason why your script is not executing. (Or more correctly, it probably is executing, but it isn’t getting past that conditional)

---

<div class="post-metadata">

**Author:** ![Nitesh\_Aggarwal](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nitesh_aggarwal/32/19941_2.png) [@Nitesh\_Aggarwal](https://community.glideapps.com/u/Nitesh_Aggarwal)\
**Post date:** [January 25, 2021, 1:47pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/52 "2021-01-25T13:47:32Z")

</div>

> [@Darren\_Murphy](#):
>
> That’s only ever going to be true if the change resulted in a new [row being added to the bottom of the sheet.](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/14)

Yes .. row is adding at bottom of sheet and all the rows below it are Empty (not existed)

---

<div class="post-metadata">

**Author:** ![Nitesh\_Aggarwal](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nitesh_aggarwal/32/19941_2.png) [@Nitesh\_Aggarwal](https://community.glideapps.com/u/Nitesh_Aggarwal)\
**Post date:** [January 25, 2021, 1:59pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/53 "2021-01-25T13:59:58Z")

</div>

@Darren_Murphy@Jeff_Hager **My simple use case is** … I have Blank sheet (with no rows) and 2 columns ( A & B) … I just want to set value of A via form (user input) and add Default value of 100 to column B (without asking to user) … and I need this **only for the first row** …  
And yes **obviously** when form is submitted I need only 1 row to be created in Google sheet as well as in Glide Sheet …

| A | B |
| --- | --- |
| User Input | No user input value & Default Value = 100 |
| User Input | No userinput value & No default Value |
| User Input | No userinput value & No default Value |
| User Input | No userinput value & No default Value |

---

<div class="post-metadata">

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

</div>

> [@Nitesh\_Aggarwal](#):
>
> Yes … row is adding at bottom of sheet and all the rows below it are Empty (not existed)

Just so we are clear, please consider the following:

![Screen Shot 2021-01-25 at 10.13.21 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/a/4a9fd01037605c31a672bb8b4398657cc616190e.png)

- If Glide adds a row at rows 4, 5, 6, 7, 8 or 9, the changeType will be `EDIT`
- If Glide creates a new row (10), the changeType will be `INSERT ROW`

---

<div class="post-metadata">

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

</div>

> [@Nitesh\_Aggarwal](#):
>
> @Darren_Murphy @Jeff_Hager **My simple use case is** … I have Blank sheet (with no rows) and 2 columns ( A & B) … I just want to set value of A via form (user input) and add Default value of 100 to column B (without asking to user) … and I need this **only for the first row** …  
> And yes **obviously** when form is submitted I need only 1 row to be created in Google sheet as well as in Glide Sheet …

Um, why do you need a script for this?  
Why not use a Set Columns action when the user submits the form?  
If you only need it for the first row, you can create a rollup that counts the number of rows, and only do the Set Columns action if that count = 1.

---

<div class="post-metadata">

**Author:** ![Nitesh\_Aggarwal](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nitesh_aggarwal/32/19941_2.png) [@Nitesh\_Aggarwal](https://community.glideapps.com/u/Nitesh_Aggarwal)\
**Post date:** [January 25, 2021, 2:25pm UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/56 "2021-01-25T14:25:53Z")

</div>

Set column is not working ☹

> **[Untitled spreadsheet](https://accurate-need-0239.glideapp.io/)**

copy demo app and see yoursef … or try it on your own sheet … Thanks !

---

<div class="post-metadata">

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

</div>

Okay, I see.

Alright, getting back to your script, here is a version that will work:

```
function on_change(event) {
  var sheet = event.source.getActiveSheet();
  var sheetname = sheet.getName();
  if (sheetname == 'Sheet1') {
    sheet.getRange(2,2).setValue(100);
  }
}

```

Note that will reset that cell to 100 every time there is a change to that sheet. I’m not sure if that’s exactly what you want.

---

<div class="post-metadata">

**Author:** ![Nitesh\_Aggarwal](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nitesh_aggarwal/32/19941_2.png) [@Nitesh\_Aggarwal](https://community.glideapps.com/u/Nitesh_Aggarwal)\
**Post date:** [January 26, 2021, 11:08am UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/58 "2021-01-26T11:08:21Z")

</div>

Thanks ! Though I want it to be executed only for the first time only … but it still works !  
Thanks a lot.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/96552_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 26, 2021, 11:13am UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/59 "2021-01-26T11:13:37Z")

</div>

Okay, so use this:

```
function on_change(event) {
  var sheet = event.source.getActiveSheet();
  var sheetname = sheet.getName();
  if (sheetname == 'Sheet1') {
    var check = sheet.getRange(2, 2).getValue();
    if (!check) {
      sheet.getRange(2, 2).setValue(100);
    }
  }
}

```

That will only set the value in B2 if it is empty.

---

<div class="post-metadata">

**Author:** ![Nitesh\_Aggarwal](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nitesh_aggarwal/32/19941_2.png) [@Nitesh\_Aggarwal](https://community.glideapps.com/u/Nitesh_Aggarwal)\
**Post date:** [January 26, 2021, 11:25am UTC](https://community.glideapps.com/t/google-sheets-built-in-on-form-submit-trigger/2542/60 "2021-01-26T11:25:22Z")

</div>

Thanks A lot … you are Genius !!! 🙂 ✌ 💯

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

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