AppsScriptPulse

Author: Phil Bainbridge

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
BeginnerDocsDriveSnippet

Replace text in a Google Doc with an image from Google Drive with Google Apps Script

Tags:
| | 0 Comments |

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:

  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.

Source: The Gift of Script: Replace text in a Google Doc with an image

BeginnerFormsSnippet

Manage Google Form onFormSubmit script executions with Script Lock

Tags:
| |

Use the Apps Script Lock Service to control Form submissions and prevent data loss

Lock Service code snippet
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]

Source: The Gift of Script: Control Form submissions with Script Lock

BeginnerSheetsSolutions

Autofill Google Sheet formula each day with Google Apps Script

Tags:
| | 0 Comments |

Check a Google Sheet once per day and if the date is in the past Autofill another row of formulas.

Example screenshot of a Google Sheet with columns and dates that are used to perform calculations.
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.

Source: The Gift of Script: Autofill Google Sheet Formula each day

BigQueryDriveSheetsSolutions

Bulk create Drive folders with subfolders with Google Apps Script

Tags:
| | 0 Comments |

Bulk create Google Drive folders with multiple subfolders. Control the naming of the folders and how many you want.

Use this tool to bulk create folders with subfolders
Use this tool to bulk create folders with subfolders

The following Google Apps Script is designed to bulk create Google Drive folders with multiple subfolders. You control the naming convention of each folder and exactly how many you want.

Features include:

  1. Runtime control – currently set to 5 minutes 30 seconds. Will ignore rows that have a ‘Folder Link’ so you can continue from where you left off and append further folders should you require.
  2. More subfolders – the Google Sheet has columns for 10 subfolders but you can technically add more (columns) and the code will account for this automatically.
  3. Toast popups to inform you of the progress as folders are created.
  4. ‘Log’ sheet and popup error messages if something goes wrong.
  5. Concatenation – create those useful descriptive folder/file names so items are easier to search for in the future. Concatenate will let you combine values that may exist in different columns in another spreadsheet for example, or take those folder names and prepend/append words around them.

Source: The Gift of Script: Bulk create Drive folders with subfolders

BeginnerDriveSheetsSolutions

Bulk duplicate Google Drive files using Google Apps Script

Tags:
| | 0 Comments |

The following Google Apps Script tool is designed to take a single Google Drive file e.g. a Doc / Sheet / Slide and make duplicates/copies of it with unique file names. The tool will also create a clickable link within the Google Sheet to each new file copy.

Screenshot of the tool for duplicating Drive files
Screenshot of the tool for duplicating Drive files

Source: The Gift of Script: Bulk duplicate Google Drive files

BeginnerFormsSnippet

Auto close Google Form after X responses with Google Apps Script

Tags:
| | 0 Comments |

The following Google Apps Script is designed to automatically close a Google Form once it has reached the number of responses you specify.

Use Apps Script code to automatically close a Google Form
Use Apps Script code to automatically close a Google Form

Do you ever create a Google Form and then forget to close it? Or maybe you want to limit the number of responses that a form can receive? If so, you can automatically close a Google Form once it has reached the number of responses you specify with this example Google Apps Script.

Source: The Gift of Script: Auto close Google Form after X responses

BeginnerDocsSheetsSolutions

Create a Google Doc for a single row of Google Sheet data with Google Apps Script

Tags:
| | 0 Comments |
Use a row of data in a Google Sheet to pass to a Google Doc via Apps Script
Use a row of data in a Google Sheet to pass to a Google Doc via Apps Script

Create a Google Doc containing the information from a selected row of Sheet data.

The following Google Apps Script is designed to create a Google Doc for the selected row of data in a Google Sheet and to include some of that data within the new Doc. it also creates a link to the new Doc back in the Sheet on the relevant row.

Source: The Gift of Script: Create a Google Doc for a single row of Sheet data

BeginnerFormsSnippet

Handling Google Forms checkbox responses with Google Apps Script

Tags:
| | 0 Comments |

Get Google Form responses for checkbox-type questions and perform further actions depending on their values.

Get all checkbox responses from a Form
Get all checkbox responses from a Form

The following Google Apps Script is an example of one way to get the responses from a Checkbox-type question on a Google Form and how you might go about differentiating them.

This came up for a project I was working on where I need to put a Yes/No value into 3 separate Google Sheet cells based on 3 options in a question. The slight challenge is that all of the responses come out as a single array for this question, containing the strings of the values that have been ticked only.

Source: The Gift of Script: Google Form Checkbox responses

BeginnerSnippet

Read and write multiple Properties with Google Apps Script

Tags:
| | 0 Comments |

Bulk read and write a number of key-value pairs in the User Properties store. Extract to an Object for ease of use elsewhere in your code.

Access User Properties and put the values into an Object
Access User Properties and put the values into an Object

The following Google Apps Script is a few snippets of some larger code where I needed to write (and then later read back) a number of User Properties in one go. Rather than creating multiple single write requests it is more efficient to do this in bulk.

I also needed a way to bulk read/extract these values later so I implemented a JavaScript Object that would allow me to easily call the Property name and get its value in return.

Source: The Gift of Script: Read & write multiple User Properties

BeginnerCalendarSnippet

Delete Google Calendar Events by keyword and date range

Tags:
| | 2 Comments |

Search a date range in Google Calendar and match any events with a given string, then delete those events.

Search for and delete Google Calendar events
Search for and delete Google Calendar events

The following Google Apps Script is designed to search a date range for Google Calendar events containing a given string (something to help target those events) and then delete them.

There are 4 items at the beginning of the script to complete for your requirements:

  1. Start date – format mm/dd/yyyy – forms the date range to look for events within.
  2. End date – format mm/dd/yyyy – forms the date range to look for events within.
  3. Search string – bit of text that is unique to the events you wish to delete to differentiate them from other Calendar events you may not wish to remove. Note: is not case-sensitive and will not pick-up search string inside of another word eg ‘the’ in ‘there’.
  4. Calendar ID – typically your email address for your personal Google Calendar to search for events in.

Source: The Gift of Script: Delete Google Calendar Events by keyword and date range