Tuesday, 3 August 2021

Check the domain of an email address by regex

The following Google Apps Script is designed to use a regular expression (regex) to confirm if an email address domain matches one we specify eg: @hotmail.co.uk, @gmail.com, @outlook.com

I developed the code so I could screen email addresses to make sure they only came from within the institution - otherwise it would have broken the tool I was developing for creating Zoom meetings. The email addresses in this example are all stored in one cell in a Google Sheet and are separated via a comma and a single space.

Email addresses in a Google Sheet with different domains can be filtered.
Screenshot of email addresses in a Google Sheet cell

Tuesday, 27 July 2021

Search Google Drive for owned files

The following Google Apps Script is designed to search Google Drive for all of the files owned by an individual (as defined by their email address). It will collate this information onto a Google Sheet, including:

  • The filename,
  • The file ID,
  • A direct/clickable link to the file,
  • The type of file eg PDF, Googe Sheet/Doc, etc.

Provide the owner and maximum script runtime for search Drive for files.
Provide the owner and maximum script runtime for search Drive for files.


Tuesday, 20 July 2021

Display HTML modal dialogue popup

The following Google Apps Script is designed to display a dialogue box within a Google Sheet. Rather than using the typical Alert dialogue box however, this one does not suspend the server-side script whilst open. What this means is that the Apps Script code does not pause until the user has interacted with the popup in some way.

This is really useful for some of the systems I have produced because they can be running for several minutes at a time during which a user is unlikely to simply be sat watching them. So if they are off doing something else and the typical Alert dialogue pops up, it's possible the script will timeout and produce a bad error message.

With a HTML modal dialogue popup I can incorporate it into a try/catch and continue to display a user-friendly message whilst programming the code to terminate in a more controlled manner - regardless of how long the user takes to interact with it.

HTML modal dialogue box with a message
HTML modal dialogue box with a message

Tuesday, 13 July 2021

Extract text from multiple Google Docs into a Sheet

The following Google Apps Script is designed to iterate through Google Docs in a given Google Drive folder and extract the paragraphs of text along with a link to each file into a Google Sheet. A new column will automatically be appended for each paragraph.

This tool was designed with the vision that you may wish to centrally collate some comments/feedback written by others in Docs, into one central location so you do not need to open each file in turn.

Provide a Google Folder ID to extract text from Docs
Provide a Google Folder ID to extract text from Docs

Tuesday, 6 July 2021

Get last row when using tickboxes

The following Google Apps Script is a quick piece of code you can insert into your scripts when wanting to get the row number of the last piece of data in a Google Sheet column. The traditional method of 'getLastRow()' was unavailable to me at the time - solely because I was using tickboxes within another column of my Google Sheet.

The purpose of the tickboxes were to allow easy user-selection and applied to the whole column. As an un-ticked tickbox has a value of false this meant the traditional 'getLastRow()' method would get confused and return a very large number. So even though my data would stop on row 6 for instance, the tickboxes continue down the sheet.

The alternative was a Function that could be given a Column, from which it would iterate through the data within it looking for values/blanks and then return the last row number.

Get the last row number from a given column
Get the last row number from a given column

Tuesday, 29 June 2021

Bulk create Sheets from a Google Sheet

The following Google Apps Script is designed to bulk create Sheets from rows of data within a Google Sheet and to include some of that data within the new file in specific cells. It also creates a link to the new Google Sheet back in the original file on the relevant row.

For those wanting to bulk create Google Docs from a Google Sheet please see this blog post.

Bulk create Google Sheets from a Sheet of data by looping through each row.
Screenshot of Google Sheets data

Tuesday, 22 June 2021

Bulk create Shared drives with permissions

The following Google Apps Script is designed to bulk create Google Shared drives all from data in a Google Sheet. It has been packaged into a downloadable tool that you can easily use.

  • Provide the name of the Shared drive on each row in column A.
  • Provide the email address(es) of the Google Account(s) under the relevant Access level column (Manager, Content Manager, Contributor, Commenter, Viewer). Use a comma and a space to separate multiple email addresses, eg: example1@example.com, example2@example.com, example3@example.com.
  • Ensure you include at least one Manager - the script will check for this - so as to prevent creating a Shared drive that you are then unable to access.
  • On the 'Config' sheet provide the column numbers - leave the default values unless you change the structure of the 'Data' sheet and move columns around.
  • The 'Log' sheet should help to troubleshoot any errors you experience, but there will also be a popup message should the script fail at some point.
Bulk create Shared drives from a Google Sheet
Bulk create Shared drives from a Google Sheet

Tuesday, 15 June 2021

Create a Shared drive

The following Google Apps Script is designed to simply create a Shared drive in Google. It does not add permissions at this stage.

 

Enable Drive API Service

Make sure you have followed the instructions here to enable the Drive API Service.


The Code

First we create a random request ID that uniquely identifies your request for the creation of a Shared drive:

 var requestID = Utilities.getUuid();

Next we provide a name for the Shared drive and format it so the Drive API can read it:

var name = {
  name: 'Shared Drive from Apps Script2'
};

Tuesday, 8 June 2021

Get the difference between dates in minutes

