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.

Just a note that this is one way to implement it.

Sample Apps Script code for Exponential Backoff

Copy or move file into folder Tool

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.

Quickly copy or move existing files into folders via a Google Sheet

Append Drive folder permissions Tool

The following Google Apps Script tool allows you to quickly append new permissions to existing folders within Google/Shared drive. This is quite a niche tool so it is anticipated you would already have a Google Sheet of folder IDs/URLs, though there is the option to select an existing Drive folder of folders and automatically bring them into this tool.

This tool is a modified version of the Append Drive files permissions 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.

Quickly append new folder permissions via a Google Sheet

Append Drive file permissions Tool

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.

Quickly append new file permissions via a Google Sheet

Check comma separated email address is valid

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.

Regular expression to check the format of an email address

Change Drive ownership without email notification

The following Google Apps Script is designed to change ownership of a Google Drive item without sending the default email notification to the new owner. Please note this will only work within your organisation (i.e. on the same domain) and not between personal Google account (where consent is required by Google).

Snippet of code for changing Drive ownership

The Gift of Script YouTube channel

There is now a new dedicated YouTube channel for the Gift of Script content.

All previous videos have been collated into Playlists.

Subscribe @TheGiftofScript.

Correct date formats in a Google Sheet

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.

Screenshot showing difference between American/British date formats

Google Add-on: Send Recurring Emails

What it does

Easily send recurring emails at a frequency of your choosing e.g. every 7 days, every 27 days, every 1 month, etc.

Write your email(s) once, select your start date, frequency, recipient(s), then kick back and relax. The tool will automatically check every day at a time that you specify to see if any emails need sending.

Example uses:

• Newsletter submissions.
• Monthly reports.
• Team meetings.
• Attendance checks.

Demo video

Find out more

Send Recurring Emails Add-on webpage.

Google Add-on: Bulk Calendar Events

What it does

Bulk create Google Calendar™ events with optional video conferencing (Google Meet™). Add location, guests, change the event visibility, colour and much more.

Example uses:

• Meetings
• Workshops
• Events
• 1:1s

Demo video

Find out more

Bulk Calendar Events Add-on webpage.

onFormSubmit Trigger simultaneous executions

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.

Probation Tracker Tool

The following Google Apps Script tool was developed to allow you to easily be reminded of when upcoming probation review meetings are due. Enter the details into the Google Sheet, including upcoming review dates, and the tool will automatically email the Line Manager both 14 days before and on the day of the review date.

Manage probation review dates in a spreadsheet and be sent reminder emails

Totally Unscripted: Level Up Your Productivity: Unboxing the Gift of Apps Script

Are you looking for ways to supercharge your productivity with Google Apps Script? Then this episode of Totally Unscripted is for you!

Join us as we explore how Google Apps Script is being used to make a real impact in productivity. Our guest speaker, Phil Bainbridge, is an experienced IT specialist at the University of York and author of 'The Gift of Script.' He will share his insights on how Google Apps Script can be used to automate tasks, create custom solutions, and enhance your workflow. As part of this episode we will discover how Phil and other members of York’s Digital Inclusion, Skills & Creativity team support digital literacy development in Google Workspace and Apps Script as well as the solutions they have created to improve productivity.

20 March 2024 at 7pm.

Replace text in a Google Doc with an image

The following Google Apps Script is designed to search the body of a Google Doc for a specific string/pattern (i.e. a keyword) and insert an image in place of it, optionally making it a clickable hyperlink too.

Instructions

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:

1. searchText - this is the unique string/pattern in the Doc that you want to replace with an image e.g. "<<keyword>>"
   
   
2. imageURL - this is the direct link to the image in Google Drive that you wish to use in the Doc.
   
   
3. size - a numerical value representing the number of pixels for the image's width/height.
   
   
4. hyperlinkURL - if you want the image to be clickable then provide a link for it.
   

Google Add-on: Auto Close Form

What it does

The Add-on allows you to have a Google Form automatically close when a set number of responses has been reached or on a specific date. You define exactly how many responses or the date, per form, and then leave it to run in the background. Once a Form has closed you will receive an automated email informing you.

Example uses:

• Sign ups to an event/workshop/conference.
• A limited time offer.
• Submission deadlines.

Find out more

Auto Close Form Add-on webpage - includes instructions and demo videos.

Drive API - get file information

The following Google Apps Script is a snippet of code where I was learning how to use the Drive API to query a folder of files, to extract some specific file information. It is designed to work on both My/Shared drives.

Information to Extract

• Name;
• Type (e.g. Doc, Sheet);
• ID;
• Creation date;
• Last modified date;
• Clickable link;
• Owners
  

Enable Drive API

Please follow the steps to enable the 'Drive API' Service if you are not working from a copy of the file in this post.

Google Add-on: Form Calendar Invites

What it does

The Add-on allows you to link a Google Form™ with Google Calendar™ events so that when people complete the Form and select a particular title/date/time from it, they are sent an automated email invite to one of the respective Calendar events.

This allows you to have Calendar events with all guests attached to it such as for conferences, workshops, drop-ins, training sessions, etc. Therefore any changes to an event (description/conferencing, etc) will be reflected for all guests - just like any other event.

Sessions listed in the Form can be selected

Find out more

Form Calendar Invites Add-on webpage - includes instructions and demo videos.