Tuesday, 19 May 2020

Create Google Doc from Form submission

The following Google Apps Script is designed to create a Google Doc for each submission of a Google Form and translate some of that Form data into the Doc. It puts a link to the newly created Google Doc back into the spreadsheet alongside the relevant row. The Name field from the Form is used as part of the filename for the created Doc and there is a Log sheet to support any troubleshooting errors.
Screenshot of spreadsheet with data and Doc link
Screenshot of spreadsheet with data and Doc link

1) getFormData.gs
This is the main Function that initially gets the data from the Google Form submission and the spreadsheet row the data is written to (so that we can later insert the hyperlink to the Google Doc in the relevant place). It also requires a project trigger to run the getFormData Function, of the event type - On form submit:
// 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];
Once the Function to create the Google Doc has ran and returned its Url we can open it, get the body of the document and replace our tags with data from the Google Form:
// open document file
var file = DocumentApp.openByUrl(fileUrl);

// get document body
var docBody = file.getBody();

// replace tags in document with Form data ****************
docBody.replaceText('<<emailAddress>>', emailAddress);
The final task is to then launch the createDocLink Function.

2) createDoc.gs
This Function creates a Google Doc for each Form submission and returns the new document Url back to the parent Function. It makes use of 'try/catch' in order to capture any errors that could occur with the process to output them into the spreadsheet. The template document (which is copied each time) and the destination folder (location to save the new Google Docs) have their Ids stored in the Config sheet so that they can be easily updated:
// get destination folder
var destinationFolder = DriveApp.getFolderById(destinationId);

// get Template Doc file
var templateDoc = DriveApp.getFileById(templateId);

// 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();
3) createDocLink.gs
This Function creates a link to the Google Doc in the spreadsheet next to the relevant row from the Google Form submission:
// 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
This Function is used to output log information should any errors occur. Further details can be found in this blog post for logging actions performed by a user.

Create Google Doc from Form submission download

No comments:

Post a Comment