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.
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:
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:
This Function creates a link to the Google Doc in the spreadsheet next to the relevant row from the Google Form submission:
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.
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 dataOnce 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:
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];
// open document fileThe final task is to then launch the createDocLink Function.
var file = DocumentApp.openByUrl(fileUrl);
// get document body
var docBody = file.getBody();
// replace tags in document with Form data ****************
docBody.replaceText('<<emailAddress>>', emailAddress);
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 folder3) createDocLink.gs
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();
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 linklogEvent.gs
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);
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