List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script

If you have ever tried to get a list of all the child files and folders of a parent folder in Google Drive, you’ve no doubt discovered that it is a slow old process. Iterating over each item in a folder and then reading the metadata of that file or folder before calling the next one can take forever!

The built-in DriveApp Class for Google Apps Script is great for working on a small number of files and folders but it just doesn’t have the functionality to retrieve specific fields in your metadata, nor does the searchFiles method or searchFolders method have the ability to isolate just the fields that you want to retrieve. Subsequently, your processing time increases significantly as it ships junk data.

This tutorial is my attempt at, as the kiddies say today, creating a ‘blazingly fast’  file and folder iterator with Google’s Drive API v2 for Apps Script.

Continue reading “List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script”

Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets

Google Apps Script: WebApp, HtmlService, LockService; Google Sheets

In this tutorial, we are going to create an interactive story chain app that we can embed into a Google Site or your own site like WordPress.

What’s a chain story, Yagi? 

Maybe you did this in school. Someone wrote the first part of a story. You then gave that story to someone else to continue writing. They then pass the story on to someone else to write the next part. And so on and so forth. In the end, the story is read out and everyone laughs at the direction the story went – except that one kid silently raging over their lack of control of the narrative.

Why are we making this? How’s it going to help me?

Well, for one, I thought it would be fun. More importantly, this will allow us to have a look at how Google Apps Scripts communicates client to server-side and vice versa in a little more advanced environment than our previous tutorial. It will also give us an opportunity to look at some more parts of Google Apps Script as they relate to creating a WebApp.

Our chain story WebApp tutorial will also give us an opportunity to look at some of the pitfalls of using WebaApp. Particularly when using the execute as me permissions. Finally, this will then launch us into our follow-up tutorial on updating the WebApp to execute as the user rather than me, the owner of the app.

This tutorial is the second part of the WebApp series. However, if you can read a bit of JS, CSS and HTML, you should be able to follow along and if you get stuck you can always go back to the first tutorial:

Google Apps Script: How to create a basic interactive interface with Web Apps

Let’s get started…

The Example: An interactive chain story

Embedded below is our interactive Chain Story web app. If you are feeling creative, read the story so far and then add your part to the story. It has been written by readers just like you:

Continue reading “Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets”

Creating Unique Ranges from 2D Arrays in Google Apps Script

Google Apps Script, Google Sheets, Javascript 

If you have ever done any work in Google Sheets you might have come across the incredibly useful UNIQUE function. This function can take a single row or multiple rows and generate a list of unique cell values.

UNIQUE demo Google Sheets

Sometime you might have a use case where you need to replicate the UNIQUE functionality in Google Apps Script. Maybe you are drawing in data from another source like an API, BigQuery or a database, or presenting your unique data in another format.

Whatever the case, knowing how to to create a unique array from a 1-dimensional array or a 2-dimensional array is an important part of your toolkit as a coding goat ?. This tutorial will look at how to create this with modern Javascript that is incorporated into the Google Apps Script language. And of course, we’ll focus our tutorial more on a Google Apps Script because that’s kinda our thing.

We’ll start off with a very basic example of creating a unique array from a 1-dimensional array in our code:

UNIQUE Array generated from a 1d array in GAS

…and then build ourselves up to a final example by grabbing multi-column range of Google Sheet data from which we create a unique list and then display the total sum corresponding to each unique cell value in each of our associated unique columns. Something like this:

UNIQUE 2-Array of multiple columns with result set in GAS
Click to Expand!

As we go through our examples I’ll display the runtime of the entire code. Runtime is not a perfect indicator of the performance of the script there are other factors that will influence the runtime, but it will do for our purposes. I think you will be surprised by how fast these scripts run.

Before we get started, let’s take a quick look at the sample data…

Continue reading “Creating Unique Ranges from 2D Arrays in Google Apps Script”

Add the User’s Signature Block to an Automated Gmail Email with Apps Script

Sending out emails as a part of a Google Workspace automated workflow is a very common task. In Google Apps Script we can send emails to users using the MailApp.sendEmail(), the GmailApp.sendEmail() method or even as a JSON payload with the Gmail Advanced API service.

While one might expect that the sender’s signature block would also be transmitted with the automated email, we find that this is not in fact the case.

So what do we do?

The video:

Extract the Primary Signature Block with Apps Script

Let’s say we want to add the following signature block to an automated email.

Gmail Signature Block example for Apps Script

To do this, we will need to use the Gmail API advanced service.

  1. In your Apps Script project, select the Add a service plus button
  2. A dialogue box will appear. Scroll down until you find Gmail.
  3. Keep the identifier as it is and then select Add. 
  4. The service should be added to the sidebar.

Gmail Advanced Service added to Apps Script IDE

From here we can create a one-liner to extract the primary signature block HTML from the sender’s Gmail account.

The logged results for our example are a HTML string that looks like this:

I don’t see my signature HTML

If, after logging your signature and you don’t see anything, check your email configuration in Gmail under Settings > General > Signature. Ensure that you have selected a signature as default, ‘For new email use’.

Gmail Signature set as default

Understanding the One-Liner

Gmail.Users.Settings.SendAs ...

This first section of the code identifies the API path. Here we are looking at the sender’s (user’s) settings for any of the user’s aliases.

... .list("me") ...

To extract the details of the settings for the active user, the person running the script, we can use the shorthand, “me”, rather than identifying a specific email.

From this point, we would get an array of objects, one for each of the user’s aliases.

For our example user, the object would look like this:

... .sendAs. ...

First, to get into the array we need to call the sendAs property.

We wouldn’t want to extract a signature block another alias so we will ensure that we are using our default email.

... .find(account => account.isDefault) ...

To do this we can use the JavaScript ‘find’ method. This takes a function as an argument. Here we have used a simple arrow function where ‘account’ is our iterator for each array. If the default for the currently iterated account is true then we want to extract the signature string.

... .signature

Finally, we extract the signature as a HTML string.

Adding it to your code

Here is a simple example of how you can append the signature to an email.

It is important to note here that instead of adding a plain text body to the sendEmail method of GmailApp (Line 13) we have instead used the optional object insertion and added the htmlBody property.

Then, above on line 10, we combine the message with the signature ensuring we have a couple of line breaks between the two to provide good separation.

Note that you may even need to add further HTML formatting to the original message to ensure line spacing is maintained.

Accessing your other Gmail Signatures with Apps Script

So, you might be thinking,

‘I like to send different signature blocks for different circumstances. How do I do that?’

Whelp, my dear friends, as of writing this, you can’t access them at all using Gmail Advanced services. Indeed many developers are more than a little miffed at this and have been so for a number of years.

‘Well now! I’m a bit cranky about this too, if only there were a place to proactively express my displeasure at those who may be able resolve this problem.’

Support the current feature request

Protest access to all signature blocks in Gmail with Gmail API

Head over to Google’s issue tracker now and add your vote and perhaps, a comment to request this feature:

Google IssueTracker

Plan B: Using Drafts

In the meantime, we can store our alternate signature in a draft email with a unique subject line.

Here is our example of the secondary signature as a draft email:

Gmail signature block stored in a draft email to be extracted for automation

We can call this function from wherever we are sending our email. The draftTextSignature function takes the subject line of our draft email containing our desired signature block. So for our example, it would be this:

const signature = draftTextSignature("Gmail Signature Block 2");

Line 8: Retrieves all the user’s draft emails with the getDrafts() method of the GmailApp class.

Then we again use the JavaScript ‘find’ method to search for the draft containing our desired subject line.  Keep in mind that ‘find’ takes a function with an iterating parameter we set to ‘signature’.

Line 10: As the code looks through each draft we extract the subject line from the draft message.

Line 12: We then compare the current subject with our target subject line and if there is a match, ‘find’ stops and returns the current draft.

Line 16: Finally, we retrieve the body from the found message.

All-in-one Retrieve Selected Gmail Signature Function for Apps Script

If you want to have the flexibility to either extract the primary signature or retrieve one from a predefined Gmail draft then you could use this code:

Try this runsies() function out to test the code:

Just make sure you change the getSignatureBlock argument to the subject line for the draft email containing your signature.

 

~Yagi

Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

Google Apps Script, Google Sheets

On a recent board post, a Google Sheets user wanted to change a four-digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered.

Unfortunately, the user was not in a position to change the starting values, so they were left with the 4 digits.

There are two ways of doing this with varying levels of complexity:

 

  1. The Google Sheets Formula Approach
  2. The Google Apps Script onEdit Approach

Continue reading “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.”