Google App Script – Set Gmail Signature On Org Users Using A Company Template

When we first moved to Google Workspaces (Gsuite at the time) from our Exchange Server one of the basic necessities that we needed was the ability to set a company wide template for Gmail users. We found work arounds like using GAM and Windows PowerShell to set each users signature, it worked but it felt old fashioned and duct taped in. We have decided to move away from the need to have a server or machine to house this script form Windows Active Directory. Also it never felt right with me that there was a tool like GAM sitting on a machine that had full domain access. We didn’t trust having that kind of entry point to our entire organization data just sitting on a windows machine just to set a Gmail signature.

We later moved to a Google Workspace reseller called Promevo that had a product call gPanel that is just like the Google Admin site but 100x better and constantly getting new features. It had lots of features and bells and whistles that Google could provide but has not programmed into their GUI. They also had a tool for applying signatures across the domain and we have been using this tool for the last few years for our signature needs. Circumstances have caused us to have to find a new reseller that is in Canada not the US so that we can get proper licensing for Google Voice. When doing the planning on how this change will affect us, one of issues that came up from leaving Promevo was the loss of using their signature tool. I have been getting better at my Google App Script capabilities so I decided to see if there was a way to do this with my new found skills. After searching the web for how to do this assuming lots of other people must have done this before. What I found were partial examples in different programming languages. The API documentation helps a bit but it did not help with any useful examples. I found lots of examples how to set your own signature using the GAS built in GmailApp Class. So AGAIN I am forced to do it myself and make a blog because there are no examples on the internet on how to do this. Please note this is not just a simple copy the code and run it. It requires that you have full admin access to your Org in Google Workspace and the ability to create new Google Cloud Projects. I will try to explain each step in as much detail as possible. So enough jibber jabber, here is the solution I came up with.

Custom Attributes

I decided to not use a Google Sheet to store all the settings as to who gets a signature and the values to place in the signature template. I wanted to integrate this so when setting up a new user we could use a single piece of glass (Google Admin) to manage these settings so when creating a new users you don’t have to go to 3 different places every time or remember to clean up after a user leaves.

In my last script I learned about creating Custom Attributes for user profiles so I decided to try an implement this into the signature script. Based on our needs I decided to create the following custom attributes:

To setup Custom Attributes go to “Google Admin > Users > More > Manage Custom Attributes > Add Custom Attribute” or click the link . I created my Custom Attribute as follows:

Google Admin – Add Custom Attribute

For our organization and our needs I felt these custom attributes would serve us the best. The thought behind these attributes were as follows:

Enforce Default Signature (Yes/No) – If this is set to Yes then the script will include this user in the next template run. Not all users need to have a signature set, also there are special cases where service accounts or role based accounts want to have their own signature not set by the org.

Exclude Mobile (Yes/No) – There are some users that have a company provided mobile device but they do not want to put it in their signature.

Certifications (Text) – Some users that have certain certifications or qualifications that want to have them placed after their name. For example if someone is a Chartered Professional Accountant they want to have CPA show up after their name.

Custom Template ID (Text) – There are some users that need to have a signature automatically applied but don’t follow the default template. This field allows you to enter a Google Drive File ID of the template that you want to use for this user instead. Make sure the user running the Google Active Script has view permissions on the file or the folder it’s in.

You can add more features or less and the coding is fairly simple to include any logic later to deal with these custom attributes. For now I would suggest setting this Enforce Default Signature setting on a dummy test account or yourself for testing till you 100% know it works.

Signature Templates

For our use I decided to create a Google Shared Drive and have all the template files and config inside that Shared Drive and gave my script user access to view this folder as I use the DriveApp to access these files. I did this so I could allow other users with access to change or modify the config to use another template file without the need for an admin to do it. For example we normally have 4 or 5 campaigns running at a time in our Org and we switch the signature once a week to the next campaign. A basic user could just update the config file to reference the default template for that week by editing one file. Our signature script runs every hour so within an hour everyone will have the new signature applied to them. In the future I might use the Google Drive REST API to get the file but till then I am going to just use the built in DriveApp. If you want you can store these files in a My Drive folder just ensure the user running the script has View access.

Below is a sample HTML template to apply to all the users

<div style="max-width: 400px; FONT: 8pt Helvetica; color: rgb(110, 110, 110);">
<div><span style="font-size: 13pt;">{FirstName} {LastName}</span><rt> <span style="font-size: 7pt;">{Certs}</span></rt></div>
<rt><div style="font-size: 9pt;">{JobTitle}</div></rt>
<rt><div style="padding-top: 5px;">{WorkNumber} OFFICE</div></rt>
<rt><div>{MobileNumber} MOBILE</div></rt>

The signature will look like this:

If you have done any HTML in the past you might notice some HTML that you have never seen before! I have used <rt></rt> to stand for remove tag. The reason we need these tags is so we can apply some logic to our template. In a perfect world every single users would have a value to fill in every one of these {Tags} in the template. Well as you know we don’t live in a perfect world so we have to anticipate the possibility that a user might not have a Mobile number or a Certification to place after their name.

Let’s look at an example:

If we have a user that does not have a Mobile Number, and we ran their data through the signature script, their signature would look like this

As you can see the {MobileNumber} tag was replaced with nothing because the user did not have a Mobile Number, but the rest of the HTML is still there that has to do with the Mobile Number. So I had to create some logic in my script to account for this and use some HTML elements (<rt></rt>) to help with the removal of unwanted HTML when a {Tag} has no value

The logic is basically this

  • if there is a {Tag} that is NOT contained inside of <rt></rt> elements, replace the {Tag} with the value, blank or not.
  • If the {Tag} IS contained inside of <rt></rt> elements, AND the {Tag} has a value, replace the {Tag} with tis value and remove the <rt></rt> HTML elements.
  • If the {Tag} IS contained inside of <rt></rt> elements, AND the {Tag} does not have a value then remove the <rt></rt> elements and everything in between the elements.

Hopefully this explains why I have created the HTML template as such. I could have used other special characters like [ ] to enclose these lines but when previewing the HTML template it broke the HTML code. The way I choose allows the HTML to display properly so you can preview what a template will look like.

If you have any suggestion or way to do this better I am always up for that discussion.

Configuration File

This file is read in when the app starts and will contain the Google Drive File ID of the Default Template file. If you want to change the default template to another file then replace the Google Drive File ID in here with the new Google Drive File ID. You don’t have to do this and you could just set this value at the beginning of the script, but by doing it this way it allows non admin users to change the default template.

Create a file called config.txt and put the following in it, make sure you replace {Your Google Drive File ID} with the Google Drive File ID of the default template you created in Google Drive above.

config.txt

{"emailTemplateID": "{Your Google Drive File ID}"}

Google Cloud Project

We need to impersonate users so that we can set their signature so we need to use a service account that has the permission to do so. To do this you will need to set up a new Google Cloud Project. Call it something descriptive so you knw what it is for in the future. Once the new project has been created, switch to it.

Create a service account

Name your service account something descriptive so you know what it is. Give it a description if you want.

Leave the Grant this service account access to project page as is and same with the Grant users access to this service account and click done.

You will be returned to the service account page and you should see the service account you just created. Click on it

We need to enable Domain Wide Delegation on the account and you will have to give it a name for the Oath consent screen. Click Save.

If you click on the service account again and you open the Domain Wide Delegation again you will now see there is a Client ID available to you. Please note this Client ID as you will need it in a few steps. You can always come back later to this page to get it.

You will now need to go to your Google Admin API page and allow this Client ID to have access to certain API scopes for your domain. The point of this is to restrict this service account to only be able to do certain things. Why, because you just gave the service account domain wide delegation capabilities. This does not mean they have domain wide delegation rights yet, it just means if given, they have these rights. When you give them access to your Google Org data you are only going to give them the privilege’s the service needs to do it’s job and nothing more. Please remember that Google Cloud and Google Workspace are two different products and don’t have anything to do with the other. So you have to create the connection between the service account and your Google Ord data using this domain wide delegation option.

Add a new client and paste the client ID that you obtained in the last step here. the Oauth scopes that we will need for this project are:

https://www.googleapis.com/auth/admin.directory.user.readonly
This allows us to connect to the directory API and get a list of domain users, we only need read only access

https://www.googleapis.com/auth/gmail.settings.basic
This allows the script access to the Gmail basic API to allow the setting of signatures

Click Authorize

You should see the name you gave your Service Account on the Oauth screen you set previously here. This is why we name things descriptively so that later (2 years form now) when you see this you know why it’s there!

What you have just done is created a service account and given the service account permission to access your Google Workspace Data using use the ‘Gmail Basic’ and ‘Directory Read Only’ APIs. We still need to create credentials for this service account but we will do that in a bit. All the ground work is now complete we can FINALLY get onto the code!

THE CODE FINALLY

Start up a new Google App Script project and call it something descriptive like “Gmail Signature Templator”. You will need to create the following files in the IDE:

code.gs (Script) – This is the main code
serviceAccount.json.html (HTML) – This holds the service account credentials file
serviceAccount.gs (Script) – This connects and gets Bearer tokens to access APIs
getSignatureUsers.gs (Script) – This gets the users from the Directory API
setSignature.gs (Script) – This set the signature using the Gmail API
helperFunctions.gs (Script) – Small functions used to parse and sort data

Libraries
OAuth2 – 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF – Add this Library ID to your project

code.gs

Change line #17 to point the the Google Drive File ID of the config.txt file you created earlier
Change line #31 to the name of the domain you want to search and the email address of an admin account as the Admin SDK Directory API requires the impersonation of an real admin account to access it.

/**
 * Script created by MrCaspan of caspan.com
 *
 * This script will get all signature users for the domain and apply a default signature to their default Gmail address
 * 
 * Pre conditions to allow this script to run:
 * Custom Attribute on domain users - https://admin.google.com/ac/customschema
 *   Category - Gmail Signature
 *     New Attribute - Enforce Default Signature  - Include this user in any signature runs
 *     New Attribute - Exclude Mobile             - Lets us exclude a users mobile number form their signature
 *     New Attribute - Custom Template            - This value is a Google Drive File ID to a custom HTML Template to use
 *     New Attribute - Certifications             - If the user has any kind of certifications
 * 
 * Please follow any documentation on any of the add on scripts
 */

const configFileID = '{FileID}';  // This file holds the configuration for the default template

/**
 * This function should be the first to run
 */
function start() {

  // Load the config
  var CONFIG = loadConfig();
 
  // Load in the default template
  var defaultTemplate = loadTemplate(CONFIG.emailTemplateID);

  // Go get users for our signature run
  const users = new getUsers('{Domain}', '{Email Address of Admin}');

  // For each one of the users
  for (const user in users) {

    Logger.log(`Getting values for ${user}`);

    // Take the phone schema and build an object
    const phones = new createPhoneObj(users[user].phones);

    // Always assume they are going to use the defualtTemplate unless replaced with a custom template further down
    var template = defaultTemplate;

    // Set up all the template values for them
    var templateValues = {
      '{FirstName}'     : users[user].name.givenName,
      '{LastName}'      : users[user].name.familyName,
      '{JobTitle}'      : users[user].organizations[0].title,
      '{MobileNumber}'  : phones.hasOwnProperty('mobile') ? phones['mobile'] : '',
      '{WorkNumber}'    : phones.hasOwnProperty('work')   ? phones['work']   : '',
      '{Certs}'         : getCustomSchemasValue(users[user].customSchemas.Gmail_Signature.Certifications)
    }

    // Because we only returned users that have the custom schema Gmail_Signature set there is no way to get
    // Users beyond this point that dont have this custom Schema set so assume they do

    // Does the user have a Custom Template ID set
    if (getCustomSchemasValue(users[user].customSchemas.Gmail_Signature.Custom_Template_ID)) {

      // Replace the template with the custom one
      template = loadTemplate(users[user].customSchemas.Gmail_Signature.Custom_Template_ID);
      Logger.log(`${user} has cutom signature template`);

    }

    // Does the user want their Mobile device removed from their signature
    if (getCustomSchemasValue(users[user].customSchemas.Gmail_Signature.Exclude_Mobile)) {

      templateValues['{MobileNumber}'] = '';
      Logger.log(`${user} has requested their mobile number not show`);

    }    

    Logger.log(`Values are: ${JSON.stringify(templateValues)}`);

    // Take the users template values and replace then in the users template
    template = doTemplating(template, templateValues);

    // Set the users signature
    setSignature(user, template);

  }

  Logger.log('Script is done!');

}

/**
 * This function will take the values and the passed template
 * replace vales and templating tags 
 * 
 * @param   {string}  template  The template that holds the HTML
 * @param   {object}  values    An object of key/value pairs to be replaced
 * @return  {string}            Returns the passed template with tags replaced
 */
function doTemplating(template, values){

  Logger.log(typeof template);
  Logger.log(`Applying these values to template tags...`);

  const regex1 = new RegExp('<rt>(.*?)<\/rt>');
  const regex2 = new RegExp('{(.*?)}');

  // For each one of the values
  for (const propterty in values) {

    // if there is any value then replace its tag
    if (values[propterty]) {
      var searchRegExp  = new RegExp(`${propterty}`, 'g');
      template = template.replace(searchRegExp, values[propterty]);
    }

  }

  // While there are still <rt></rt> tags in the template
  while (regex1.exec(template)) {

    // If there is a {tag} inbetween the <rt></rt> elements
    if (regex2.exec(regex1.exec(template))) {

      // Replace the <rt>(.*)</rt> with nothing
      template = template.replace(regex1, '');
    
    // there was not a {tag} found
    } else {

      // Replace the <rt> </rt> with the non <rt> match version
      template = template.replace(regex1.exec(regex1.exec(template))[0], regex1.exec(regex1.exec(template))[1]);

    }

  }

  Logger.log(`Complete`);
  return template;
}

getService.gs

/**
 * This script will take a service account JSON key file 
 * It will authenticate to Google as an impersonated or non impersonated user
 * It will create a service object that holds the bearer token needed to access APIs
 * This script requires the OAuth2 Libraries, https://github.com/googleworkspace/apps-script-oauth2
 * Add this library 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF to your script
 * 
 */


// Load the service account JSON file as an object
const serviceAccount = JSON.parse(HtmlService.createHtmlOutputFromFile("serviceAccount.json").getContent()); // Parse the JSON file to an object

/**
 * Will return a service object and bearer a token 
 *
 * @param {string} name A unique discriptor for this token, If credentials have to change use a differnet name
 * @param {string} scopes A list of Google Scopes (space seperated)
 * @param {string} userToImpersonate If you want to impersonate a user like in the case of an API like the Admin SDK {optional}
 * 
 * @return {object} A service object with the bearer token
 */
const getService = (name, scopes, userToImpersonate) => {

  if (userToImpersonate != '') {
    return OAuth2.createService(name)
      .setSubject(userToImpersonate)
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')
      .setPrivateKey(serviceAccount.private_key)
      .setIssuer(serviceAccount.client_email)
      .setPropertyStore(PropertiesService.getScriptProperties())
      .setScope(scopes)
      .setLock(LockService.getScriptLock())
      .setCache(CacheService.getScriptCache());
  } else {
    return OAuth2.createService(name)
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')
      .setPrivateKey(serviceAccount.private_key)
      .setClientId(serviceAccount.client_email)
      .setPropertyStore(PropertiesService.getScriptProperties())
      .setScope(scopes)
      .setLock(LockService.getScriptLock())
      .setCache(CacheService.getScriptCache());
  }
}

/**
 * This function will reset the service and clear any cache
 */
function reset() {
  getService().reset();
}

getUsers.gs

