AppsScriptPulse

Avoiding Google Apps Script onFormSubmit simultaneous executions when unlinking/linking Google Sheet responses

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 …

Source: The Gift of Script: onFormSubmit Trigger simultaneous executions

A Google Sheets probation tracker and reminder tool

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

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

Source: The Gift of Script: Probation Tracker Tool

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

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

Manage Google Form onFormSubmit script executions with Script Lock

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

Autofill Google Sheet formula each day with Google Apps Script

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

Bulk create Drive folders with subfolders with Google Apps Script

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

Bulk duplicate Google Drive files using Google Apps Script

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

Auto close Google Form after X responses with Google Apps Script

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

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

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

Handling Google Forms checkbox responses with Google Apps Script

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