The following Google Apps Script is designed to get the difference (in minutes) between 2 dates from a Google Sheet. This was part of a tool used to create events from data in a Google Sheet where I needed to get the duration of the meeting for Zoom. The actual date, hour and minute values are separated in columns as it was easier to control user input in that format, so we will need to piece them together.

Start and End date values in a Google Sheet
Start / End date values in a Google Sheet

Tuesday, 1 June 2021

Set permissions on a Shared drive

The following Google Apps Script is designed to bulk set permissions on a Shared drive. It has been created as a standalone Function that requires the ID of the Shared drive and manually entered email addresses for the relevant permsisions.

This is how I first worked with it to learn what information was required and how to structure the content, before combining it all into a Google Sheet tool which will be blogged about in the near future.


Enable Drive API Service

Make sure you have followed the instructions here to enable the Drive API Service.

 

The Code

Below are some comments about snippets of the code to better understand what is happening.

We need to create a JavaScript Object that contains the relevant Shared drive permissions and email addresses as arrays. There are some blank values in this example to show that not every type of role needs something in it:

var roles = {
    organizer: ["
example1@example.com"], // Manager
    fileOrganizer: ["example2@example.com"], // Content Manager
    writer: ["example3@example.com", "example4@example.com"], // Contributor
    reader: [ ] // Viewer
};

Tuesday, 25 May 2021

Generate a quick unique(ish) value

The following Google Apps Script is designed to generate a quick, random and fairly unique value. I've found it useful for generating some random strings for meeting IDs. I have included a JavaScript 'slice' so that I can generate just an 8-character value for instance (you can adjust as required).

var uniqueValue = Utilities.getUuid();
var shortUniqueValue = uniqueValue.slice(0, 8);

Tuesday, 18 May 2021

Get permissions of a Shared drive

The following Google Apps Script is designed to get permissions of a given Shared drive. It uses the Shared drive ID to then list a users email address and role they have. This page on Google Drive permissions is useful for what we need to put together to make the call.

 

Enable Drive API Service

Make sure you have followed the instructions here to enable the Drive API Service.

 

The Code

We need to include an optional argument that allows for us to work on Shared drives:

var optionalArgs = {
    supportsAllDrives: true
}

Next we can run the request to contact the Drive API and request a list of existing permissions:

var sharedDrivePermissions = Drive.Permissions.list(sharedDriveID, optionalArgs);

Tuesday, 11 May 2021

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.

Input is required in the 'Welcome' sheet to connect the tool with your data.
Use the 'Welcome' sheet for input to run the tool

Tuesday, 4 May 2021

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.

Use data within a Google Sheet to bulk create Folders and add permissions
Bulk create Google Drive folders from a Sheet of data

Tuesday, 27 April 2021

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.

1 column contains the words to find and the other column the words to replace with.
A list of words to find and their replacement equivalent.

Tuesday, 13 April 2021

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]);

      
}

Tuesday, 30 March 2021

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.

Messages can be posted in to a Slack channel via the Google Form
Screenshot of message when posted in Slack channel

Tuesday, 16 March 2021

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.

Tuesday, 2 March 2021

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.
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:

Tuesday, 16 February 2021

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

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

Bulk create Google Calendar events with optional Google Meet

The following Google Apps Script tool is designed to bulk create Google Calendar events with optional video conferencing (ie Google Meet). 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.
Google Sheet columns allow for event details to be added.

Tuesday, 2 February 2021

Create a Zoom meeting via the API

The following blog post is a continuation from the connecting to the Zoom API via OAuth one here (please familiarise yourself with it before proceeding). In addition to this post for getting your Zoom meeting settings via the API.

We have already demonstrated successfully getting an Access Token for authentication from Zoom OAuth and then getting our Zoom meeting settings. Now that we have those items we can go ahead and create a Zoom meeting, as we will do here. You may find this page on creating a meeting via the API useful.

Zoom meeting details in a Google Sheet
Zoom meeting details in a Google Sheet

Tuesday, 26 January 2021

Get your Zoom meeting settings via the API

The following blog post is a continuation from the connecting to the Zoom API via OAuth one here (please familiarise yourself with it before proceeding).

Now that we are able to successfully get an Access Token after authenticating our account we can use it to call various Zoom APIs. Here we will access user settings in your Zoom account, specifically the meeting settings, and log the results in a Google Sheet. This provides both a way of confirming we have made a successful connection and will allow us to create subsequent Zoom meetings using these settings.

Tuesday, 19 January 2021

Connect to Zoom API with Apps Script and OAuth

The following blog post is about connecting to the Zoom API by creating a Zoom OAuth App and then using a Web App designed in Google Apps Script. Our aim here is to return an Access Token which could then subsequently be used to access Zoom account data (eg your profile, meetings, etc).

Here are some useful links:

 

Process overview

There are a set of steps that we need to complete in order to achieve successful authentication (connection with our Zoom account via the Zoom API):

  1. Have a user visit a dedicated URL (which comes from our Web App).
  2. This URL is attached to our Zoom OAuth App and upon a user visiting, it returns an Authorisation Code.
  3. Our Web App then uses this Authorisation Code along with the Client ID and Client Secret (generated when we created the Zoom OAuth App) to make another request that finally returns an Access Token, valid for 1 hour.
  4. Further blog posts will explore how we then get Zoom account data, create meetings, etc with the Access Token.