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!
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.
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.
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
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?
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.
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.
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)
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.
@@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?
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
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
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.
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.
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.
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
@@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.
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?
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!
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
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.
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!
The same process should work. Are you having issues?
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
Sorry it's been SO LONG. Do you still need help?
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
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?
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.
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.
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!
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.
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?
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.
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
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.
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.
Glad you did. Sorry I didn't get back to 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..
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?