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
docLinkCell.setFormula(hyperlink);
logEvent.gs
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

8 comments:

  1. Hi Phil
    Thanks.
    I have tried to replicate your system but I get some errors..:

    TypeError: Cannot read property 'namedValues' of undefined
    getFormData @ 1) getFormData.gs:8

    I am pretty new to this so its probably something simple that I need to do first.
    Will you guide me in the right direction?

    Best regards
    Simon

    ReplyDelete
    Replies
    1. Hi Simon

      Assuming you copied or downloaded the file directly from here and have not altered it, this error typically comes from when a person tries to run the code directly in the Script Editor, rather than via the Trigger from a Form submission.

      So make sure you have created your Trigger (https://developers.google.com/apps-script/guides/triggers/installable). After which the only way to run/test the code is to submit the Google Form each time.

      Kind regards
      Phil

      Delete

    2. I tried to do it over again. Copied the files. setup the trigger on getformdata to run on form submission and allowed the script to run on my google drive. So far so good.
      But the script still doesn't fire on formsubmission.
      I get this message in cloudlog:
      TypeError: Cannot read property '0' of undefined
      at getFormData(1) getFormData:14:41)

      I did try to google the error but its all Greek to me.. :-)
      So maybe I just need a simple adjustment or what do you think?

      Best regards
      Simon

      Delete
    3. Hi Simon

      Hhhmmmm, if you wanted to use the 'Contact Form' on the blog to share your Form/Google Sheet with me directly, I may be able to take a quick look for you.

      Kind regards
      Phil

      Delete
  2. Thanks for this tutorial and code. I have been trying to do this. I do keep getting this error message though. "Cannot read property '0' of undefined". Any advice?

    ReplyDelete
    Replies
    1. Hi

      Since creating this post a couple of years ago, Google have changed the naming used when capturing an email address from a Google Form. I have now updated the downloadable file and the GitHub code to account for this.

      So please download another copy and try again.

      Kind regards
      Phil

      Delete
  3. I found a way to resolve the error others have mentioned ("Cannot read properties of undefined (reading 'getRow')"):

    1. I copied the supplied project files into my Google Drive
    2. I linked the Form to the Spreadsheet using "Link To Sheets" > "Select Existing Spreadsheet".
    Even though there's already a responses sheet, doing this action creates a new sheet with the same columns.
    3. Delete any existing triggers that you have - I thought the one I'd created in the user interface was fine, but it wasn't.
    4. Create a trigger by copying-and-pasting the code below:

    function createTrigger() {
    var sheetId = 'enter-your-id-here';
    var sheet = SpreadsheetApp.openById(sheetId);
    ScriptApp.newTrigger("getFormData").forSpreadsheet(sheet).onFormSubmit().create();
    }

    There's probably a more elegant way to fix the issue, but at least it works :)

    Thanks Phil for sharing your script, it was just what I needed to get started on my project!

    ReplyDelete
    Replies
    1. Thank you Ben.

      Another check for people is to ensure they are not logged into more than one Google Account at the same time when setting it up - a common problem with people running Apps Script code.

      Delete