/**
 * Script created by MrCaspan of caspan.com
 *  
 * Requires the use of getService.gs
 * 
 * Pre conditions to allow this script to run:
 * A new Google Cloud Project that is used only for this entire script
 * Enable the Google Admin SDK API
 * A service account that has a JSON key file
 *  This service account must have domain wide delegation
 *  The Client ID of the service account must be added to the Google Admin https://admin.google.com/ac/owl/domainwidedelegation
 *  You will then need to allow the scope https://www.googleapis.com/auth/admin.directory.user.readonly
 *
 * Documentation
 * Scope - https://developers.google.com/admin-sdk/directory/v1/guides/authorizing
 * API   - https://developers.google.com/admin-sdk/directory/reference/rest/v1/users/list
 * Prams - https://developers.google.com/admin-sdk/directory/v1/guides/search-users#examples 
 *
 * @param {string} domain, The domain to get the user from
 * @param {string} impersonateAccount, account to impersonate
 * 
 * @return {object}, an object of domains users that require signature templating
 */
function getUsers(domain, impersonateAccount) {
   
  Logger.log(`Getting users for the domain ${domain}...`);

  const service = getService('AdminSKD-Directory', 'https://www.googleapis.com/auth/admin.directory.user.readonly', impersonateAccount);
  if (!service.hasAccess()) {Logger.log('There was a service error:' + service.getLastError());return;}

  var url = 'https://admin.googleapis.com/admin/directory/v1/users' +
            '?domain=' + domain + // Set this to the domain to get users
            '&maxResults=500' +   // This is the Max number of results to return
            '&projection=full' +  // Include all fields associated with this user.
            '&query=Gmail_Signature%2EEnforce_Default_Signature%3Dtrue';  // Make to URL Encode your query

  const options = {
    method: 'GET',
    headers: {'Authorization': 'Bearer ' + service.getAccessToken() },
    contentType: 'application/json',
    muteHttpExceptions: true
  };

  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() != 200) {Logger.log(response);fail;}

  // Turn the response into an object
  var response = JSON.parse(response);

  // Make an object that has propteries that are the users email address
  // and the value be the users data to make it easier to access
  for (const user of response.users) {
    this[user.primaryEmail] = user;
  }

  Logger.log('Complete');

}

setSignature.gs

/**
 * Script created by MrCaspan of caspan.com
 * 
 * Requires the use of getService.gs
 * 
 * Pre conditions to allow this script to run:
 * A new Google Cloud Project that is used only for this entire script
 * Enable the Gmail API
 * A service account that has a JSON key file
 * This service account must have domain wide delegation
 * The Client ID of the service account must be added to the Google Admin https://admin.google.com/ac/owl/domainwidedelegation
 * Allow the scope https://www.googleapis.com/auth/gmail.settings.basic
 * 
 * Documentation
 * Scope   - https://developers.google.com/admin-sdk/directory/v1/guides/authorizing
 * API     - https://developers.google.com/gmail/api/reference/rest/v1/users.settings.sendAs/get
 * payload - https://developers.google.com/gmail/api/reference/rest/v1/users.settings.sendAs#SendAs
 */


 /**
 * Sets the signature on a user
 * 
 * @param {string} user, the email address of the user to template
 * @param {string} signature, the template to apply to the user's Gmail signature
 * 
 */
function setSignature(user, signature) {

  Logger.log(`Setting signature for ${user}...`);

  var serviceSignature = getService(`GoogleDrive: ${user}`, 'https://www.googleapis.com/auth/gmail.settings.basic', user);
  if (!serviceSignature.hasAccess()) {Logger.log('There was a service error:' + serviceSignature.getLastError());return;}

  // Doc - 
  var url   = `https://gmail.googleapis.com/gmail/v1/users/${user}/settings/sendAs/${user}`;

  const payload = JSON.stringify({
    "sendAsEmail"       : user,
    "isDefault"         : true,
    "replyToAddress"    : user,
    "signature"         : signature
  })
 
  const options = {
    method              : 'PUT',
    headers             : {'Authorization': 'Bearer ' + serviceSignature.getAccessToken() },
    contentType         : 'application/json',
    muteHttpExceptions  : true,
    payload             : payload
  }

  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() != 200) {Logger.log(response);fail;}

  Logger.log('Complete');
  return;

}

helperFunctions.gs

