Last active
April 27, 2022 13:52
-
-
Save phillypb/37b798de58248266d9a0585cb1ad4f62 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 to get Google Form data. Needs onFormSubmit trigger. | |
*/ | |
function getFormData(e) { | |
// get Form data | |
var formValues = e.namedValues; | |
// get row Form data is written to in spreadsheet | |
var dataRow = e.range.getRow(); | |
// get specific values from Form ****************** | |
var emailAddress = formValues['Email address'][0]; | |
Logger.log('Email Address is: ' + emailAddress); | |
var name = formValues['Name'][0]; | |
Logger.log('Name is: ' + name); | |
var shoeSize = formValues['Shoe size'][0]; | |
Logger.log('Shoe size is: ' + shoeSize); | |
// get specific values from Form ****************** | |
// run Function to create Google Doc and return its Url | |
var fileUrl = createDoc(name); | |
try { | |
// open document file | |
var file = DocumentApp.openByUrl(fileUrl); | |
} | |
catch(e) { | |
logEvent('Error opening new document file: ' + e + e.stack); | |
var file = false; | |
} | |
// proceed if successfully open document file | |
if (file) { | |
// get document body | |
var docBody = file.getBody(); | |
// replace tags in document with Form data **************** | |
docBody.replaceText('<<emailAddress>>', emailAddress); | |
docBody.replaceText('<<name>>', name); | |
docBody.replaceText('<<shoeSize>>', shoeSize); | |
// replace tags in document with Form data **************** | |
// run Function to create link to Google Doc in spreadsheet | |
createDocLink(name, dataRow, fileUrl); | |
} | |
else { | |
// do nothing as unable to open new document file | |
} | |
} | |
/* | |
Function to create Google Doc and return its Url. | |
*/ | |
function createDoc(name) { | |
// get spreadsheet | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
// get 'Config' sheet | |
var configSheet = ss.getSheetByName('Config'); | |
try { | |
// get Google folder destination Id | |
var destinationId = configSheet.getRange(2, 2).getValue(); | |
Logger.log('Destination folder Id is: ' + destinationId); | |
// get destination folder | |
var destinationFolder = DriveApp.getFolderById(destinationId); | |
} | |
catch(e) { | |
logEvent('Error getting destination folder: ' + e + e.stack); | |
var destinationFolder = false; | |
} | |
// proceed if successfully got destination folder | |
if (destinationFolder) { | |
try { | |
// get Template Doc Id | |
var templateId = configSheet.getRange(1, 2).getValue(); | |
Logger.log('Template Doc Id is: ' + templateId); | |
// get Template Doc file | |
var templateDoc = DriveApp.getFileById(templateId); | |
} | |
catch(e) { | |
logEvent('Error getting Template Doc file: ' + e + e.stack); | |
var templateDoc = false; | |
} | |
// proceed if successfully got Template Doc file | |
if (templateDoc) { | |
// make copy of Template Doc file with new name, into destination Folder | |
var newFile = templateDoc.makeCopy(name, destinationFolder); | |
// get Url of new file | |
var newFileUrl = newFile.getUrl(); | |
// return Url to Parent Function | |
return newFileUrl; | |
} | |
else { | |
// do nothing as unable to get destination folder | |
} | |
} | |
else { | |
// do nothing as unable to get destination folder | |
} | |
} | |
/* | |
Function to create link to Google Doc in spreadsheet next to relevant row. | |
*/ | |
function createDocLink(name, dataRow, fileUrl) { | |
// get spreadsheet | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
// get 'response' sheet | |
var responseSheet = ss.getSheetByName('Form responses 1'); | |
// create name for Doc link | |
var linkName = 'Google Doc for ' + name; | |
// get relevant cell to insert link to Doc in | |
var docLinkCell = responseSheet.getRange(dataRow, 5); | |
// create hyperlink | |
var hyperlink = '=HYPERLINK("' + fileUrl + '","' + linkName + '")'; | |
// set hyperlink in spreadsheet | |
docLinkCell.setFormula(hyperlink); | |
} | |
/* | |
Function to be used for outputting log info to a Sheet. | |
*/ | |
function logEvent(action) { | |
// get the user running the script | |
var theUser = Session.getActiveUser().getEmail(); | |
// get the relevant spreadsheet to output log details | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var logSheet = ss.getSheetByName('Log'); | |
// create and format a timestamp | |
var dateTime = new Date(); | |
var timeZone = ss.getSpreadsheetTimeZone(); | |
var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ HH:mm:ss"); | |
// create array of data for pasting into log sheet | |
var logData = [niceDateTime, theUser, action]; | |
// append details into next row of log sheet | |
logSheet.appendRow(logData); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment