-
-
Save devluis/60d0ef940a9b42323e23 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, '&').replace(/</g, '<'); | |
signature = signature.replace(/>/g, '>').replace(/'/g, ''').replace(/"/g, '"'); | |
//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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment