INTRO: Using onChange to Trigger Specific Events
HTML-код
- Опубликовано: 9 сен 2024
- This is an introduction to the concept of using onChange to trigger specific events as opposed to onEdit.
The challenge: onEdit() doesn't fire when the change is the result of a formula, including IMPORTRANGE(), GOOGLEFINANCE(), etc. and onChange() doesn't include the source, range, or value properties.
Important notes:
* onChange only runs when the Sheet is open; the Sheet will not update "in the background"
* You can call the function whatever you want; onChange is an installed trigger and must be installed from the Triggers section, which means it can be applied to any function
* You must use the PropertiesService before the onChange to set each key-value pair so the onChange script has a starting value
* Using the cell A1 notation as the key name makes it easier to process since the SpreadsheetApp can access the range via the key name
IMPORTRANGE Source Sheet (to copy from File : Make a copy)
docs.google.co...
Testing sheet with script (to copy from File : Make a copy)
docs.google.co...
* Remember to update the IMPORTRANGE sheet ID
Script:
function onChange(e){
Logger.log(e.changeType);
if(e.changeType != "OTHER") return;
let props = PropertiesService.getScriptProperties().getProperties();
const sh = SpreadsheetApp.getActive();
const ss = sh.getActiveSheet();
let r, val;
for (let p in props){
Logger.log(p);
Logger.log(props[p]);
r = ss.getRange(p);
val = r.getValue();
if(val != props[p]){
r.offset(1,0).setValue(props[p]);
PropertiesService.getScriptProperties().setProperty("A2",SpreadsheetApp.getActive().getActiveSheet().getRange("A2").getValue()); // run for each relevant cell
}
}
}
function setupProperties(){ // run for each relevant cell
PropertiesService.getScriptProperties().setProperty("A2",SpreadsheetApp.getActive().getActiveSheet().getRange("A2").getValue());
}
function retrieveProps(){
Logger.log(PropertiesService.getScriptProperties().getProperty("A2"));
}
Connect with me:
• spencer.farris@gmail.com
• spencerfarris.me
• www.linkedin.com/in/spencer-farris/
• Twitter @FarrisSpencer
• Google Product Expert support.google.com/docs/profile/12305
This video gave me answers what I was searching for last 2 days. It just made my day because I had almost given up on the project I was working on until I saw this tutorial. Obviously I watched the 'Properties and Cache services' tutorial as well to completely grasp the whole concept. You are a fantastic tutor as well. Thanks!
Thanks for the Video Brother 😊
Spencer, thank you very much for sharing your knowledge.
I have modified the function so that it looks at a specific cell on a specific sheet.
thanks again.
Greetings from Argentina.
how
Can you also make a video on how to make an onChange trigger from programmatically added entries to Google Sheets? For example entries that came in from AppSheet to Google Sheets. Thanks in advance!
Great video bro
I wanna ask
Can it applies on every new cell on column that is generated by formula?
I want that every new cell value will trigger webhook, and sending this value to telegram straight away.
But it can't be done when it's generated by formula.
I do appreciate if you can help me figure this out
Thanks
Idan
How can I have the script send an email when triggered by a formula, just like when I use the onEdit script?
Thanks for this great Vid, really could have used this 1 a while ago.
Glad you've found it now!
can you help me build a script to automatically create last modified timestamp on specific imported range cell in the left/right of it?
I am new to using scripts. I think this is amazing, but how do you limit it to a change in a single cell? Then could you have multiple onChange in statements for other single cell within the same sheet?
This script is designed to only affect cell A2
I copied your code exactly but my importrange onchange trigger failed citing "Please select an active sheet first"?
I have no idea what error that is, haha.
Can you add me as an editor on the sheet?
So, onEdit does not detect formatting changes to a cell; whereas onChange can. The issue for me is onChange will only report the changeType and that's it. Do you know of a way to find the cell that the changeType == 'FORMAT' occurs? I have an onEdit trigger running on the entire sheet, and format changes will not trigger it, so I cannot detect the cell event from it.
Nevermind, I figured it out.
Hi new to all of this, based on this concept and building on it, could then "OnChange" ultimately trigger and onEdit with a formula? The Formula I have in mind being an array that calculates duration (time passed) from when a time stamp was created) Ideally it makes sense, but making it work might prove much harder. I am surprised google differentiated between both changes and limited this type of automanous updates in google sheets.
Great content! Thank you! Just one query, how about if I'm applying this into a range or column and not in a specific cell?
I've never done it with larger than a single cell, so I'm kinda spitballing here. I would try to set the entire range of values to the PropertiesService and use JSON to extract and compare strings.
How can you utilize this OnChange if the value changing is meant to move the Row to another tab??
Where are you stuck?
@@SpencerFarris I dont know! XD this is my humble code
function onEdit(e){
let r = e.range;
if (r.columnStart != 48 || r.rowStart == 1 || e.value == null) return;
const sh = SpreadsheetApp.getActive();
const valArray = ["Production Board","DELIVERY","ARCHIVE"];
const destArray = ["ACTIVE PRODUCTION","DELIVERY","ARCHIVE"];
let dest = sh.getSheetByName(destArray[valArray.indexOf(e.value)]);
let src = sh.getActiveSheet();
if (dest.getName() == src.getName()) return;
src.getRange(r.rowStart,1,1,84).moveTo(dest.getRange(dest.getLastRow()+1,1,1,84));
src.deleteRow(r.rowStart);
}
HOW can I switch it to be OnChange??
@@mauryhernandez2297 The biggest thing is getting the original values into the Properties Service and comparing against that to see if the script should run. Have you tried that?
Getting error "Error
TypeError: Cannot read property 'changeType' of undefined
onChange @ Code.gs:2"
Please suggest what to do.
Thanks
I'm assuming you ran the code. As shown in the video, I do not manually run it. Rather, it runs automatically whenever the appropriate type of change occurs.
Hi Spencer how can i integrate this function with one from your other video about function sendMailEdit(e)?
I have column that output is if formula and i want to send email alert for every row when formula change that output to "Approved" can you help me with that? unfortunately I don't know how do it on my own as i'm very basic with JS
What have you written so far?
Spencer - do you freelance consult? I have a simple (for you), yet wildly complex (for me) trigger to solve. I've tried to follow along for onChange...I was able to replicate your onEdit script for my needs, but I need to trigger an email based off a calculated field.
I do. spencer.farris@gmail.com
Is there a way to get the data of the actual change from the event object? I see onChange's event object also has a "source" property, and in it there is a "getChanges" function. But I can't find any mention of this function on Google's site.
There is not, hence this workaround. It's unfortunate, but is what it is.
What happens when you want to set the values of old values of a whole table and then add then you want to compare them with the one ones?
I apologize I don't understand what you mean
Thanks a lot for the response.@@SpencerFarris I should have explained that better. Let me rephrase. In your video, the ImportRange function changes the value of A2 cell. In my case, the ImportRange changes the values of a A2:F100 range. I was wondering how we can store all the old values of the A2:F100 range into PropertiesService and then compare them with the updated A2:F100 range. This is similar to Janine's question below. I hope this explains it better, and hopefully that could help others with the similar questions.
first of all - THANK YOU! you're video is truly awesome!
is there any chance you have a video about "bulk pasting value" with onEdit?
I made a script that make timestamps whenever a cell is edited but I'm getting lost trying to find solutions to cases someone paste a lot of values in the designated column. (I guess I don't fully understand how to use loops in apps script yet)
I'll look at making one, thank you!
How to write a function to change minutes in the trigger .. I want to trigger data from my sheet for every 3 mins instead of 5 minutes...please share the script if u can help me
Hi Gopinath. I don't understand what you're looking for, sorry. Can you explain more?
@@SpencerFarris in Google sheet, while creating trigger for my function, in minuter timer only options like 1min, 5 min, 10 min etc.. but I have to trigger my data for every 3 mins.. is it possible to do?
@@tradingbots9187 Gotcha! Yes-ish. So what you'd do is set a minute timer then check every minute if it is the third minute. If it is, continue, if not, escape.
@@SpencerFarris thank u understood..
Awesome Vidoe:), Question: How do keep the log gong father down the sheet, like for example 100 rows? Thanks
Hi Jaylon. I'm happy to help, but don't understand what you're looking for.
Like I want to keep track of a changing cell just like you showed in the video with your changing price. Instead of only keeping track of the previous value I would like to see the 1-10th previous values. Hope this makes sense
@@jaylonnichols5504 Send me an email. I'd like to figure this out if possible, but I'm not tracking. Apologies.
Hiii thanks for this awesome script i have found only at your channel. If you can give the same script for moving rows it will be very helpful to me
This is just an intro to the idea of using onChange. You are free to make it do whatever you want
hi Spencer. I would like to use getUi.alert function when it detect onChange trigger. but i receive this error "Exception: Cannot call SpreadsheetApp.getUi() from this context. at onChange(Code:40:22)" . can you help me?
Can you please add me as an editor on your sheet?