Find and replace in a Google Sheet - whole data search

The following Google Apps Script is designed to search Google Sheet data for a list of terms that need correcting and the cell colour changing on ones that have been modified. So here is a list of the 'incorrect' words to find and what it should be replaced by, as an example:

• xray change to X-Ray
• 1st change to First
• 2nd change to Second
  

This blog post is a slight adaptation of the one here for Find and replace in a single column. This time we are expanding our scope to the whole of the Google Sheet data where our list of terms may be present in multiple columns.

Use the 'Welcome' sheet for input to run the tool

Bulk create Google Drive folders with a sub-folder

The following Google Apps Script is designed to bulk create Google Drive folders along with a sub-folder from data given in a Google Sheet (eg to name the folders and add permissions).

This tool and blog post is largely a continuation of this one for bulk creating Google Drive folders. The main difference being that there is an extra column to provide the name of your sub-folder. Note that the tool will try to create a sub-folder regardless of what is entered - as I was just aiming to create a version for the many requests I got from the previous blog post from people who wanted a sub-folder.

Bulk create Google Drive folders from a Sheet of data

Find and replace in a Google Sheet

The following Google Apps Script is designed to search Google Sheet data for a list of terms that need correcting and the cell colour changing on ones that have been modified. So here is a list of the 'incorrect' words to find and what it should be replaced by, as an example:

• xray change to X-Ray
• 1st change to First
• 2nd change to Second

The discrepancies may have occured from the longevity of the data and/or individual users having their own terminology. So we want to take a list of these words and in this example target a specific column of raw data where they may be present.

A list of words to find and their replacement equivalent.

Extract a list of Google Group members into a Sheet

The following Google Apps Script is designed to extract a list of the current members of a Google Group (email address and role) into a Google Sheet. You simply enter the email address of the Google Group that you belong to and then run the Function.

The script starts by using the 'GroupsApp' to access the Group and then get a list of all the users:

var group = GroupsApp.getGroupByEmail('YOUR GROUP EMAIL ADDRESS HERE');
var members = group.getUsers();

Once we have an array (list) of all the members we need to cycle through each one and get their email address and role, as well as tidying up some of the formatting (eg setting everything to lowercase). Then we can push this detail into a new array (list) that we will later use to paste into our Sheet:

for (var i=0; i<membersLength; i++) {
      
   // get Email Address
   var memberEmailAddress = members[i].getEmail();
      
   // get Role and set as lowercase
   var memberRole = group.getRole(memberEmailAddress).toString().toLowerCase();
      
   // push details into array for later pasting into Google Sheet
   memberDetails.push([memberEmailAddress, memberRole]);
      
}

Submit a Google Form to a Slack channel via a Webhook

The following Google Apps Script is designed to take a Google Form submission and send the data to a specific Slack channel via a Webhook.

Screenshot of message when posted in Slack channel

Create a Google Calendar Event via the Calendar API

The following Google Apps Script is designed to create a single Google Calendar event via the Calendar API. I've been recently developing a tool to bulk create Calendar events from a Google Sheet but before that I needed to learn the necessary formatting/structure to be able to send details to the Calendar API.

This is a simple Google Apps Script file that may look lengthy but in actual fact is just each possible item of an event that a user may wish to modify. Some of the items have default values which means you could remove them from the script and the Calendar API would use these defaults instead. My hope is that laying out the format like this allows you to then manipulate as you require and learn as I did.

Structure of the event:

• ID of the Calendar to create the events in (typically an email address).
• Summary - title of the event.
• Description.
• Location.
• Start/End Date/Time.
• Google Meet conferencing.
• Attendees - guests invited to the event.
• Send updates - email invitations to attendees.
• Guests can invite others.
• Guests can modify the event.
• Guests can see other guests.
• Show Me As - your availability during the event (eg busy).
• Event visibility - eg private or public.

Bulk create Google Calendar events with optional Meet or Zoom - overview

The following Google Apps Script tool is designed to bulk create Google Calendar events with optional video conferencing (Google Meet or Zoom). It is an enhanced version of this blog post for creating events with optional Google Meet. As it now includes Zoom there are a number of extra steps such as additionally setting up a Zoom Marketplace App.

The tool is run entirely from a Google Sheet and the details of each event is added per row, from which the tool plugs in to Google Calendar and bulk creates the events for you. The outcome is the standard Calendar event item that can then be edited just like any other.

Google Sheet columns allow for event details to be added.

Bulk create Google Calendar events with optional Meet or Zoom - the code

Following on from the overview blog post here, I have included the code itself from which the tool is built: