email-sig.js

/*
 *  Copyright (c) 2012 Cloudreach Ltd
 *  Copyright (c) 2012 Juan Canham
 *  Copyright (c) 2013 Touchtype Ltd
 *  Copyright (c) 2013 Ian McDonald <ian.mcdonald@jandi.co.uk>
 *
 *  Google Apps admin tools - Email signature
 *
 *  This was orginally written by Juan Canham of Cloudreach and then
 *  added to by Ian McDonald of SwiftKey (brand of Touchtype).
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

var userDataSheetName = 'Summary';
var userSheetName = 'Users';
var theSheet = SpreadsheetApp.getActiveSpreadsheet();

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'});
  menuEntries.push({name: 'Get All Users', functionName: 'getAllUsers'});
  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;    
}

function getAllUsers() {

  if ( startupChecks()) { 
    return;
  }
  
  var userSheet = theSheet.getSheetByName(userSheetName);
  var userList = UserManager.getAllUsers();
  var row = [];
  var userDataSheet = theSheet.getSheetByName(userDataSheetName);
  var userData = userDataSheet.getSheetValues(1, 1, userDataSheet.getLastRow(), userDataSheet.getLastColumn());
  var column;
  var email_column = -1;
  
  if (userSheet != null) {
    theSheet.deleteSheet(userSheet);
  }
  
  userSheet = theSheet.insertSheet(userSheetName);
  
  for ( i = 0 ; i < userDataSheet.getLastColumn() ; i++ ) {
    column = userData[0][i];
    if (column === 'Email') {
        email_column = i;
        break;
    }
  }
    
  if (email_column === -1) {
    var msg = "No column callled Email in sheet "+userDataSheetName;
    Browser.msgBox('Error', msg, Browser.Buttons.OK);
    return;
  }
  
  row = [];
  row.push("Given Name");
  row.push("Family Name");
  row.push("Email");
  row.push("In "+userDataSheetName+" sheet");
  userSheet.appendRow(row);
  
  for ( u=0 ; u < userList.length ; u++ ) {
    row = [];
    row.push(userList[u].getGivenName());
    row.push(userList[u].getFamilyName());
    row.push(userList[u].getEmail());
    for (i=1; i < userData.length; i++) {
      var found = false;
      // this for loop is corect - we exclude title row and the length is one more than actual number
      if (userData[i][email_column] === userList[u].getEmail()) {
        found = true;
        break;
      }
      Logger.log(userData[i][email_column]+" "+userList[u].getEmail()+" "+found);
    }
    if (found) {
      row.push("Yes");
    } else {
      row.push("No");
    }
    userSheet.appendRow(row);
  }
}