Setting domain wide signatures for google mail

Using Google Apps Script and Google docs

While Google mail’s compliance footers are great for legal compliance, Google currently don’t offer an easy way to set signatures for all your users that are based on user specific variables. I had a quick look at the existing offerings but I also noticed that due to the openness of Google’s APIs it's easy to pull data from a Google spreadsheet and push out the signatures to users across a domain. Giving us:

• Centrally managed user data in a simple spreadsheet 
• A legal compliance footer enforced on all outbound emails 
• A quickly customisable signature with contact details that can be edited/updated by users (no more waiting for IT to update your contact details) 

As Google Apps Script doesn't connect to the Google email settings API directly, this also makes use of Google Apps Script's support of oauth to securely authenticate from Google Apps Script to the Google email settings API. This post includes setup instructions and an overview of how it works, if you just want the code feel free to skip to the end...

Variables

There are three types of variables, they all use the same syntax so it's important to know that they are resolved in the below order:

• Company wide variables
• These can be used for Logos or other things that are the same across the entire company
• They are taken from the Signature Settings sheet (this sheet also contains the template)
• Group wide variables
• These can be used for team specific parts of a signature, certification logos or office contact info
• These are taken from the Signature Group Settings sheet 
• They must mach a Summary sheet column heading, so each user has a separate value for the setting, but they get substitute out if a table to replace them exists in the Signature Group Settings sheet
  
• User specific variables
• These are per user settings (E.g contact numbers, names, etc)
• They are taken from the Summary sheet column headings

note: The sheet names mentioned above are currently hardcoded names to prevent too many settings, but if you have a look at code you'll see they can easily be changed.

Syntax

