Exponential Backoff, a process by which when something fails you try again a set number of times whilst increasing the delay between each attempt.
Sample Apps Script code for Exponential Backoff
The following Google Apps Script is designed to explore Exponential Backoff – a process by which when something fails you try again a set number of times whilst increasing the delay between each attempt, up to a certain point.
I needed this for a tool I built which adds Guests to a Calendar event from a Google Form submission. Whilst I was using ScriptLock to prevent simultaneous submissions, the code ran so fast that it would infrequently trip the Calendar API with the following error message “API call to calendar.events.patch failed with error: Rate Limit Exceeded”.
By infrequently I mean a reported issue only once in 3,500 submissions over the course of 12 months. Enough however to take the opportunity to learn about Exponential Backoff and to squash that single instance.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Quickly copy or move existing files into folders within Google drive via a Google Sheet.
Quickly copy or move existing files into folders via a Google Sheet
The following Google Apps Script tool allows you to quickly copy or move existing files into existing folders within Google/Shared drive. This is quite a niche tool so it is anticipated you would already have a Google Sheet of file IDs/URLs, though there is the option to select an existing Drive folder of files and automatically bring them into this tool.
You can continue to append further rows to the Google Sheet as existing ones will be skipped once the ‘Status’ column has automatically been marked as ‘Completed’. Alternatively you can clear the Sheet each time for a fresh start.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Quickly append new permissions to existing files within Google Drive. Insert email addresses using a comma-space format with optional notifications.
Quickly append new file permissions via a Google Sheet
The following Google Apps Script tool allows you to quickly append new permissions to existing files within Google/Shared drive. This is quite a niche tool so it is anticipated you would already have a Google Sheet of file IDs/URLs, though there is the option to select an existing Drive folder of files and automatically bring them into this tool.
By inserting email addresses using a comma-and-space format you can optionally select to send a notification email to the new user(s)
You can continue to append further rows to the Google Sheet as existing ones will be skipped once the ‘Status’ column has automatically been marked as ‘Completed’. Alternatively you can clear the Sheet each time for a fresh start.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Loop through a Google Sheet cell of comma-space separated email addresses and check their format is valid, otherwise display a user popup.
The following Google Apps Script is designed to loop through a Google Sheet cell of email addresses that have been separated by a comma and space. It then uses a regular expression (regex) to confirm the email address meets the correct formatting criteria.
This code was developed as a way of implementing additional checks when asking users to be precise in how exactly they enter multiple email addresses. So if they were to forget the space for instance it could alert them, before the rest of the code risked failing as whatever task it was designed to do.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Search through Google Sheet data and correct the format of any date values to your choosing.
The following Google Apps Script is just one way you could search through data within a Google Sheet, pinpoint all date-values (no matter where they are), check their format (e.g. dd/mm/yyyy) and update it to a format of your choosing if necessary.
This function has been useful when American/British date formats have managed to make their way into a Google Sheet.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Caution when using onFormSubmit triggers to avoid mass simultaneous executions when un/linking a Response Sheet.
The following post is a caution around onFormSubmit triggers after I recently discovered a way that somebody had inadvertently managed to execute my code simultaneously over 253 times. Now unless a Google Form is submitted by 253 individuals all at the same time … then this is baffling.
When should an onFormSubmit trigger run?
At the point when a Google Form has been completed and the ‘Submit’ button pressed.
How else can the trigger be activated?
Well … it turns out when you unlink and then link back a Google Response Sheet containing the onFormSubmit trigger, it will activate it for every single Form response collected up to that point in time …
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Probation tracker tool to easily manage upcoming review meetings. Have automated reminder emails sent to Line Managers.
Manage probation review dates in a spreadsheet and be sent reminder emails
Key Functionality
The daily check is performed between 7am – 8am every day and for any emails that are sent a Note will be attached to the cell with its timestamp for record purposes and it will be coloured in light-green.
You can continue to append further rows for new staff starters after the initial Sheet setup or you may wish to delete/move rows for those that have completed their probation period.
You can adjust dates, emails addresses, etc at any point in time.
If you enter something into the ‘Probation Passed’ column then that given row will be skipped in future checks, to help save time if you continue to add to this Sheet.
You do not have to enter a review date for every single column, thus allowing flexibility between differing roles, just leave it blank.
There is a Log sheet to help capture any errors and it will attempt to email the account that creates the daily check (trigger) to alert them.
Editor: A reminder that Phil Bainbridge will be joining us on Totally Unscripted at the slightly earlier time for non-US viewers of 7pmUTC 20 March, 2024. This ‘Google Sheets probation tracker and reminder tool’ is a great example of the types of solutions Phil creates as part of his role at the University of York. Watch live at 1200 PT / 1500 ET / 1900 GMT
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Search the body of a Google Doc for a specific string/pattern and insert an image in place of it.
In this example the code is designed to sit behind the Google Doc so it is bound to it. There are 4 pieces of information to complete in order to setup the script:
searchText – this is the unique string/pattern in the Doc that you want to replace with an image e.g. “<<keyword>>“
imageURL – this is the direct link to the image in Google Drive that you wish to use in the Doc.
size – a numerical value representing the number of pixels for the image’s width/height.
hyperlinkURL – if you want the image to be clickable then provide a link for it.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Use the Apps Script Lock Service to control Form submissions and prevent data loss
Lock Service code snippet
The following Google Apps Script is a one example of how the Lock Service can be used to prevent concurrent running of code. Here we have a Google Form that can be submitted by users at any point, the code then takes some of those details and appends them to another Google Sheet row. In normal circumstances this will happen relatively quickly and without clashes, but what if multiple people submit the Form at the same time!?
[Editor note: An alternative approach to tryLock() is waitLock(). The only different with a waitLock() is it will throw an exception after the set number of milliseconds. An example of waitLock() with onFormSubmit is included in the reference documentation]
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Check a Google Sheet once per day and if the date is in the past Autofill another row of formulas.
Autofill Google Sheet formulas each day
The following Google Apps Script is designed to check a Google Sheet once per day and if the date is in the past it Autofills another row with the existing formulas used across the columns. This post is a variation of the Autofill Google Sheet Formula one.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
