Collate and organise a large Sheet of data

The following Google Apps Script is designed to organise a large Google Sheet of data into a specific category and then populate a Google Sheet template file with it. In this example our main data consists of these columns:

• URLs
• Last Update Time
• Creator Email
• Owner Email
• Incompatibility Reason

We want to organise the jumbled data so that we gather together all the rows of the same Owner Email address and pop these into a separate Google Sheet, which is then shared ('edit' access). Repeating this process for the next unique Owner Email address.

Collate and organise a Sheet of data by Owner Email

Create a dynamic Menu from content in a Google Sheet

The following Google Apps Script is designed to dynamically create a Menu in a Google Sheet, based specifically on the data within it. So rather than the typical process of creating a static Menu where you prescribe exactly which items go into it and the Functions to run, this script will generate the items each time the Sheet is opened and perform one universal Function: translating 'Hello' into a couple of different languages in this example.

This means you can add/remove items from your Sheet and the Menu will adjust accordingly (the next time you re-open it), without any need to go into the Apps Script Editor.

Screenshot of Sheet popup showing translated text from data within the Sheet.

Convert Sheet data into a JavaScript Object for easier handling

The following Google Apps Script offers a way to organise a Google Sheet of data so that your Header row names are paired with your data values. So say you have the Headers 'Forename' and 'Surname' this script will create a JavaScript Object with a name:value pair that makes it significantly easier to extract the data you want.

This is as opposed to the standard approach where you typically work with 2D arrays that require strict column positioning so that you know the exact position of a value within it. Should another column then be inserted or moved in your Google Sheet, the likelihood is that a chunk of your array positions would need manually updating.

Screenshot of Sheet data with Header rows

Replace text in a Google Sheet with an image

The following Google Apps Script is designed to replace text in a Google Sheet. You provide the text you want to search for and it will replace all instances of that text (specifically the contents of that cell) with your image. You can optionally choose to adjust the size of the row/column and centre your image. You will need to ensure that whatever image you use, it is accessible online via a URL.

Note this is one of about four different ways of inserting an image into a Google Sheet.

I have a similar blog post available for replacing text in a Google Doc with an image.

Screenshot of Google logo that has replaced text in a Google Sheet

Assign Google Workspace licences for individuals without the Admin Console

The following Google Apps Script is designed to automate the assigning of a Google Workspace (e.g. Education Plus) licence in the Google Admin Console, for individual accounts/users by providing them with a quick Google Form to submit.

This saves having to manually go into the Google Admin Console and assign a licence. All the user has to do is tick the box on the Google Form, they will receive an automated confirmation email and their new licence will be applied within 24 hours.

You can refer to the Google product and SKU IDs webpage if you need to assign a different licence type. There is some basic checking to see if an account/user is an 'Administrator' and it will therefore prevent them from getting a licence.

Screenshot of the Google Form title used for submission

Check date in future and a Wednesday

The following Google Apps Script is designed to check that a date submitted via a Google Form is both in the future and falls on a Wednesday. This was developed as part of a newsletter submission process to help validate information and inform a user when they had not followed the instructions. It has been developed for UK dates and may need adjusting for your own time zone.

Google Form question asking for a date to be entered.

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