Tag: JS find

Add the Editor’s Email when they Tick the Check Box in Google Sheets with Apps Script

This tutorial is for Google Workspace Domain accounts.

Recently, I was a guest interviewee at a Google Workspace Developer Summit in Singapore and an attendee asked me how they could automatically add the editor’s email to an adjacent cell when the user checks a check box in Google Sheets.

This can be incredibly handy for project managers to see when a user completes a task or, at a glance, who edited a row.

Of course, there are some simple ways of doing this out-of-the-box within Google Sheets.

  1. A simple right-click > Show edit history on any cell can reveal who has edited the cell in the past.
    Show cell history in Google Sheets
  2. If you want a more detailed history of edits on your Google Sheet workbook then you can always select the version history button in the top right of your Sheet.

    Google Sheets Version History
    The ‘clock’ icon here opens the version history page.

The problem with these options is that it is not there on the screen for the user to quickly see who edited what line.

In this tutorial, we are going to use some Google Apps Script magic to automatically add the editor’s email to the row when they click that checkbox.

Let’s dive in!

Continue reading “Add the Editor’s Email when they Tick the Check Box in Google Sheets with Apps Script”

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”

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:

https://youtu.be/j21CWNDKA7k

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