Change file permissions from Editor to Viewer

The following Google Apps Script is designed to change the Google Drive file permissions of a specific user from Editor to Viewer. The function actually came about when needing to end a process where a user had been editing a Google Sheet on a Shared drive that they should only then have Viewer access to. To complicate matters the Apps Script code is running as said user when it needs to reduce their own permissions.

Firstly you will need to enable the Drive API in the Script Editor by going to 'Services' > 'Drive API' > 'Add'. Next we will look to get the permission ID for the email address of the user we want to change access for:

var permissionId = Drive.Permissions.getIdForEmail('email address here').id;

Google Add-on: Archive Sheet data

Easily move/archive a row of data from one Google Sheet (tab) to another, within the same file. If you prefer something a bit quicker than cut/paste and with less chance of human-error. It will also remember your individual preferences for each file so it is even quicker to use next time around.

This is a Google Workspace Add-on designed to work in Google Sheets via the Side Panel and is freely available for all. It is a packaged-up version of my previous Archive Sheet data post.

I will be breaking down the new skills I have acquired in future blog posts with the hope of helping you to save time if you do something similar.

Create a new Sheet tab and format a header row

The following Google Apps Script was developed as part of a larger tool for collating file information into a specifically formatted Google Sheet. As the tool was an Add-on I needed to setup the Google Sheet file in advance so that information could be later appended to it. This code:

• Checks if a particular named Sheet (tab) already exists within the Google Sheet file. If it does then it will delete the existing Sheet and create a brand new one.
• Reduce the overall number of columns within the Sheet.
• Insert 1 row of data to use as the Header row.
• Set the Header row font size, colour, weight (bold) and horizontal/vertical alignment.
  
• Set the Header row background colour, height and column widths.
  

Nicely formatted Header row for appending data to

Google Add-on: Search Drive for owned files

I am happy to announce my first ever official Google Workspace Marketplace Add-on!

Search Drive for owned files - enter an email address to find all of the Google Drive files that are owned by that account and export the results into a Google Sheet for easy viewing.

This is an Editor Add-on designed to work in Google Sheets and is freely available for all. It is a packaged-up version of my previous Search Google Drive for owned files post for those interested in what most of the Apps Script code looks like.

I will be breaking down the new skills I have acquired in future blog posts with the hope of helping you to save time if you do something similar.

Replace text in a Google Doc with a hyperlink

The following Google Apps Script is designed to search the body of a Google Doc for a specific string/pattern (ie a keyword I have used) and insert a clickable hyperlink. Typically I use the JavaScript replace method when going through the body of a Doc and inserting data. However I recently came across a difficulty where I was inserting data into a table that was causing the long hyperlinks to split between 2 lines and hence lose their click functionality. With full credit to this blog post by Yagisanatode I found a way to overcome this.

Starting in the usual manner we pick-up our Google Doc and get its body, so that we can start to work on the content:

var docBody = DocumentApp.openById('Doc ID here').getBody();

Now we look to use the 'findText' method to search the content for our string/pattern. From this we are returned the position (range) and need to 'getElement' with 'asText' so that it can be edited:

var getString = docBody.findText("<<keyword>>").getElement().asText();

Finally we can set the text that will be displayed in the Google Doc in replace of our keyword along with our hyperlink for when a user clicks on it:

getString.setText("Useful link to click").setLinkUrl("www.pbainbridge.co.uk");

Convert Google Doc to PDF in a given folder

As of February 2023 'Convert Drive Files' is now available as a Google Workspace Marketplace Add-on.

The following Google Apps Script is designed to create a PDF file of a Google Doc in a Drive folder that you specify, with the option to delete the original Doc. This snippet of code is from larger solutions developed on this blog and allows you to understand and replicate the process.

The Code

Firstly we get our Google Doc (that we want to convert to a PDF) and the Google Drive folder where we want our PDF to be stored:

  // get Google Drive folder
  var folder = DriveApp.getFolderById('ENTER ID HERE');

  // get Google Doc file
  var file = DriveApp.getFileById('ENTER ID HERE');

Next we get the content of our Google Doc as a PDF (blob) and create our new file in the Google Drive folder:

  // get file content as PDF blob
  var pdfBlob = file.getAs('application/pdf');

  // create new PDF file in Google Drive folder
  folder.createFile(pdfBlob);

Finally, and optionally if you want to comment this part out, we delete the original Google Doc that we no longer need:

  // delete original Google Doc file
  file.setTrashed(true);

Totally Unscripted: The Pulse of the Google Apps Script Community

I was very honoured to be invited to join the Totally Unscripted show on Wednesday 27 October 2021 after achieving one of the top 3 AppsScriptPulse contributions of 2021!

The show is hosted by Martin Hawksey, Charles Maxson and Steve Webster. It was an opportunity to join 2 fellow Apps Scriptors (Romain and Scott Donald) to discuss our backgrounds and experiences. 

Thank you to everybody who has been using this blog as I share my learning with Google Apps Script. Below is an embedded YouTube video of the show:

 

Bulk create Google Drive Folders and add Files 2.0

