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.
A simple right-click > Show edit history on any cell can reveal who has edited the cell in the past.
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.
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.
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.
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.
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’.
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:
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
Head over to Google’s issue tracker now and add your vote and perhaps, a comment to request this feature:
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:
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’.
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:
get signature block for automated email with Gmail
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
/**
* Retrieves either the user's primary signature block or one stored in a draft.
* @param {String} [draftSubject] - Optional unique subject line for the sign block stored in the email draft.
* @returns {String} HTML formatted string of the selected signature block.
