Get Google Form file upload links into a Google Doc

The following Google Apps Script aims to provide one way to solve the difficulty of passing Google Form data into a Google Doc, after the Form has been submitted, where you have a file upload question and you want a nice accessible link putting in the Google Doc rather than the long (unclickable) URL.

I have a specific blog post which focusses more upon the process of creating Google Docs from a Form submission.

Google Doc with Form data and accessible file links

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

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

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

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

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.
   

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.

Google Add-on: Convert Drive Files update December 2023

 

What it does

Bulk convert Google Drive™ files quickly and easily with this Add-on. Convert between Google™, Microsoft Office™, OpenDocument, PDF, etc. Point the Add-on at a Google Drive™ folder, select the file types you want to convert, select your destination folder and whether you want the original file to be deleted, then hit go! Saves having to go into each file individually, selecting the file type to download and then selecting the save destination.

Update

• Remove original file extension option
• Based on feedback, the default behaviour for automatically removing any existing file extension has been stopped. There is now a simple Yes/No option allowing you to choose for yourself exactly what you want to happen. The main reason for this change was due to file names containing 'full stops' that would otherwise be lost as the tool treat them as part of the extension.

 

Find out more

Convert Drive Files Add-on webpage - includes instructions and demo videos.