@@saperis My French got in the way, that came out wrong. You look very smart and I'd hate to be in argument with you. Ok, that's more like it. Cheers from Vancouver :)
@@saperis video suggestion: working with strings in google scripts! For me, beginning, its really hit and miss if any given function will work. Just getting the id of sheet turns out a hassle (.match not recognized, etc). Cheers
Thanks - that was very helpful. I've written code for data analysis for several years, but I'm just starting to use scripts and macro's in Google, and getting my triggers to work as expected has been a problem. This answers some of my questions
@@saperis well the content is still relevant 🙂 And I meant a high-pass filter not a low-pass filter, sorry. The hum is loud on good headphones but would probably not be heard on lower quality equipment. Have a nice day !
I like your excellent explanation.👍❤ i have a question I want to send emails on a specific date. I want to send an email This should work after 3 days from the date of customer registration and continuously. For example " 2022.07.13 after registration then message should be sent on 16.07.2022. Date may change because customers can register anytime") How to send email with conditions.🤔 Please tell me how to do it Thank you very much 😊
I've never done anything like this. In theory, you would need to write a script that instantiates it's one time-based trigger. None of the default time-based triggers runs every three days. So again, you would have to write something yourself.
Nice video--Just FYI that the link you mention for learning more deeply about GoogleSheet automation doesn't seem to be working, and the link from your comments shows a course "Productivity for Creatives with Google Workspace" (same on domestika), but not one for automation.
@@saperis I am just beginning to look into Google’s new AppSheet. Can’t wait to see how I can use it to automate some of my workflow. Have you begun using it yet?
I checked it out a couple of months ago but I don't actually use it. I might start creating some videos on it in the future but I'm not sure about it yet.
Great video, I want to set up a trigger to be run every 72 hours. Let’s say I create the trigger, now what should I do when setting up the trigger using that window??
Nothing. It will run just as you set it. That's why it's time-based. It runs based on the time you have defined, without you having to do anything else.
@@saperis thanks for your response. The issue here is google doesn’t allow for every 3rd day or 72 hours option so I was just wondering how would I go about it’s any help would be appreciated.
Thank you alot for this useful video, making my understanding structured. 👍 Just do you know how to get a summary of all Google Apps Script triggers in my account? I start to use triggers little by little with lots of test projects 😜 I hope one day I can find out all my deployed triggers in order to remove useless ones.
You would probably have to build a script yourself that tracks how many times a script ran. There is no default way of running your script for X times.
That was great. Thanks. I’m making a Glide app. Would your Google Apps Script work with the Glide app platform? I was considering it as an option for a email response to users, authorizations, and anything else useful.
I've never tried out Glide apps. But it looks like other people are using it together with Google Apps Script: dev.to/harishkotra/apps-script-rest-apis-google-sheets-glide-apps-42h6
Love your explanations! I am trying to use the openById method (in a bound script) to open one of my other spreadsheets, but keep getting a permission error. Discussion boards say that the way around the problem is to use a "trigger". What does this mean? (Note: I'm brand new to Google Apps Script -- this is my 2nd day!)
I'm guessing you don't have access to the file you are trying to open. I believe you would need at least viewing rights on that document. Check this video to find out what triggers are: czcams.com/video/KC7pBjD3GGw/video.html
So, here is the thing. I wish to place inside a cell the formula =now() and see the clock updating its seconds in real time, using the format "hh:mm:ss" from a fixed cell. It can be done in excel. I wonder if it can be done in Google Sheets, I thought that if I could trigger the show event every second I could have a clock effect, but such option is not at sight (seconds). Any suggestion?
There's a NOW() formula in Sheets too: support.google.com/docs/answer/3092981?hl=en#:~:text=NOW%20provides%20the%20current%20date,when%20it%20was%20first%20entered. This formula updates every time the spreadsheet is edited. In theory, you could set the the NOW() formula with an Apps Script function you write and use a time-based trigger to update it. But I think you might quickly hit a quota limit if you run the script every second. See this video to find out how to set a formula with Apps Script: czcams.com/video/aYG55AlprHE/video.html
HI, I have a question, I create a code linking form, spreadsheet, and calendar using onFormSubmit() which it will map the leave request date on the calendar once the form is submitted. The code works just fine. But when I add some IF ELSE function for the drop-down in the form, it doesn't work. Which trigger I should use for this?
Do you expect the script to run because you manually add an IF/ELSE Sheets function? Or do you mean that after adding the IF/ELSE function to your Google Sheet, the onFormSubmit() trigger doesn't work anymore whenever someone submits the form?
No, because the onEdit trigger is a simple trigger. Such simple triggers can't send out emails. Find out more in the official documentation: developers.google.com/apps-script/guides/triggers
Interesting Video! I have a time trigger (weekly fires) that activate a function that contains an installable trigger. In some google sheets the weekly trigger does not fire, in some it does. What could be the reason that it not fires?
No idea what might be causing this. Many people have issues with time-based triggers not working as expected. Found this thread on Stackoverflow that might be interesting: stackoverflow.com/questions/59175431/time-driven-triggers-not-working-properly
@@saperis Thanks for fast respons! In between I solved the problem! The files, where the triggers did not work, are corrupt. After replacing these files everything works fine. Many times is this the solution for time driven triggers...
According to the Apps Script documentation you can. Read the details here: developers.google.com/apps-script/add-ons/concepts/workspace-triggers#installable_triggers But I have never built an add-on myself yet so I can't speak from experience about this topic.
Great video. However I still don't understand how to make an installable trigger with event source "From spreadsheet" when it is not a bound script. Any idea?
Check this section of the Google Apps Script documentation for an answer: developers.google.com/apps-script/guides/triggers/installable#application_triggers
@@saperis Thank you for your answer. Honestly, I think I've read all of the Google documentation so far. When I create a new project (not bounded) then there is no option "From spreadsheet" in "Select event source" select. When I changed the manifest file, this option appeared however I had to specified what function I want to run on "homepageTrigger". And of course I get application icon in right side panel (what I don't want to have). I really have no idea what can I do and reading Google documentation for last five days hasn't helped me at all.
Hi! I have several onEdit triggers but when I duplicate the sheet, all of it are gone on Triggers and have to manually add all of it again on the duplicate sheet. Is there a way that those onEdit triggers won't be removed once I create a copy of the original sheet? Thank you.
Maybe adding the onEdit trigger in the script instead of through the user interface, could help solve this issue: developers.google.com/apps-script/guides/triggers#onedite
Thanks for the video! Can you help me with code to let my script determine whether the trigger source from a particular run event was from CLOCK or from manual (e.g. pressing Debug or Run)? I thought it was getEventTrigger but it seems to return all the scheduled triggers for the script. I'm looking for a run-time check of how this instance was executed. Thanks for any tips!
The event object should give you the information you're looking for. You get that objet with simple and installable triggers. Have a look at the documentation: developers.google.com/apps-script/guides/triggers/events
I couldn't find any "reload" method on the documentation. I guess your best bet would be to trigger a reloading of the page by opening it: var preso = SlidesApp.openById('PRESENTATION_ID_GOES_HERE');
Hi Saperis, Great video but I have a question: I want to have a function that capture some data from the user that is using my Add-on in the onOpen() event. I've tried with the installable triggers but it's only working in my spreadsheet, not at an Add-on level (in the rest of user's spreadsheets). What i want is when a user opens their spreadsheet my custom function runs. Do you know what thing I'm doing wrong? Thanks!
If you are referring to a add-on the user installed I suggest you have a look at the official add-on documentation: developers.google.com/apps-script/add-ons/overview As I've never created an add-on myself I have no information that could be useful to your situation.
I don't get where to place the code for the installable trigger "programmatic managing of triggers" and whether or not I have to use the pop up trigger tool window. thanks
@@saperis thanks for the link; but I don't get where to place this code, I mean, how google app knows when and how to run it. Is it enough to place it in a trigger/trigger.gs file? should I place this in onOpen function?
You add these triggers to your files. If you want to you can add a separate file or use the file you already have. The type of installable triggers are all mentioned in that documentation I shared with you. You especially have to check the section about managing the manual triggers as it contains the information you are requesting here: developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually
Thanks for your video. I am new to script writing. I created a data sheet taking API data by the minute from several pages, but suddenly everything stops working / updating. Is it due to: "Triggers total runtime 90 min / day"? I am watching arbitrage data and would like it updated by the minute for about 20-30 data points. Any recs on managing this? I'm way outside of my wheelhouse, but enjoying this dive into it.
Hi Peter. I would, just like you, suppose you're reaching the quota limits and due to that the script stops executing after some iterations. There isn't really a workaround for that.
@@saperis Thanks so much for replying to my comment! I worried I failed in the script writing. I am really excited to learn these new things. Thank you! and I look forward to your future content.
That's currently not possible with the installable triggers. You could try creating the trigger programmatically to achieve this: developers.google.com/apps-script/guides/triggers/installable?hl=en#managing_triggers_programmatically
Oh wow, I was completely unaware there are so many types of triggers! Thanks Chanel
Always happy to share my knowledge with our viewers. Thank you for the feedback. 😀
I'm learning how to app script from you, I'm a total beginner and your videos are essential watching for me. Thanks so much.
Great to hear that! I know learning a new skill can be a bit difficult at times, but please keep at it. 👍
Chanel, you scare me a little. But your videos are top of the line! Love them 😍
Sorry I look scary but happy you like the videos. 😉
@@saperis My French got in the way, that came out wrong. You look very smart and I'd hate to be in argument with you. Ok, that's more like it. Cheers from Vancouver :)
Ah, pas de problème! 😀
@@saperis video suggestion: working with strings in google scripts! For me, beginning, its really hit and miss if any given function will work. Just getting the id of sheet turns out a hassle (.match not recognized, etc). Cheers
Loved your hair. And yes, I did learn plenty of useful things I didn't know before. Thank you!
Happy you liked the video and the hair. 😀
Thanks - that was very helpful. I've written code for data analysis for several years, but I'm just starting to use scripts and macro's in Google, and getting my triggers to work as expected has been a problem. This answers some of my questions
Happy to hear that 😀
interesting but the low pulsating hum that sounds like a ventilator is really distracting. I recommend you apply a low-pass filter on your sound file.
Well, this video was made ages ago. Since then my video setup has changed. But thanks for the tip.
@@saperis well the content is still relevant 🙂 And I meant a high-pass filter not a low-pass filter, sorry. The hum is loud on good headphones but would probably not be heard on lower quality equipment. Have a nice day !
Impressively slick video, thanks!
Glad you enjoyed it! 😀
I like your excellent explanation.👍❤
i have a question
I want to send emails on a specific date.
I want to send an email This should work after 3 days from the date of customer registration and continuously.
For example " 2022.07.13 after registration then message should be sent on 16.07.2022. Date may change because customers can register anytime")
How to send email with conditions.🤔
Please tell me how to do it
Thank you very much 😊
I've never done anything like this. In theory, you would need to write a script that instantiates it's one time-based trigger. None of the default time-based triggers runs every three days. So again, you would have to write something yourself.
@@saperis Like a reminder.i found it.thank you 😊
@@Thilina_madushanyou had to go in and create new Triggers through the Google app scripts code?
Informative, thanks!
Glad it was helpful!
Thank you! This helped a lot.
Glad it helped! 😀
Nice video--Just FYI that the link you mention for learning more deeply about GoogleSheet automation doesn't seem to be working, and the link from your comments shows a course "Productivity for Creatives with Google Workspace" (same on domestika), but not one for automation.
Yes, the Apps Script course is no longer available. The only course currently available is the one on Domestika.
Very helpful. Thank you.
You're welcome! 😀
@@saperis I am just beginning to look into Google’s new AppSheet. Can’t wait to see how I can use it to automate some of my workflow. Have you begun using it yet?
I checked it out a couple of months ago but I don't actually use it. I might start creating some videos on it in the future but I'm not sure about it yet.
Thank you so much!
You're welcome! 😀
Great video, I want to set up a trigger to be run every 72 hours. Let’s say I create the trigger, now what should I do when setting up the trigger using that window??
Nothing. It will run just as you set it. That's why it's time-based. It runs based on the time you have defined, without you having to do anything else.
@@saperis thanks for your response. The issue here is google doesn’t allow for every 3rd day or 72 hours option so I was just wondering how would I go about it’s any help would be appreciated.
In that case check out how you can add a trigger through a script: developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers
hi sorry but when i go to the clock it didn't give me the botton ADD trigger, it give me file.json and file.gs, what i have to do????
Thank you alot for this useful video, making my understanding structured. 👍
Just do you know how to get a summary of all Google Apps Script triggers in my account? I start to use triggers little by little with lots of test projects 😜 I hope one day I can find out all my deployed triggers in order to remove useless ones.
Check the Apps Script home screen and then the "My Triggers" section.
Try this direct link: script.google.com/u/0/home/triggers
@@saperis Ah great, I even didn't know there is a home page for Apps Script. So sweet of you, thank you Saperis again! Have a nice day 😃
i want protect or hide my script code of google sheet from another person. How it possiable?
Hi Saperis
Can you guide how can you stop trigger execution after 5 times?
You would probably have to build a script yourself that tracks how many times a script ran. There is no default way of running your script for X times.
I create trigger with newTrigger method. How to setup failure notifications without choose UI? Thanks
Good questions. I've never looked into that so I have no suggestion.
That was great. Thanks. I’m making a Glide app. Would your Google Apps Script work with the Glide app platform? I was considering it as an option for a email response to users, authorizations, and anything else useful.
I've never tried out Glide apps. But it looks like other people are using it together with Google Apps Script: dev.to/harishkotra/apps-script-rest-apis-google-sheets-glide-apps-42h6
I've never gotten an email about a trigger script error.
That's great. No email = no error. 😀
Love your explanations! I am trying to use the openById method (in a bound script) to open one of my other spreadsheets, but keep getting a permission error. Discussion boards say that the way around the problem is to use a "trigger". What does this mean? (Note: I'm brand new to Google Apps Script -- this is my 2nd day!)
I'm guessing you don't have access to the file you are trying to open. I believe you would need at least viewing rights on that document.
Check this video to find out what triggers are: czcams.com/video/KC7pBjD3GGw/video.html
So, here is the thing. I wish to place inside a cell the formula =now() and see the clock updating its seconds in real time, using the format "hh:mm:ss" from a fixed cell. It can be done in excel. I wonder if it can be done in Google Sheets, I thought that if I could trigger the show event every second I could have a clock effect, but such option is not at sight (seconds). Any suggestion?
There's a NOW() formula in Sheets too: support.google.com/docs/answer/3092981?hl=en#:~:text=NOW%20provides%20the%20current%20date,when%20it%20was%20first%20entered.
This formula updates every time the spreadsheet is edited.
In theory, you could set the the NOW() formula with an Apps Script function you write and use a time-based trigger to update it. But I think you might quickly hit a quota limit if you run the script every second.
See this video to find out how to set a formula with Apps Script: czcams.com/video/aYG55AlprHE/video.html
HI, I have a question, I create a code linking form, spreadsheet, and calendar using onFormSubmit() which it will map the leave request date on the calendar once the form is submitted. The code works just fine. But when I add some IF ELSE function for the drop-down in the form, it doesn't work. Which trigger I should use for this?
Do you expect the script to run because you manually add an IF/ELSE Sheets function?
Or do you mean that after adding the IF/ELSE function to your Google Sheet, the onFormSubmit() trigger doesn't work anymore whenever someone submits the form?
Can we triggered google app script to send emails when new row added to spreadsheet.
No, because the onEdit trigger is a simple trigger. Such simple triggers can't send out emails.
Find out more in the official documentation: developers.google.com/apps-script/guides/triggers
Interesting Video! I have a time trigger (weekly fires) that activate a function that contains an installable trigger. In some google sheets the weekly trigger does not fire, in some it does. What could be the reason that it not fires?
No idea what might be causing this. Many people have issues with time-based triggers not working as expected.
Found this thread on Stackoverflow that might be interesting: stackoverflow.com/questions/59175431/time-driven-triggers-not-working-properly
@@saperis Thanks for fast respons! In between I solved the problem! The files, where the triggers did not work, are corrupt. After replacing these files everything works fine. Many times is this the solution for time driven triggers...
Let's say I build a google sheet add-on, can I use installable trigger to run in users who install my add-on?
According to the Apps Script documentation you can. Read the details here: developers.google.com/apps-script/add-ons/concepts/workspace-triggers#installable_triggers
But I have never built an add-on myself yet so I can't speak from experience about this topic.
Great video. However I still don't understand how to make an installable trigger with event source "From spreadsheet" when it is not a bound script. Any idea?
Check this section of the Google Apps Script documentation for an answer: developers.google.com/apps-script/guides/triggers/installable#application_triggers
@@saperis Thank you for your answer. Honestly, I think I've read all of the Google documentation so far.
When I create a new project (not bounded) then there is no option "From spreadsheet" in "Select event source" select. When I changed the manifest file, this option appeared however I had to specified what function I want to run on "homepageTrigger". And of course I get application icon in right side panel (what I don't want to have). I really have no idea what can I do and reading Google documentation for last five days hasn't helped me at all.
Hi! I have several onEdit triggers but when I duplicate the sheet, all of it are gone on Triggers and have to manually add all of it again on the duplicate sheet. Is there a way that those onEdit triggers won't be removed once I create a copy of the original sheet? Thank you.
Maybe adding the onEdit trigger in the script instead of through the user interface, could help solve this issue: developers.google.com/apps-script/guides/triggers#onedite
@@saperis Thank you. 😀
Thanks for the video! Can you help me with code to let my script determine whether the trigger source from a particular run event was from CLOCK or from manual (e.g. pressing Debug or Run)? I thought it was getEventTrigger but it seems to return all the scheduled triggers for the script. I'm looking for a run-time check of how this instance was executed. Thanks for any tips!
The event object should give you the information you're looking for. You get that objet with simple and installable triggers.
Have a look at the documentation: developers.google.com/apps-script/guides/triggers/events
Is there any way to reload the page via script.
In case I'm using Google slide
I couldn't find any "reload" method on the documentation.
I guess your best bet would be to trigger a reloading of the page by opening it:
var preso = SlidesApp.openById('PRESENTATION_ID_GOES_HERE');
Hi Saperis,
Great video but I have a question: I want to have a function that capture some data from the user that is using my Add-on in the onOpen() event. I've tried with the installable triggers but it's only working in my spreadsheet, not at an Add-on level (in the rest of user's spreadsheets).
What i want is when a user opens their spreadsheet my custom function runs.
Do you know what thing I'm doing wrong?
Thanks!
If you are referring to a add-on the user installed I suggest you have a look at the official add-on documentation: developers.google.com/apps-script/add-ons/overview
As I've never created an add-on myself I have no information that could be useful to your situation.
I don't get where to place the code for the installable trigger "programmatic managing of triggers" and whether or not I have to use the pop up trigger tool window. thanks
These triggers you have to write in the script editor. Here is the information about it: developers.google.com/apps-script/guides/triggers/installable
@@saperis thanks for the link; but I don't get where to place this code, I mean, how google app knows when and how to run it. Is it enough to place it in a trigger/trigger.gs file? should I place this in onOpen function?
You add these triggers to your files. If you want to you can add a separate file or use the file you already have.
The type of installable triggers are all mentioned in that documentation I shared with you. You especially have to check the section about managing the manual triggers as it contains the information you are requesting here: developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually
Thanks for your video. I am new to script writing. I created a data sheet taking API data by the minute from several pages, but suddenly everything stops working / updating. Is it due to: "Triggers total runtime 90 min / day"? I am watching arbitrage data and would like it updated by the minute for about 20-30 data points. Any recs on managing this? I'm way outside of my wheelhouse, but enjoying this dive into it.
Hi Peter. I would, just like you, suppose you're reaching the quota limits and due to that the script stops executing after some iterations. There isn't really a workaround for that.
@@saperis Thanks so much for replying to my comment! I worried I failed in the script writing. I am really excited to learn these new things. Thank you! and I look forward to your future content.
Check out my online course on Google Apps Script: courses.saperis.io/
How can run a macro automatically after every 5 minutes
You would have to set up a time-based trigger. Check this video: czcams.com/video/KC7pBjD3GGw/video.html
Hello
How to add Trigger for Monday to Friday, 9 to 3 PM, Every Minute...
Alll Three Together...
That's currently not possible with the installable triggers.
You could try creating the trigger programmatically to achieve this: developers.google.com/apps-script/guides/triggers/installable?hl=en#managing_triggers_programmatically
@@saperis How to add Trigger for weekday rush hour, 7 to 9 AM and 5 to 7 PM, Every 5 Minutes, to log duration from home to office?
i think i'm triggered by triggers
That comment triggers my method smileOnMyFace(). 😉