# OnChange trigger

**URL:** <https://community.glideapps.com/t/onchange-trigger/16970>\
**Category:** Ask for Help\
**Created:** [October 9, 2020, 9:57pm UTC](https://community.glideapps.com/t/onchange-trigger/16970 "2020-10-09T21:57:20Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Maryth\_R](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/maryth_r/32/13091_2.png) [@Maryth\_R](https://community.glideapps.com/u/Maryth_R)\
**Post date:** [October 9, 2020, 9:57pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/1 "2020-10-09T21:57:20Z")

</div>

How to make an onChange google sheets trigger run a clear contents script in sheet B when any changes are made to a specific cell in sheet A?

Lets say that:  
I want the cell trigger to be located in sheet A and I need the script to clear contents located in sheet B.

How do I do that with the onChange trigger option?

Thank you.

---

<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:** [October 9, 2020, 10:50pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/2 "2020-10-09T22:50:03Z")

</div>

A pseudo-code would be like this.

For any events made, grab the event location cell and determine if it’s in sheet A.

If it’s in sheet A fhen proceed else stop.

If proceed, grab sheet B, then grab the determined range and clear.

Script stops there.

---

<div class="post-metadata">

**Author:** ![Maryth\_R](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/maryth_r/32/13091_2.png) [@Maryth\_R](https://community.glideapps.com/u/Maryth_R)\
**Post date:** [October 9, 2020, 11:53pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/3 "2020-10-09T23:53:52Z")

</div>

Hi ThinhDinh  
Thanks for the quick reply.  
I do not know where to start with the pseudo-code XD. The reality is I have no business trying to add scripts into a google sheet (I am a landscaper) but I am ready to learn.

This is all I have in the script editor:

function ClearContentScript2() {  
var ss = SpreadsheetApp.getActiveSpreadsheet();  
var sheet = ss.getSheetByName(“4.2.1 Estimate Approved Button 1st step for zap”);

var range = sheet.getRange(“A2:C100”);  
range.clearContent();  
}

I found this script while googling “how to clear contents while still maintaining the format”.

I need to be able to type a new estimate quickly (yep for my landscaping jobs lol). And the thing is that it was great to be able to add items to the estimate but then and I found myself into a pickle when trying to create a new one, in the app, I am taking forever to erase the old info.

I need it to be able to clear all the past data before I can write a new estimate, erasing it manually in front of the customer is a pain, so I am trying to find a way to do this “automatically” by maybe having a button that I can use to make some quick cell change, maybe a simple yes/no, or even just writing anything a letter…anything, and then (ideally) just making a simple change in a specific cell would trigger the clearing content script in a separate sheet (the sheet where I write the estimates at) and then the entire “estimates page” would be clean ready for a new estimate.

---

<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:** [October 10, 2020, 3:00am UTC](https://community.glideapps.com/t/onchange-trigger/16970/4 "2020-10-10T03:00:33Z")

</div>

If you have a sheet with dummy data I will try to jump in to help.

---

<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:** [October 10, 2020, 3:10am UTC](https://community.glideapps.com/t/onchange-trigger/16970/5 "2020-10-10T03:10:30Z")

</div>

You need to setup a trigger to fire your function when an edit is made.

You could create a second function that looks something like:

```
function on_sheet_edit(event) {
  var range = event.range;
  if (range == 'range to edit to fire trigger') {
    ClearContentScript2();
  }
}

```

Then add a trigger to fire that function when an edit is made.  
To add a trigger, go to the script editor and click on the icon that looks like a timer:

![Screen Shot 2020-10-10 at 11.04.42 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/6/6d52f3c5b0b77028943aecbd1cfbacbeff746514.png)

Then click on “Add Trigger”, and select your function to run when an event type is “On edit”. It will look like so:

 ![Screen Shot 2020-10-10 at 11.05.14 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/8/8b2a026f2e7b7473f225ecb4e14f02ad55c40f49.png)

HTH,  
Darren

---

<div class="post-metadata">

**Author:** ![Maryth\_R](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/maryth_r/32/13091_2.png) [@Maryth\_R](https://community.glideapps.com/u/Maryth_R)\
**Post date:** [October 10, 2020, 3:37pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/6 "2020-10-10T15:37:44Z")

</div>

Thank you ThinhDinh

here is the dummy data (it is set to Anyone on the internet with this link can edit).

> **[Copy of Estimate zap](https://docs.google.com/spreadsheets/d/1NkDCbXP0_59ZG7BORMmoDQwQr0sDws8g1S_iZZVo2C0/edit?usp=sharing)**
>
> Form Responses 2
> 
> Timestamp,Would you like to create a new estimate for a new customer?
> 10/9/2020 14:29:45,Yes
> 10/9/2020 14:29:50,No
> 10/9/2020 14:45:16,Yes
> 10/9/2020 14:52:47,Yes
> 10/9/2020 16:16:27,No
> 10/9/2020 16:17:22,Yes
> 10/9/2020...

---

<div class="post-metadata">

**Author:** ![Maryth\_R](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/maryth_r/32/13091_2.png) [@Maryth\_R](https://community.glideapps.com/u/Maryth_R)\
**Post date:** [October 10, 2020, 3:48pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/7 "2020-10-10T15:48:28Z")

</div>

> **[Copy of Estimate zap](https://docs.google.com/spreadsheets/d/1NkDCbXP0_59ZG7BORMmoDQwQr0sDws8g1S_iZZVo2C0/edit?usp=sharing)**
>
> Form Responses 2
> 
> Timestamp,Would you like to create a new estimate for a new customer?
> 10/9/2020 14:29:45,Yes
> 10/9/2020 14:29:50,No
> 10/9/2020 14:45:16,Yes
> 10/9/2020 14:52:47,Yes
> 10/9/2020 16:16:27,No
> 10/9/2020 16:17:22,Yes
> 10/9/2020...

I have tried the onEdit but I have encountered the same issue as when I tried the onChange trigger. it deletes it, but the problem is that it keep deleting everything while I am writing anything down. So, I thought that maybe having the trigger cell in a separate sheet may solve this problem but I do not know how to make that happen.  
I have tried to use the trigger for when a google form is submitted, it only works when I did it directly from the google form (which I cannot make it work from the glide app, I tried to open the form as a link, it did not work. It did not work either when I used the glide app to submit the google form responses into its responses google sheet, the script may only be triggered when submitting the response from the google form itself, it ignores whatever changes are made directly into the response sheet).

---

<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:** [October 10, 2020, 4:19pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/8 "2020-10-10T16:19:21Z")

</div>

```
function ClearContentScript2(e) {
  var ActiveSheet = e.source.getActiveSheet().getName();
  var ActiveRow = e.source.getActiveCell().getRow();
  var ActiveColumn = e.source.getActiveCell().getColumn();
if(ActiveSheet == '4.2.0 Trigger onChange Cell Location is B4' && ActiveRow == 4 && ActiveColumn == 2){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("4.2.1 Estimate Approved Button 1st step for zap");
  var range = sheet.getRange("A2:C100");
  range.clearContent();
}
}

```

I have edited a working version for you and tested with my trigger.

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/e/e703112277f26f630c63955a759cc133e78449c5.png)

You can change your trigger now, I will delete mine.

---

<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:** [October 10, 2020, 4:20pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/9 "2020-10-10T16:20:14Z")

</div>

The logic behind this is:

- On any event that makes a change to the sheet, get the Sheet name, the column and row of the cell that has the change.

- If it is exactly cell B4 in the sheet you chose, then proceed to delete, else stop.

---

<div class="post-metadata">

**Author:** ![Maryth\_R](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/maryth_r/32/13091_2.png) [@Maryth\_R](https://community.glideapps.com/u/Maryth_R)\
**Post date:** [October 10, 2020, 4:46pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/10 "2020-10-10T16:46:34Z")

</div>

> [@ThinhDinh](#):
>
> ```auto
> function ClearContentScript2(e) {
> var ActiveSheet = e.source.getActiveSheet().getName();
> var ActiveRow = e.source.getActiveCell().getRow();
> var ActiveColumn = e.source.getActiveCell().getColumn();
> if(ActiveSheet == '4.2.0 Trigger onChange Cell Location is B4' && ActiveRow == 4 && ActiveColumn == 2){
> var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("4.2.1 Estimate Approved Button 1st step for zap");
> var range = sheet.getRange("A2:C100");
> range.clearContent();
> }
> }
> 
> ```

Thank you SO much 🙂 !!!

---

<div class="post-metadata">

**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\
**Post date:** [November 10, 2020, 6:51pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/11 "2020-11-10T18:51:35Z")

</div>

Is anyone else now having problems with OnChange triggers working with Glide? All of a sudden mine no longer work. I thought maybe the script needed to be modified due to changes so I used Thinh’s script but still same issue. does not work unless I make the change amnually on the sheet myself. I use the form submission to add the data. The data gets added to column 35. Here is the Thinh way.  
function ClearContentScript2(e) {  
var ActiveSheet = e.source.getActiveSheet().getName();  
var ActiveColumn = e.source.getActiveCell().getColumn();  
if(ActiveSheet == ‘ADVISOR INFORMATION’ && ActiveColumn == 35 ){  
var spreadsheet = SpreadsheetApp.getActive();  
spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘SEARCH’), true);  
spreadsheet.getRange(‘W22’).clear();  
spreadsheet.getRange(‘U2:U22’).clear();  
spreadsheet.getRange(‘W2:W22’).clear();  
spreadsheet.getRange(‘F2:F22’).clear();  
spreadsheet.getRange(‘K22’).clear();  
spreadsheet.getRange(‘I22’).clear();  
spreadsheet.getRange(‘L22’).clear();  
spreadsheet.getRange(‘N22’).clear();  
spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘MILEAGE SEARCH RESULTS’), true);  
spreadsheet.getRange(‘W2:Z7’).clear();  
spreadsheet.getRange(‘AM2:AN7’).clear();  
}  
}

And my old way:  
function onChange() {  
var s = SpreadsheetApp.getActiveSheet();  
if( s.getName() == “ADVISOR INFORMATION” ) { //checks that we’re on the correct sheet  
var r = s.getActiveCell();  
if( r.getColumn() == 35 ) { //checks the column  
var nextCell = r.offset(0, 1);  
if( nextCell.getValue() === ‘’ ) //is empty?  
var spreadsheet = SpreadsheetApp.getActive();  
spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘ADVISOR INFORMATION’), true);  
spreadsheet.getRange(‘AI2:AI1000’).clear();  
spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘SEARCH’), true);  
spreadsheet.getRange(‘W22’).clear();  
spreadsheet.getRange(‘U2:U22’).clear();  
spreadsheet.getRange(‘W2:W22’).clear();  
spreadsheet.getRange(‘F2:F22’).clear();  
spreadsheet.getRange(‘K22’).clear();  
spreadsheet.getRange(‘I22’).clear();  
spreadsheet.getRange(‘L22’).clear();  
spreadsheet.getRange(‘N22’).clear();  
spreadsheet.setActiveSheet(spreadsheet.getSheetByName(‘MILEAGE SEARCH RESULTS’), true);  
spreadsheet.getRange(‘W2:Z7’).clear();  
spreadsheet.getRange(‘AM2:AN7’).clear();  
};  
}  
}

Any idea as to why this no longer works?

---

<div class="post-metadata">

**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\
**Post date:** [November 13, 2020, 9:12pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/12 "2020-11-13T21:12:07Z")

</div>

Did you find a solution to this? I’m having an issue with an onChange trigger too.

---

<div class="post-metadata">

**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\
**Post date:** [November 13, 2020, 10:44pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/13 "2020-11-13T22:44:13Z")

</div>

No, but with the action combos you can clear cell contents as soon as you place them elsewhere. But , in this scenario it doens’t help me.

---

<div class="post-metadata">

**Author:** ![christoph](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/christoph/32/22794_2.png) [@christoph](https://community.glideapps.com/u/christoph)\
**Post date:** [October 13, 2021, 9:40am UTC](https://community.glideapps.com/t/onchange-trigger/16970/14 "2021-10-13T09:40:55Z")

</div>

It seems that I have the same problem now, how have you solved it? Thanks.

---

<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:** [October 13, 2021, 11:32am UTC](https://community.glideapps.com/t/onchange-trigger/16970/15 "2021-10-13T11:32:04Z")

</div>

So you have a trigger that isn’t firing?

Are you using an onChange trigger or an onEdit trigger?

---

<div class="post-metadata">

**Author:** ![christoph](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/christoph/32/22794_2.png) [@christoph](https://community.glideapps.com/u/christoph)\
**Post date:** [October 13, 2021, 5:35pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/16 "2021-10-13T17:35:33Z")

</div>

I only use onChange triggers. I’m using a button with a combined action with several SetColumn actions in a sequence, with one of them changing a value in a “trigger” column. And the onchange script looks if a certain sheet has the right value in that column.  
My impression is, that sometimes the onChange event is not been fired for all sheets which have changed in that sequence. Would this be possible?

---

<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:** [October 13, 2021, 5:38pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/17 "2021-10-13T17:38:20Z")

</div>

> [@christoph](#):
>
> My impression is, that sometimes the onChange event is not been fired for all sheets which have changed in that sequence. Would this be possible?

I’m not sure, but I’d imagine if you add some logging to your trigger it should be possible to figure out what’s going on. eg. get it to log the sheetname every time it fires, for example.

---

<div class="post-metadata">

**Author:** ![christoph](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/christoph/32/22794_2.png) [@christoph](https://community.glideapps.com/u/christoph)\
**Post date:** [October 13, 2021, 6:35pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/18 "2021-10-13T18:35:08Z")

</div>

Good idea 🙂

---

<div class="post-metadata">

**Author:** ![christoph](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/christoph/32/22794_2.png) [@christoph](https://community.glideapps.com/u/christoph)\
**Post date:** [October 13, 2021, 10:57pm UTC](https://community.glideapps.com/t/onchange-trigger/16970/19 "2021-10-13T22:57:39Z")

</div>

Through the action I add values to three cells and the onChange trigger fires, and I see the correct values in the sheet, but when the onChange script reads the data in that cells, there are no values in them. Very strange!

---

<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:** [October 14, 2021, 1:28am UTC](https://community.glideapps.com/t/onchange-trigger/16970/20 "2021-10-14T01:28:32Z")

</div>

That is strange indeed. Are you 100% certain it’s reading from the cell you think it is?

[Next page](https://community.glideapps.com/t/onchange-trigger/16970.md?page=2)