Search through Google Sheet data and correct the format of any date values to your choosing.
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.
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
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 …
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
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
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
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
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:
searchText – this is the unique string/pattern in the Doc that you want to replace with an image e.g. “<<keyword>>“
imageURLÂ – this is the direct link to the image in Google Drive that you wish to use in the Doc.
size – a numerical value representing the number of pixels for the image’s width/height.
hyperlinkURLÂ – if you want the image to be clickable then provide a link for it.
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
Use the Apps Script Lock Service to control Form submissions and prevent data loss
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]
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
Check a Google Sheet once per day and if the date is in the past Autofill another row of formulas.
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.
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
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
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:
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.
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.
Toast popups to inform you of the progress as folders are created.
‘Log’ sheet and popup error messages if something goes wrong.
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.
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
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
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
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
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.
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
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.
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
