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.
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.
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.
If you have a sheet with dummy data I will try to jump in to help.
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:
Then click on “Add Trigger”, and select your function to run when an event type is “On edit”. It will look like so:
HTH,
Darren
Thank you ThinhDinh
here is the dummy data (it is set to Anyone on the internet with this link can edit).
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).
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.
You can change your trigger now, I will delete mine.
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.
Thank you SO much 
!!!
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?
Did you find a solution to this? I’m having an issue with an onChange trigger too.
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.
It seems that I have the same problem now, how have you solved it? Thanks.
So you have a trigger that isn’t firing?
Are you using an onChange trigger or an onEdit trigger?
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?
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.
Good idea 
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!
That is strange indeed. Are you 100% certain it’s reading from the cell you think it is?