The following Google Apps Script tool was developed to bulk create Google Drive folders with optional files copied in to each one, all from data within a Google Sheet, along with the ability to adjust all folder/file names used.

It is a variation of this Bulk create Google Drive Folders and add Files tool post.

Bulk create Google Drive folders with optional files, from a Google Sheet

Bulk convert Google Docs to PDFs 2.0

As of February 2023 this tool is now available as a Google Workspace Marketplace Add-on.

The following Google Apps Script tool is designed to bulk convert all Google Docs within a given Google Drive folder into PDFs. You can choose the destination folder for the PDFs to be put into and also whether you want the original Docs to be deleted. This tool does work on Shared drives.

Improvements / Features

1. Maximum runtime - in order to prevent the tool from reaching the limits imposed by Google you can adjust the number of minutes the tool can run for. Change this in the 'GlobalVariables.gs' file in the Script Editor.
2. Continue from where it left off - if you have a lot of Google Docs to convert and the above runtime is reached the tool will save its progress and prompt you to run it again, avoiding any file duplication.
3. HTML popup - as well as the 'Log' sheet the tool now displays a direct popup to the user if it encounters a problem.
4. PDF counter - after successfully running the tool will include the number of PDFs created as part of the success popup to the user.

Bulk convert Google Docs to PDFs

As of February 2023 this tool is now available as a Google Workspace Marketplace Add-on.

The following Google Apps Script tool is designed to bulk convert all Google Docs within a given Google Drive folder into PDFs. You can choose the destination folder for the PDFs to be put into and also whether you want the original Docs to be deleted. This tool does work on Shared drives.

Bulk convert Google Docs to PDFs using Apps Script

Find and replace in a Google Sheet - ReplaceAllWith

This blog post is a slight adaptation of the one here for Find and replace on a whole Google Sheet. This time we are focussing upon the fastest way to change all of the text values without highlighting changed cells. In effect we have deleted a chunk of code and replaced it with one single line:

textFinder.replaceAllWith(replaceWord);

So whilst we can no longer target a specific cell for any formatting changes, this method saves us time by not performing any more loops, and so may be your chosen method for speed.

Bulk create Google Drive Folders and add Files

The following Google Apps Script tool was developed to bulk create Google Drive folders with optional files copied in to each one, all from data within a Google Sheet. There is also the option to add specific 'edit' permissions to the newly create Drive folders of which the files would automatically inherit this access level.

The tool is an expansion of the 'Bulk create Google Drive folders 2.0' blog post here, so you may wish to read and watch the video on there first. Also note I have version 2.0 of this tool available now.

Bulk create Google Drive folders and add files, from a Sheet of data

Key Functionality

• Complete the necessary information in the Config sheet before proceeding. Then use the Create folders option from the Admin menu at the top of the Google Sheet.
• Adding permissions is optional - use the Config sheet to change the dropdown as required. If you select 'No' then the usual Google Drive inheritance will occur based on the parent Google Drive folder.
• You can add multiple File IDs into the relevant cell and they can be different for each row. Leaving this cell blank/empty means no files will attempt to be copied into the new folder.
• The original filename will be updated during the copy to append the folder name to the end of it, in order to prevent creating a large number of files with identical names.
• There is a Log sheet to help troubleshoot any errors which may occur when running the tool.

Send data to a Google Form via Apps Script

The following Google Apps Script is designed to submit specific data to a Google Form, by creating a prefilled URL. The reason for this was I needed some way of sending data from a number of individual Google Sheets (that I did not own) to one central location, but crucially it needed to be anonymised.

Sending the data directly to a Google Sheet includes version history both in the file and cell meaning it was not truly anonymous. Whereas sending the data through a Google Form and then on to the Response Sheet did strip away anything identifiable.

The logic behind this code is to create a prefilled URL which contains answers to each of the Forms questions. Use the previous webpage link to learn how to create such a URL first so that you understand each question on a Form has a unique value.

Screenshot of Google Form questions

Search Google Drive files and extract row data

The following Google Apps Script is designed to search the content of files on Google Drive which includes the given search word(s). Once it has collated these files it will then go into each one, search for the row of data that matches our search word(s) and extract that into a collective Google Sheet.

The information collected from the initial search to collate the files includes:

• The filename,
• The file ID,
• A direct/clickable link to the file,
• The type of file - this tool is restricted to Google Sheet.

The information collected from the second part of the tool includes:

• A direct/clickable link to the file,
• The Header row from the file,
• The row of data from the file.
  

Search and extract the content of Google Drive files

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, Google Sheet/Doc, etc,
• The file creation date, 
• The file last updated date.
  

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

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 / End date values in a Google Sheet

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

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

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

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

As of March 2024 this tool is now available as a Google Workspace Marketplace Add-on.

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

As of March 2024 this tool is now available as a Google Workspace Marketplace Add-on.

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

As of March 2024 this tool is now available as a Google Workspace Marketplace Add-on.

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.

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:

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

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.

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:

• How to create an OAuth App in Zoom.
• Zoom App Marketplace.
  
• How to deploy a Google Apps Script as a Web App.
  

 

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.