AppsScriptPulse

Get weekday name from a date

Take a date and extract the day of the week from it. By making use of ‘formatDate’ and the pattern ‘EEEE’ within a few lines of code we have a workable week-day name that could be used in an if statement as discussed in the previous post.

Source: The Gift of Script: Get weekday name from a date – alternative

Copy template sheet to Google Sheets and reorder

Template sheet with signatures to copy to other files

Screenshot of template sheet with signatures to copy to other files

Take a template Google Sheet (containing fields for 3 signatures) and copy it into other Google Sheet files within a given Google Drive folder and then reorder those sheets so that it was the first one.

The idea was based around needing users to authorise student marks with their signature from multiple Google Sheet reports produced from a third party system.

Source: The Gift of Script: Copy template sheet to Google Sheets and reorder

Dynamically remove Google Form options

Screenshot of Google Form appointment slots
Google Form appointment slots

This was developed as I wanted to learn about removing Google Form option choices as part of exploring its capabilities for use with appointment slots. I am aware there are a number of Google add-ons that achieve this but they come with potential data protection issues when accessing your Google Account, hence an in-house approach.

The principle is simply that a user would select their desired appointment slot on the Form and then that option would be unavailable for the next user accessing the Form.

Source: The Gift of Script: Dynamically remove Google Form options

Batch get existing Google Drive folder IDs & names

Get the folder IDs of a large number of current Google drive folders along with parts of their name – for capturing in a Google Sheet.

Source: The Gift of Script: Batch get existing folder IDs & names – version 2.0

Fixing dates from a Google Form

For those getting a ‘date’ from a Google Form and finding that it flips the day/month the wrong way around. This small piece of code can be inserted to create a correct date format.

Source: The Gift of Script: Fixing dates from a Google Form

‘While’ loop with a ‘Sleep’ and ‘Toast’

Screenshot of toast notification in Google Sheet
Screenshot of toast notification in Google Sheet

Understanding how to use a simple ‘while’ loop that pops up a ‘toast’ message every 5 seconds by making use of ‘Utilities.sleep’ to pause the script.

This was created as part of some testing for another script designed to bulk move files between Google Drive folders. Without the toast notification it was difficult to determine the progress being made, so adding it in allowed for the name of the file last copied to be displayed to the user. This script removes all of the extra features and shows the basic while loop and toast.

Source: The Gift of Script: While loop with a Sleep and Toast

Clear cell contents on Google Sheet edit

Screenshot of coloured columns to highlight actions
Screenshot of coloured columns to highlight actions

This is designed to clear the adjacent cell to the right of the one that has just been edited – so long as it is within the column we specify.

It was developed as I was testing the available options for a Stock Inventory system where an item that was to be marked as back in stock would automatically clear an email timestamp in the adjacent cell (ready for the whole ordering process to start again).

Source: The Gift of Script: Clear cell content on Google Sheet edit

Create alphabetised folders in Google Drive

Screenshot of Google Drive folder layout

The script is designed to bulk create folders from a Google Sheet and to arrange them into alphabetised folders within Google Drive, by extracting the first letter of a user’s surname.

If a suitable alphabetised folder does not exist in Google Drive, then one is created. The script also adds the created folder to an ‘Everybody’ folder so that there is a single area with all the folders listed in.

Source: The Gift of Script: Create alphabetised folders in Google Drive

Bulk rename files in Google Drive

Screenshot of File renamer text fields

Screenshot of File renamer text fields

This is designed to automate bulk renaming of multiple files within a single Google Drive folder. It has been developed as a Google Sheet with flexibility towards entering your own search pattern and replacement string as you see fit.

Source: The Gift of Script: Bulk rename files in Google Drive

Search Google Calendar date range for specific events

Search through a given date range on Google Calendar, look through the events and then extract their Title & Id.

Source: The Gift of Script: Search Google Calendar date range for specific events

Subscribe to Apps Script Pulse...