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