The syntax for the templates is fairly simple (though suggestions for making it more readable are welcomed

• The script will prompt you for 4 variables
• regOpen this goes at the front of a section to be substituted, I use ${
• regClose this goes at the end of a section to be substituted, I use }$
• tagOpen this goes at the front the variable to be substituted, I use {
• tagClose this goes at the end of the variable to be substituted, I use }


• If the variable is blank for the user then the section from regOpen to regClose is removed
• If there is a value for the variable then it is put in between tagOpen and tagClose and one set of opens and closes are removed but the text between them is left
• If you use multiple instances of the variable in a section that might be removed then the regOpen tags must be repeated.

e.g

 [t] - 0207 183 3893 ${ (ext. {Office Ext #}) }<br>  
 ${[m] -<a ${href="tel:{Mobile #}"} ${value="{Mobile #}"}>{Mobile #}<a><br>}$  
 ${{Primary Office}}<br>   

Authorisation

The script requires the user running it has domain superadmin privileges to run as it uses the provisioning API. The authorisation chain is a little confusing, but it is done this way to prevent having to put the domain oauth key into the script properties. You have to setup a project, then input it's oauth details into the script. You could skip this extra step and put the domain wide oauth key in the user script properties are safe and can't be read by other users, however as you're giving domain wide access with these credentials extra security is a good idea.

Get the Oauth details

1. Go the Google API console https://code.google.com/apis/console/b/0/
2. Create a project
3. Create an Oauth 2.0 client ID  (Type "installed application" > other)
4. Copy the clientID "e.g 10XXXXXXXXXXX.apps.googleusercontent.com" with scope https://apps-apis.google.com/a/feeds/emailsettings/ 
5. Use the ClientID and Client secret from the api console
   
6. Put the details into the scripts user properties

Authorising the script

1. The first time you run the script it will ask for authorisation to run the script
2. Then it will ask for authorisation to access the project
3. Then the next time you run it, it will ask for access to the Gmail Settings APIs
4. After this everything is authorised

Code

The code is  mostly self explanatory, however if you have any questions or feedback please let me know.
 

	function getSignature() {
	//pretty basic function for testing
	if ( startupChecks()) { return; }
	var email = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue().toString();
	if ( email === "" ) {
	Browser.msgBox("No email selected", "Please select a cell containing a user's email" , Browser.Buttons.OK);
	return;
	}
	var result = authorisedUrlFetch(email, {});
	Browser.msgBox(result.getContentText());
	}
	function setIndividualSignature() {
	Logger.log('[%s]\t Starting setIndividualSignature run', Date());
	if ( startupChecks()) { return; }
	var userData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary');
	var template = getTemplate();
	var row = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getRow();
	if (userData.getRange(row, 1).isBlank() === true) {
	var msg = "Please select a cell on a row containing the user who's signature you wish to update";
	Browser.msgBox('No valid user selected', msg, Browser.Buttons.OK);
	} else {
	setSignature(template, userData, row);
	}
	Logger.log('[%s]\t Completed setIndividualSignature run', Date());
	}
	function setAllSignatures() {
	Logger.log('[%s]\t Starting setAllSignatures run', Date());
	if ( startupChecks()) { return; }
	var userData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary');
	var template = getTemplate();
	//Go through each user listing
	for ( row = 2; row <= userData.getLastRow() ; row++) {
	setSignature(template, userData, row);
	}
	Logger.log('[%s]\t Completed setAllSignatures run', Date());
	}
	function getTemplate(){
	Logger.log('[%s]\t Getting Template', Date());
	var settings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Signature Settings');
	var template = settings.getRange(2, 1).getValue().toString();
	//Substitute the company wide variables into the template
	template = substituteVariablesFromRow(template, settings, 2);
	return template;
	}
	function setSignature(template, userData, row){
	var groupData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Signature Group Settings');
	//Google Apps Scripts always deals in ranges even if you just want one cell
	//getValue returns an object, so convert it to a string
	var email = userData.getRange(row, 1).getValue().toString();
	//quick exit if the user isn't in the domain
	if (!checkUserIsValid(email)){
	Logger.log('[%s]\t Skipping user %s',Date(),email);
	return;
	}
	//Substitute in group variables, e.g those for groups of users
	//this must be done before filling out user specific data as it was added after initial design
	Logger.log('[%s]\t Substituting Group Variables for user %s',Date(),email);
	var signature = substituteGroupVariables(template, userData, groupData, row);
	//Fill out the template with the data from the user's row to form the signatures
	Logger.log('[%s]\t Substituting Individual Variables for user %s',Date(),email);
	signature = substituteVariablesFromRow(signature, userData, row);
	//The API docs say there is a 10,000 character limit
	//https://developers.google.com/google-apps/email-settings/#updating_a_signature
	if (signature.length > 10000) { Browser.msgBox('signature over 10000 characters for:' + email); }
	Logger.log('[%s]\t Sending signature for user %s',Date(),email);
	sendSignature(email, signature);
	Logger.log('[%s]\t Processing complete for user %s',Date(),email);
	}
	function substituteVariablesFromRow(text, sheet, row) {
	//Generating two lists avoids the need to do lots of individual calls to the sheet
	var tags = sheet.getSheetValues(1, 1, 1, sheet.getLastColumn())[0];
	var values = sheet.getSheetValues(row, 1, 1, sheet.getLastColumn())[0];
	for ( v = 0; v < values.length; v++){
	text = tagReplace(tags[v],values[v],text);
	}
	return text;
	}
	function substituteGroupVariables(text, dataSheet, lookupSheet, row) {
	//this function is still not great but at least it makes use of getSheet
	var tags = dataSheet.getSheetValues(1, 1, 1, dataSheet.getLastColumn())[0];
	var values = dataSheet.getSheetValues(row, 1, 1, dataSheet.getLastColumn())[0];
	var GroupVariables = lookupSheet.getSheetValues(1, 1, lookupSheet.getLastRow(),1);
	//for each GroupVariable
	for (j = 0; j < GroupVariables.length ; j += 3){
	//find the column for later changing the value
	for (i = 0; i < tags.length; i++){
	if (tags[i] === GroupVariables[j][0]){
	//and build a lookup table to switch it out
	var lookupTable = lookupSheet.getSheetValues(j+1,2,2,lookupSheet.getLastColumn()-1);
	for ( k=0;k<lookupTable[0].length;k++) {
	if (values[i] === lookupTable[0][k]){
	text = tagReplace(tags[i], lookupTable[1][k], text);
	}
	}
	}
	}
	}
	return text;
	}
	function sanitize(text){
	var invalid = ["[","^","$",".","|","?","*","+","(",")"];
	for(m=0;m<invalid.length;m++){
	text = text.replace(invalid[m],"\\"+invalid[m]);
	}
	return text;
	}
	function tagReplace(tag, value, text){
	var regOpen = sanitize(UserProperties.getProperty('regOpen'));
	var tagOpen = sanitize(UserProperties.getProperty('tagOpen'));
	var regClose = sanitize(UserProperties.getProperty('regClose'));
	var tagClose = sanitize(UserProperties.getProperty('tagClose'));
	var regex = new RegExp("(.*)"+regOpen+'(.*?)'+tagOpen+tag+tagClose+'(.*?)'+regClose+"(.*)","g");
	value = value.toString().replace("$","\\$");
	if ((value !== "")) { value = "$2"+value+"$3"; }
	value = "$1"+value+"$4";
	//I'm sure this can be avoided by making the regex more complicated, but this will do for now
	for(q=0; ((text.match(regex)) && q<128); q++ ){
	text = text.replace(regex,value);
	}
	return text;
	}
	function sendSignature(email, signature) {
	// https://developers.google.com/google-apps/email-settings/#updating_a_signature
	var requestData = {
	'method': 'PUT',
	'contentType': 'application/atom+xml',
	'payload': getPayload(signature)
	};
	var result = authorisedUrlFetch(email, requestData);
	if (result.getResponseCode() != 200) {
	var msg = 'There was an error sending ' + email + "'s signature to Google";
	Browser.msgBox('Error settings signature', msg, Browser.Buttons.OK);
	}
	}
	function checkUserIsValid(user){
	var userList = UserManager.getAllUsers();
	for ( u=0 ; u < userList.length ; u++ ) {
	if (userList[u].getEmail() === user){ return true; }
	}
	return false;
	}
	function getPayload(signature) {
	//First line is needed for XML, second isn't but we might as well do it for consistency
	signature = signature.replace(/&/g, '&amp;').replace(/</g, '&lt;');
	signature = signature.replace(/>/g, '&gt;').replace(/'/g, '&apos;').replace(/"/g, '&quot;');
	//Unfortunately when inside app script document.createElement doesn't work so lets just hardcode the XML for now
	var xml = '<?xml version="1.0" encoding="utf-8"?>' +
	'<atom:entry xmlns:atom="http://www.w3.org/2005/Atom" xmlns:apps="http://schemas.google.com/apps/2006" >' +
	'<apps:property name="signature" value="'+signature+'" /></atom:entry>';
	return xml;
	}
	function authorisedUrlFetch(email, requestData) {
	//takes request data and wraps oauth authentication around it before sending out the request
	// https://developers.google.com/apps-script/class_oauthconfig
	// http://support.google.com/a/bin/answer.py?hl=en&hlrm=en&answer=162105
	// https://developers.google.com/apps-script/articles/picasa_google_apis#section2a
	// The scope from https://developers.google.com/google-apps/email-settings/ has to be URIcomponent encoded
	var oAuthConfig = UrlFetchApp.addOAuthService('google');
	oAuthConfig.setConsumerSecret(UserProperties.getProperty('oAuthConsumerSecret'));
	oAuthConfig.setConsumerKey(UserProperties.getProperty('oAuthClientID'));
	oAuthConfig.setRequestTokenUrl('https://www.google.com/accounts/OAuthGetRequestToken?scope=https%3A%2F%2Fapps-apis.google.com%2Fa%2Ffeeds%2Femailsettings%2F');
	oAuthConfig.setAuthorizationUrl('https://www.google.com/accounts/OAuthAuthorizeToken');
	oAuthConfig.setAccessTokenUrl('https://www.google.com/accounts/OAuthGetAccessToken');
	UrlFetchApp.addOAuthService(oAuthConfig);
	requestData['oAuthServiceName'] = 'google';
	requestData['oAuthUseToken'] = 'always';
	var emailParts = email.split('@');
	var url = 'https://apps-apis.google.com/a/feeds/emailsettings/2.0/' + emailParts[1] + '/' + emailParts[0] + '/signature';
	var result = UrlFetchApp.fetch(url, requestData);
	if ( result.getResponseCode() != 200 ) {
	//Do some logging if something goes wrong
	//Too deep to give the user a meaningful error though so pass the result back up anyway
	Logger.log('Error on fetch on' + url);
	Logger.log(requestData);
	Logger.log(result.getResponseCode());
	Logger.log(result.getHeaders());
	Logger.log(result.getContentText());
	}
	return result;
	}
	function onOpen() {
	//add a toolbar and list the functions you want to call externally
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var menuEntries = [];
	menuEntries.push({name: 'Set All Signatures', functionName: 'setAllSignatures'});
	menuEntries.push({name: 'Set Individual Signature', functionName: 'setIndividualSignature'});
	menuEntries.push({name: 'Get Signature', functionName: 'getSignature'});
	ss.addMenu('Signatures', menuEntries);
	}
	function startupChecks() {
	//Check that everything that is needed to run is there
	//I don't check that any of it makes sense, just that it exists.
	var requiredProperties = [];
	//the help text looks pretty terrible but it is better than nothing
	var oAuthHelp = 'Goto https://code.google.com/apis/console#:access and register as an "Installed application" \n'+
	'Then add the ClientID to authorised 3rd party clients \n'+
	'With scope https://apps-apis.google.com/a/feeds/emailsettings/ \n'+
	'The script may then need authorising, this can be done by running one of the scripts from the script editor';
	requiredProperties.push({name: 'oAuthClientID', help: oAuthHelp});
	requiredProperties.push({name: 'oAuthConsumerSecret', help: oAuthHelp});
	requiredProperties.push({name: 'regOpen', help: 'A character or sequence to go before sections to be substituded, e.g ${'});
	requiredProperties.push({name: 'regClose', help: 'A character or sequence to go after sections that will be substituted, e.g } or }$'});
	requiredProperties.push({name: 'tagOpen', help: 'A character or sequence to go before tags to be substituded, e.g {'});
	requiredProperties.push({name: 'tagClose', help: 'A character or sequence to go after tags that will be substituted, e.g } or }$'});
	var requiredSheets = [];
	requiredSheets.push({name: 'Summary', help: 'A "Summary" sheet must exist that contains a 1 header row and 1 row per user, with no gaps in either the 1st column or row, the 1st row must be the users usernames'});
	requiredSheets.push({name: 'Signature Settings', help: 'A "Signature Settings" sheet must exist that contains a the template in cell 2A and then has 1 header row and 1 row per company wide variable, with no empty header cells'});
	requiredSheets.push({name: 'Signature Group Settings', help: 'A "Signature Group Settings" sheet must exist that contains 3 Rows (setting values, what to substitute, comments) with every third row containing a column header'});
	var fail = false;
	for ( s = 0; s < requiredProperties.length; s++) {
	var property = UserProperties.getProperty(requiredProperties[s].name);
	if (property == null) {
	var title = 'Script Property ' + requiredProperties[s].name + ' is required';
	var prompt = requiredProperties[s].help;
	var newValue = Browser.inputBox(title, prompt, Browser.Buttons.OK_CANCEL);
	if ((newValue === '') || (newValue === 'cancel')) {
	fail = true;
	} else {
	UserProperties.setProperty(requiredProperties[s].name, newValue);
	}
	}
	}
	for ( s = 0; s < requiredSheets.length; s++) {
	var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(requiredSheets[s].name);
	if (sheet == null) {
	fail = true;
	var title = 'Sheet ' + requiredSheets[s].name + ' is required';
	var prompt = requiredSheets[s].help;
	Browser.msgBox(title, prompt, Browser.Buttons.OK);
	}
	}
	return fail;
	}

view raw gistfile1.js hosted with ❤ by GitHub

Juan Canham System Engineer Cloudreach Limited