/**
 * This constructor will take a phone schema and build an object
 * it will also support custom phone types
 * 
 * @param   {object}  phoneSchema   An object of phone numbers form the directory service
 */
function createPhoneObj(phoneSchema) {

    // Loop around phone schema to get phone values
    for (const propterty in phoneSchema) {

      var proptery = phoneSchema[propterty].type;
      var value = phoneSchema[propterty].value;

      // If the type is custom 
      if (proptery == 'custom'){
        
        // Use the customType Value instead
        proptery = phoneSchema[propterty].customType;

      }

      // Add the number to the phone object
      this[proptery] = value;

    }

}

/**
 * This function will load the email template file as specified by the config file
 * 
 * @param   {string}  fileID  The Google Drive ID of the file to open
 * @return  {string}          The contents of the FileID passed
 */
function loadTemplate(fileID) {

  try {
    var file = DriveApp.getFileById(fileID);
  }catch(e){
    Logger.log('The file ID is not a valid file or you dont have permissions to it' + e);
  }

  // Return the file contents
  return file.getBlob().getDataAsString();

}

/**
 * This function will load the config file and convert it to an object
 * 
 * return   {object}    This object will hold the data from the config file
 */
function loadConfig() {
  const file = DriveApp.getFileById(configFileID);
  const fileContent = file.getBlob().getDataAsString();
  return JSON.parse(fileContent);
}

/**
 * This function will check to see if the custom schema exists
 * 
 * @param   {object}  customSchema  The custom Schema to test for
 * @return  {string}                This will return either '{blank}' or the value of the Custom Schema
 */
function getCustomSchemasValue(customSchema) {

  // Test to see if the customSchema is undefined (does not exist)
    if (typeof customSchema === "undefined") {
              
    // Set the values the user has set
    return ''

  // It must exist
  } else {
    
    // Return the value stored in it
    return customSchema;

  }

}

serviceAccount.json.html

You will need to get the contents for this file form the service account we created before. We need to generate a key for the service account. This will download a JSON file to your computer. Copy the contents of this file into this file.

Go to your service account you created and client the 3 dots and click ‘Manage Keys’

We are going to create a new key

Choose the JSON format and click create, this will automatically download a file to your computer

This JSON file is the keys to your service account. Anyone that has them can use your service account so guard them and protect this file. I recommend that once you have this files contents copied into the app that you securely delete it as you will never need it again once stored in the script. If for some reason you ever loose this key you can just delete it from the service account and generate a new one.

The file contents should look like this

Copy the entire contents of this file into the serviceAccount.json.html file in your project

Testing The Script

The first time you run the script it will ask for access to Google Drive (to get the config file and the template) as well as external access (the app uses UrlFetch to connect to the APIs). I would recommend that you again do your signature testing with a dummy account and check that each settings works as expected. I would then add another user and just test it. Once you are happy and its tested that its working fine you can start to apply it to other users. Please remember I am just a IT guy that programs and I have only coded for our environment (~100 users) I have no idea how this code will do at large scale. Would love to know how many users you are using it for and the average time it takes to run the script for you. I might have to add a feature that only does 50 users at a time then call the script again to ensure it can finish as there is a 6 minute limit to Google App Scripts and 30 second limit to custom Functions. I have made the script verbose so that you can see it working as it runs. After you are sure it is running you can remove the noisy Logger.log() calls but I recommend leaving them in so if there is an issue you can see the logs of the last run.

When testing please make use of the reset() function at the beginning of your script as this will ensure that when you are testing only new bearer tokens are generated and old ones are not used causing weird issues for you.

Automate the Script

I have created a timed based trigger that executes the start() function every 1 hour to ensure the signature is constantly applied to the user. Other cool ideas would be the ability to trigger the script using a bot Direct Message or a webApp that you can hit using a URL. the ideas are endless

Feedback

This is my first attempt at writing such a complex script and sharing it to the world. I am open to constructive criticism and notes about things I have botched. Please I would love to get your feedback even if it’s just a pat on the back to say “Thanks I needed this script and it works for me”.

© MrCaspan 2021