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.

Screenshot of email addresses in a Google Sheet cell

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.

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

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

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

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.

Screenshot of Google Sheets data

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

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'
};