Auto Fill a Google Doc Template from Google Sheet Data

In this post, I’ll walk you through how to auto fill Google Doc templates with data pulled from a Google Spreadsheet using Google Apps Script. This tutorial is a follow-up of sort to Auto Fill a Google Doc from Google Form Submissions, so if what you want to do involves a form, that would be worth checking out as well.

To make this a helpful starting point for other tools, we will make this automation run by adding a custom menu option to Google Sheets and save the URL to the document we create back to the spreadsheet.

Prepare The Sheet

For this tutorial, our starting point will contain some data on employees, like their first and last name, hire date, and hourly wage. I’ve also created a blank column named ‘Document Link’ at the end of the data range to store a link to the Google Doc that our script will create.

Click here to make a copy of the Sheet and Script project >>>

a screenshot of employee data in a google spreadsheet

By storing the document link in the spreadsheet, we get a handy way  to access the created document, but we can also use that data field to help us control which documents get created when we run the code from the add-on menu options. In this tutorial, we will only create new documents when a spreadsheet row doesn’t already have a URL in the ‘Document Link’ column.

Creating a Google Doc Template

The best part about populating a Google Doc template is that you can create fairly sophisticated documents and merge data into the copies that you make. In most cases, you’ll find this a lot easier than working with the DocumentApp class in Google Apps Script to create nice looking things.

In this example, we’ll create some variable replacement tokens by surrounding the variables we want to replace with two sets of curly braces: {{First Name}}

The key here is that whatever text you use in the Google Doc will need to be unique to the document, and it needs to match exactly to a string we use in our code, including whitespace, capitalization, etc.

An employee details grid in Google Docs

This document template will get copied each time our script runs, and then we will use the DocumentApp to search for our replacement tokens in the document body, and then we will replace those tokens with values from our sheet.

Writing the Code in Google Apps Script

There are two main pieces to our script for this tutorial: the function that adds a menu item to our sheet so that we can easily run our script, and the function that will process our spreadsheet data and autofill the document templates. This code needs to be added to the script editor accessible via the Tools > Script Editor menu.

We can look at the function to create a menu item first:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs')
  menu.addToUi();

}

I’ve written another, more extensive article on creating menu items in Google Sheets, so check that out if you have any questions or want to learn more about the different menu types.

When we add a menu item, we attach the ‘Create New Docs’ option to a function called createNewGoogleDocs that we’ll look at in more detail. I’ve included the whole function below with a descriptive comment for pretty much every line, but there are a few things I’ll break out into more specific examples as well.

function createNewGoogleDocs() {
  //This value should be the id of your document template that we created in the last step
  const googleDocTemplate = DriveApp.getFileById('1RhLb3aKf-C-wm5lfSBe2Zj43U1P0P2av1_kzekbeCP4');
  
  //This value should be the id of the folder where you want your completed documents stored
  const destinationFolder = DriveApp.getFolderById('1VCnjlXCBGOxEvHUsRmogllZ41Snu4RN1')
  //Here we store the sheet as a variable
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Data')
  
  //Now we get all of the values as a 2D array
  const rows = sheet.getDataRange().getValues();
  
  //Start processing each spreadsheet row
  rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    //Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
    if (row[5]) return;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Employee Details` , destinationFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    //In this line we do some friendly date formatting, that may or may not work for you locale
    const friendlyDate = new Date(row[3]).toLocaleDateString();
    
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText('{{First Name}}', row[0]);
    body.replaceText('{{Last Name}}', row[1]);
    body.replaceText('{{Position}}', row[2]);
    body.replaceText('{{Hire Date}}', friendlyDate);
    body.replaceText('{{Hourly Wage}}', row[4]);
    
    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    //Store the url of our new document in a variable
    const url = doc.getUrl();
    //Write that value back to the 'Document Link' column in the spreadsheet. 
    sheet.getRange(index + 1, 6).setValue(url)
    
  })
  
}

One of the many questions I get asked is about using conditional logic, meaning we’ll look at a row in a spreadsheet and use that value to decide whether or not a new document is created, or perhaps which template is used is we had different document templates for different employee types.

Conditional Logic in forEach Loop

To check out how this works, let’s look at the first few conditional logic checks in our forEach loop:

//Start processing each spreadsheet row 
rows.forEach(function(row, index){ 

  //Here we check if this row is the headers, if so we skip it 
  if (index === 0) return; 
  //Here we check if a document already exists by looking at 'Document Link', 
  //if so we skip it 
  if (row[5]) return;

Using the forEach method on our row of spreadsheet data, which is modeled in a JavaScript array, we pass in both the value of the row itself, which is also an array, as well as the index of the current row.

In the next line, the index === 0 check looks at whether or not this is the header row. If it is the header row, then we return the function of our loop for that item so that no other code is processed.

In the following line, we do something similar with the item at the fifth array index (sixth column in the Google Sheet), meaning in this example we look at whether or not the ‘Document Link’ column has a value. If it does, we return the function for this loop item so we skip this code.

However, you could also use this pattern to check other things about the data in the current row and introduce branching logic based on what data you find. For example, you could store a different value for the googleDocTemplate variable based on whether the employee was a ‘Web Developer’ or ‘CEO.’

Creating Clean Strings with Template Literals

Another line worth mentioning is where I use a template literal to construct the string name of our new document:

const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Employee Details` , destinationFolder)

Template literals are a newer construct in Google Apps Script that allow us to create cleaner strings than we would be able to create using string concatenation. Instead of using either single '' or double "" quotes to create a string, a template literal uses back-ticks `` to create a string.

How you assemble strings in this way is also quite different. With template literals, we interpolate variables into strings using place holders, while  string concatenation involves adding smaller strings into one longer string.

You can see the comparison below for how those practices would work on the example above:

const templateLiteralExample = `${row[1]}, ${row[0]} Employee Details`
const stringConcatExample = row[1] + " " + row[0] + " Employee Details"

Either example above evaluates to the same string, so you can choose based on preference, and often I’ll switch between the two techniques in the same project because of the context. I tend to use template literals when I want a string with whitespace, as you can see doing that in the second example can get annoying.

If you are interested in learning more about the modern features of JavaScript available in Google Apps Script, you can read my article on using template literals.

Generating the Documents

Once we have saved the code, you can generate the documents from the Spreadsheet using the AutoFill Docs -> Create New Docs menu item. If you ever want to regenerate a document, all you need to do is remove the value in the ‘Document Link’ column.

a screenshot of someone using a google add one menu item

 

Frequently Asked Questions

This is a space where I’ll surface relevant FAQs from the comments section.

Displaying Formatted Values from the Spreadsheet

I’ve had a number of people ask about how to display formatted values from the spreadsheet (e.g. currency) or values derived from a formula. The easiest way to do this is to use the getDisplayValues method instead of getValues in the following line of the script:

const rows = sheet.getDataRange().getValues();

The methods are essentially the same except one returns the underlying value, and the other returns the values along with any display transformations the spreadsheet is doing. You can find more info about the getDisplayValues method on the official docs.

 

90 thoughts on “Auto Fill a Google Doc Template from Google Sheet Data”

  1. Jennifer Boyle says:

    This was very easy to follow on YouTube – thank you for this information. I do have a question about something though…I have several date columns on my spreadsheet. Do I list them in this way:
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[2]} Invoices`, destinationFolder);
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();
    const friendlyDate = new Date(row[1]).toLocaleDateString();
    const friendlyDate = new Date(row[6]).toLocaleDateString();
    const friendlyDate = new Date(row[9]).toLocaleDateString();
    const friendlyDate = new Date(row[12]).toLocaleDateString();
    const friendlyDate = new Date(row[15]).toLocaleDateString();

    1. BrownBearWhatDoYouSee says:

      Hi, Thanks for reading/watching. You should create a unique variable name for each date you pull from the spreadsheet, like so:
      const eventStartDate = new Date(row[1]).toLocaleDateString();
      const eventEndDate = new Date(row[6]).toLocaleDateString();
      const makeupDate = new Date(row[9]).toLocaleDateString();

      Otherwise, you will get an error for trying to redeclare a constant variable.

      Regards,
      Jeff

  2. Kaki Whiddon says:

    I really appreciated your video. I am extremely new to this, but I am trying to make our life a little easier at school. I keep getting error messages when I try to run the script. Can you let me know what I’m missing?

    function createNewGoogleDocs() {

    const googleDocTemplate = DriveApp .getFolderById(‘1cMMnT-ulKJZxJWKdoa_ihIij3o2W6Plt8KoRH67g0HA’);
    const destinationFolder = DriveApp .getFolderById(‘1Q6zi1G5yA72ZFyDnd_-PsDsqhZyGhcr3’);
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘PFI’)
    const rows = sheet .getDataRange() .getValues();

    rows.forEach(function (row, index) {
    if(index===0) return;
    if(row[20]) return;
    const copy = googleDocTemplate //makecopy(`${row[2]}, ${row[3]} PFI`, destinationFolder)
    const doc = DocumentApp .openById(copy.getID())
    const body = doc.getBody();
    const friendlyDate = new Date(row[7]) .toLocaleDateString();

    body.replaceText(‘{{FirstName}}’, row [2]);
    body.replaceText(‘{{LastName}}’, row [3]);
    body.replaceText(‘{{TeacherName}}’, row [5]);
    body.replaceText(‘{{Grade}}’, row [4]);
    body.replaceText(‘{{Location}}’, row [6]);
    body.replaceText(‘{{NineWeeks}}’, row [9]);
    body.replaceText(‘{{Date}}’, friendlyDate);
    body.replaceText(‘{{Time}}’, row [8]);
    body.replaceText(‘{{MinorBehavior}}’, row [11]);
    body.replaceText(‘{{Step1}}’, row [13]);
    body.replaceText(‘{{Step2}}’, row [14]);
    body.replaceText(‘{{Number}}’, row [16]);
    body.replaceText(‘{{NextConsequence}}’, row [18]);

    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.setRange(index + 1, 20).setValue(url)

    })

    }

    1. BrownBearWhatDoYouSee says:

      What error messages are you getting?

  3. bruno says:

    Hello thanks for the tutorial
    Im getting this error
    exception unexpected error while getting the method or property getfolderbyid on object driveapp

    1. BrownBearWhatDoYouSee says:

      I would check first the id of the folder to make sure it matches, then make sure you have sufficient permission to access the folder. From there, if that doesn’t work, you can post the code you have written so we can take a look at that. Thanks for watching, Jeff

  4. Hussein Al-Lami says:

    Kaki
    I am not sure if it is intentional or not, but you have an error in your code.
    const copy = googleDocTemplate //makecopy(`${row[2]}, ${row[3]} PFI`, destinationFolder)

    the // is commenting the rest of the line, I guess it’s a typo but it would generate an error because that function has to be a one continues line.
    googleDocTemplate.makecopy(`${row[2]}, ${row[3]} PFI`, destinationFolder)

    Jeff thank you for the video I have learnt a lot you are a life saver.
    I have been trying to do this for a long time using exel macro but excel refuses to cooperate.

    1. BrownBearWhatDoYouSee says:

      Thanks for letting me know, but I can’t see those two slash marks inside of the statement on line 3. Did that happen when you copied and pasted? Thanks for the kind words and the heads up that something may not be working right.

      Regards,
      Jeff

  5. Jeremy says:

    Hi Jeff,

    Great write up with clear descriptions. Thank you!
    When looking at the destination for the new files, is there a resource you can point me to that explains how to 1) create a folder from the generated file name and 2) move those matching files into that folder?

    In researching this, I thought this code would work when moved after the spreadsheet was opened:

    //data from the two row fields would be used to make the folder name

    const destinationFolder = DriveApp.getFolderById(‘${row[1]} ${row[2]}’)

    If you’d be so kind, am I on the right track? Is there a resource that would be straightforward like your site?

    Thank you!

    1. BrownBearWhatDoYouSee says:

      Yeah, that should be a one line solution like the below. You just need to use the createFolder method on the Drive folder we have already initialed, and then specify the new folder as the home for the files we create later: https://developers.google.com/apps-script/reference/drive/folder#createFolder(String)

      const destinationFolder = DriveApp.getFolderById('ID_GOES_HERE')
      const newFolder = destinationFolder.createFolder(‘${row[1]} ${row[2]}’);

      //Later on, use the newFolder instead of the destination folder as the home for the copy
      const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Employee Details` , newFolder)

      Feel free to post back with any additional questions or comments. Thanks for reading! Cheers, Jeff.

  6. Filipe says:

    This is absolutely fantastic! Thanks for the tutorial. Would it be possible for this script to go a little further and generate exported PDFs of the documents created and after doing so erase the created google docs and keep only the pdfs?

    1. BrownBearWhatDoYouSee says:

      Yes, all of that is very much possible. After saving the doc, you would use the getAs(‘application/pdf’) method on the Drive representation of the file, then save that pdf using createFile method of the folder you want to save it in, then finally call the setTrashed method on the original file you want to delete:
      https://developers.google.com/apps-script/reference/drive/file#getascontenttype
      https://developers.google.com/apps-script/reference/drive/folder#createfilename,-content,-mimetype
      https://developers.google.com/apps-script/reference/drive/file#settrashedtrashed

      Thanks for watching and post back if you need some additional guidance to implement this.
      Cheers,
      Jeff

  7. Nathalie says:

    How do I show a column with an amount with a dollar sign and comma? for example, &66,666.67

    1. BrownBearWhatDoYouSee says:

      Hi, Thanks for reading! You should be able to use the getDisplayValues method to get the formatted cell values: https://developers.google.com/apps-script/reference/spreadsheet/range#getDisplayValues()

  8. Colby S. says:

    Hello, I am not sure where I am going wrong…when trying to run the “Create New Docs” I get the following error:

    TypeError: googleDocTemplate.makeCopy is not a function

    Any idea why?

    1. BrownBearWhatDoYouSee says:

      Typically that happens when the script does not successfully get the template file by id, so I’d check there. If you want to post the code also, that typically helps me give more specific feedback.

      Regards,
      Jeff

  9. Jeremy says:

    Thank you, Jeff!
    I’m still struggling a bit with understanding your explanation, but that is my fault, I think.

    When I use the code you provided above:
    const newFolder = destinationFolder.createFolder(‘${row[1]} ${row[2]}’);

    it throws a SyntaxError: Invalid or unexpected token. (That might have been a simple error on my part going from website to code window, too.) I tried the tick marks [ ` ] instead of [ ‘ ’], but when I try to run the script from the spreadsheet, I then get a different error:

    ReferenceError: row is not defined

    This is probably because the spreadsheet isn’t loaded at this point in the code, correct?

    Thank you!

  10. Rick says:

    Awesome instructions Jeff – Thanks.
    Can you advise on what to change to create labels in the same single document? I know the document creation and saving need to be outside the forEach, but how do I define the repeat blocks of data i.e. Name?
    Thanks in advance.

    1. BrownBearWhatDoYouSee says:

      Hey Rick,
      Thanks for watching and happy to help, but I’m not sure what you are asking. Any chance you could ask the question a different way, or post some example code with an indication of where you are struggling.

      Regards,
      Jeff

  11. Fidel says:

    Greetings Jeff,
    How would one keep on appending the records to one long google doc. Its just that I need to print the resulting docs and they are so many. I figure it would be easier to just make one long doc…with the next page featuring the next record.

    1. BrownBearWhatDoYouSee says:

      I did something like that here, but it is a very simple example: https://jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/

      It would obviously be more complex if you needed a more complex “record” for each row.

      Regards,
      Jeff

  12. McKay Jarman says:

    Hey! This was a great video and super helpful. I have been getting an error when I try to run this. It says “Exception: You do not have permission to call DocumentApp.openById. Required permissions: https://www.googleapis.com/auth/documents (line 21 file “Create File”).

    Any ideas? Here is my code for the Create New Google Docs Function with the ID’s removed.

    function createNewGoogleDocs() {

    const googleDocTemplate = DriveApp.getFileById(”);
    const destinationFolder = DriveApp.getFolderById(”);
    const sheet = SpreadsheetApp.getActiveSpreadsheet() .getSheetByName(‘Form Responses 4’);
    const rows = sheet.getDataRange().getValues();

    rows.forEach(function(row, index) {
    if (index === 0) return;
    if (row[31]) return;

    const copy = googleDocTemplate.makeCopy(`${row[3]}, ${row[2]} Diaper Program File`, destinationFolder);
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();

    body.replaceText(‘{{First Name}}’, row[2]);
    body.replaceText(‘{{Last Name}}’, row[1]);

    doc.saveAndClose();
    const url = doc.getUrl()
    sheet.getRange(index+1, 32).setValue(url)

    })
    }

    1. BrownBearWhatDoYouSee says:

      Did you remove the ids for the file and folder before posting the comment? If not, that would be the first place I would check and make sure they match exactly. Then, if you are sure those match, make sure the owner gives you access to the document as an editor. Lastly it could be an issue with permissions at the root of your Google Workspaces domain. For this, if you work in an enterprise environment, the IT dept. may have disabled certain features of Google Apps Script or add-ons more generally. Let me know how it goes. Thanks for reading.

  13. Damir Dunderovic says:

    This is going to save me quite some time for contracts and templates. Thank you so much.

    Needed to fix date format, found out this worked:
    const friendlysignDate = Utilities.formatDate(new Date(row[5]),’GMT+1′,’dd.MM.yyyy’);

    Then needed to change number format (because of currencies)… only viable option I found was using Utilities.formatString. Some geniuses here would probably make that work, I almost had the correct formatting I needed using ‘%’-style format strings. This was useful: https://stackoverflow.com/questions/24622154/format-string-as-currency-for-google-docs

    Suddenly I came across the simplest solution ever… and after all that, I saw the reply in the comment section suggesting replacing getValues() with getDisplayValues(). That did the trick as I can now use Sheets functionality to decide correct formatting.

    You should consider adding this as a comment to the script.

    1. BrownBearWhatDoYouSee says:

      Hey Damir,
      Thanks so much for adding this detail to the comments, and you are right, this should go in the initial post. I just updated it with an FAQ section to capture things like this so people don’t have to try so many things themselves. Also good idea to bring in the Utilities class to do the date formatting. Depending on what locale you are in, that will make things a lot easier. I often forget that class exists. Thanks again for reading and writing up your suggestions!
      Regards,
      Jeff

  14. Alex J Johnson-Jimenez says:

    Thank you so much for sharing your skills with us. This code, with some modifications to suit our needs, just saved my teacher colleagues HOURS of paperwork! You have no idea how grateful we are.

    1. BrownBearWhatDoYouSee says:

      Hi Alex,
      Thanks so much for leaving the kind words about my work. For someone who started writing random stuff on the internet to help people, it always makes my day to know that I could help some teachers save some valuable time. Thanks for reading and reaching out, and be sure to check back because there are a lot of other time saving avenues for this type of automation.

      Regards,
      Jeff

  15. Cat Bagsic says:

    I’m getting this error:
    TypeError: Cannot read property ‘getDataRange’ of null (line 14, file “Code”)

    I exactly typed in:
    const rows = sheet.getDataRange().getValues();

    1. BrownBearWhatDoYouSee says:

      This error suggests there was a problem in the previous line when getting the active spreadsheet and then a particular sheet by name. I would double check this line of code to make sure that you used the correct name:
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data')

      Post back if you are still having issues. Thanks for reading, Jeff

  16. Arhenis Camal says:

    Hey i would like to know if its possible to work this code with multiple spread sheets at the same time?

    1. BrownBearWhatDoYouSee says:

      Yes, that would be technically possible. You would just need to open the other sheets using the SpreadsheetApp class to pull in data from the other spreadsheets.

      Thanks for reading, Jeff

  17. Whit says:

    Hi Jeff, thanks for the great resource!
    I’m running into trouble because my spreadsheet has data validation in some columns to provide drop-downs with multiple options, and the script is reading those cells as not empty. The first time I tried to run it, it tried to create hundreds of blank copies of the template in my destination folder. Is there a workaround to get it to ignore those cells and only generate docs for the rows with “real” data?

    1. BrownBearWhatDoYouSee says:

      That’s a great question, and I think you might be the first person to big this issue to my attention.

      Instead of using the getDataRange method, which gets a range that contains the maximum extent of your data, you could try using the getRange method, which allows you to select a more specific range. You can still call getValues or getDisplayValues and everything will work the same, but using this you can define the range that contains your “real” data.

      Here is a link to the method I mentioned: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column,-numrows. Post back if you have more questions about how to implement that.

      Regards,
      Jeff

  18. Whit says:

    Thanks Jeff! I’m giving it a whirl, but I’m not sure that’ll work for my purposes long-term because I anticipate the spreadsheet being pretty heavy traffic and changing fairly often. For context: the sheet in question is an issue submission log for a data quality assurance team, and what I want it to do is create a filled-in copy of our issue triage/investigation record whenever new issues are submitted. I thought about importing a text-only version of the log to another sheet in the workbook and having the script look at that instead, but then I wouldn’t get the nifty links to the docs in the column I wanted! (unless there’s a way to have the results go to a different sheet…? I’m still pretty new to Google App Script)

    1. BrownBearWhatDoYouSee says:

      That makes sense. What I suggested wouldn’t be the best way to do this with a growing sheet, but if people are submitting a form to add to the sheet, you might consider using the form submission as a trigger to create a document from just the submission as done here: https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/

      Then you could write the link back to the row with a matching timestamp. Feel free to post back if you want to toss some other ideas around, but if people are submitting a form (or could submit a form), and you are making a doc for each record, using the form submission trigger is the way to go IMHO.

      Thanks for reading,
      Jeff

  19. Jeremy says:

    Reposting this follow-up question, since I think it got lost in the shuffle:

    I’m still struggling a bit with understanding your explanation, but that is my fault, I think.

    When I use the code you provided above:
    const newFolder = destinationFolder.createFolder(‘${row[1]} ${row[2]}’);

    it throws a SyntaxError: Invalid or unexpected token. (That might have been a simple error on my part going from website to code window, too.) I tried the tick marks [ ` ] instead of [ ‘ ’], but when I try to run the script from the spreadsheet, I then get a different error:

    ReferenceError: row is not defined

    This happens at the line containing the code above:
    const newFolder = destinationFolder.createFolder(‘${row[1]} ${row[2]}’);

    This is probably because the spreadsheet isn’t loaded at this point in the code, correct? How can this be fixed? I’ve been trying to change the order of operations, but haven’t had any luck.

    Thank you!

    1. BrownBearWhatDoYouSee says:

      Thanks for posting back. Sometimes these comments get a bit too much for me to manage 🙂

      Can you post the whole script you are using? Does the SyntaxError point specifically to the line where we create the newFolder variable?

      One quick thing to check would be to see if your script is using the V8 runtime. It should by default, but it is also easy to disable, and the line in question uses syntax that would not be recognized by the old runtime. Make sure the V8 runtime is active and then post your code if enabling it doesn’t solve the issue: https://developers.google.com/apps-script/guides/v8-runtime#legacy-editor_2

      Best of luck,
      Jeff

  20. Francesco says:

    Thanks for this! Is it possible to show a different value based on IF? Basically, if the {{GMN}} variable is empty, then put another variable.
    Thanks!

    1. BrownBearWhatDoYouSee says:

      Yes, with something like this (which assumes the variable name is GMN):

      if (!GMN) {
      GMN = 'Your new value';
      }

      Regards,
      Jeff

  21. Jeremy says:

    No worries, Jeff! Regardless of whether you’re able to help, I still appreciate all the info I’ve learned.

    Yes, the error happens one the line we create the newFolder variable. And yes, I’m running V8 (always a good idea to check).

    Code:

    function onOpen() {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu(‘Autofill’);
    menu.addItem(‘Create New Doc’, ‘createNewGoogleDocs’)
    menu.addToUi();
    }

    function createNewGoogleDocs() {
    //This value should be the id of your document template that we created in the last step
    const googleDocTemplate = DriveApp.getFileById(‘myLocationofTemplate’);

    //This value should be the id of the folder where you want your completed documents stored
    const destinationFolder = DriveApp.getFolderById(‘myLocationForDocStorage’)
    const newFolder = destinationFolder.createFolder(`${row[1]}, ${row[2]}`);
    //Here we store the sheet as a variable
    const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(‘sheetWithData’)

    //Now we get all of the values as a 2D array
    const rows = sheet.getDataRange().getValues();
    //Start processing each spreadsheet row
    rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    //Here we check if a document has already been generated by looking at ‘Document Link’, if so we skip it. Row 16 is the link location
    if (row[0] === “”) return;
    if (row[16]) return;
    //Using the row data in a template literal, we make a copy of our template document in our newFolder, formerly destinationFolder
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[2]}` , newFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    //In this line we do some friendly date formatting, that may or may not work for you locale
    const friendlyDate1 = new Date(row[29]).toLocaleDateString();
    const friendlyDate2 = new Date(row[30]).toLocaleDateString();

    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText(‘{{FirstName}}’, row[2]);
    body.replaceText(‘{{LastName}}’, row[1]);
    body.replaceText(‘{{today}}’, friendlyDate1);
    body.replaceText(‘{{expDate}}’,friendlyDate2);

    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    //Store the url of our new document in a variable
    const url = doc.getUrl();
    //Write that value back to the ‘Document Link’ column in the spreadsheet.
    sheet.getRange(index + 1, 17).setValue(url)
    //create a PDF from the new file
    var pdffolder = DriveApp.getFolderById(“137NpXOgkgnupF8NoSrdUh9uRDvVJZZ-9”);
    var pdfFILE = DriveApp.getFileById(doc.getId()).getAs(‘application/pdf’);
    pdfFILE.setName(doc.getName() + “.pdf”);
    var theFolder = pdffolder;
    var theFile = DriveApp.createFile(pdfFILE);
    theFolder.addFile(theFile);

    })

    }

    1. BrownBearWhatDoYouSee says:

      Ahhh..now I see the issue with the line for creating a new folder. That needs to happen inside of the forEach loop, otherwise it doesn’t have it doesn’t have access to the ‘row’ variable you use to create the name. The only other thing I noted was that you could also call createFile directly on the pdffolder variable to keep things a little bit more precise: https://developers.google.com/apps-script/reference/drive/folder#createFile(String,String)

  22. Jeremy says:

    No worries, Jeff! Regardless of whether you’re able to help, I still appreciate all the info I’ve learned.

    Yes, the error happens one the line we create the newFolder variable. And yes, I’m running V8 (always a good idea to check).

    Code:

    function onOpen() {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu(‘Autofill’);
    menu.addItem(‘Create New Doc’, ‘createNewGoogleDocs’)
    menu.addToUi();
    }

    function createNewGoogleDocs() {
    //This value should be the id of your document template that we created in the last step
    const googleDocTemplate = DriveApp.getFileById(‘myLocationofTemplate’);

    //This value should be the id of the folder where you want your completed documents stored
    const destinationFolder = DriveApp.getFolderById(‘myLocationForDocStorage’)
    const newFolder = destinationFolder.createFolder(`${row[1]}, ${row[2]}`);
    //Here we store the sheet as a variable
    const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(‘sheetWithData’)

    //Now we get all of the values as a 2D array
    const rows = sheet.getDataRange().getValues();
    //Start processing each spreadsheet row
    rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    //Here we check if a document has already been generated by looking at ‘Document Link’, if so we skip it. Row 16 is the link location
    if (row[0] === “”) return;
    if (row[16]) return;
    //Using the row data in a template literal, we make a copy of our template document in our newFolder, formerly destinationFolder
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[2]}` , newFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    //In this line we do some friendly date formatting, that may or may not work for you locale
    const friendlyDate1 = new Date(row[29]).toLocaleDateString();
    const friendlyDate2 = new Date(row[30]).toLocaleDateString();

    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText(‘{{FirstName}}’, row[2]);
    body.replaceText(‘{{LastName}}’, row[1]);
    body.replaceText(‘{{today}}’, friendlyDate1);
    body.replaceText(‘{{expDate}}’,friendlyDate2);

    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    //Store the url of our new document in a variable
    const url = doc.getUrl();
    //Write that value back to the ‘Document Link’ column in the spreadsheet.
    sheet.getRange(index + 1, 17).setValue(url)
    //create a PDF from the new file
    var pdffolder = DriveApp.getFolderById(“myLocationForDocStorage”);
    var pdfFILE = DriveApp.getFileById(doc.getId()).getAs(‘application/pdf’);
    pdfFILE.setName(doc.getName() + “.pdf”);
    var theFolder = pdffolder;
    var theFile = DriveApp.createFile(pdfFILE);
    theFolder.addFile(theFile);

    })

    }

  23. Jeremy says:

    BIG THANK YOU Jeff!

    (please feel free to delete my duplicate comment that was just submitted)

    I’ll look back at your responses to other comments to see if I can figure out how to get the newly created PDF to also go to this new folder, but the hard part is done. Thank you again!

  24. Jessi says:

    Hello,
    Thanks for this great tutorial! I feel like I almost have it but keep getting this error: “TypeError: Cannot read property ‘getDataRange’ of null”
    Any idea of what I am doing wrong?
    function onOpen() {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu(‘AutoFill Docs’);
    menu.addItem(‘Create New Docs’, ‘createNewGoogleDocs’)
    menu.addToUi();
    }

    function createNewGoogleDocs() {
    const googleDocTemplate = DriveApp.getFileById(’15RA6J3nRuVp4PbhFVdWQJrPv77Zk8FnIO59Lvh-wKtM’);
    const destinationFolder = DriveApp.getFolderById(‘1968FcWfXwiAuOTdRW7b_ou0V08vIAvtn’);
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘ Donations2020 ‘);
    const rows = sheet.getDataRange ().getValues();
    rows.forEach(function(row, index){
    if (index === 0) return;
    if (row[12]) return;
    const copy = googleDocTemplate.makeCopy(`${row[0]}, ${row[0]} Donations2020 ` , destinationFolder)
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();
    const friendlyDate = new Date(row[0]).toLocaleDateString();
    body.replaceText(‘{{Name}}’, row[1]);
    body.replaceText(‘{{Address}}’, row[4]);
    body.replaceText(‘{{Donation amount}}’, row[5]);
    body.replaceText(‘{{Date}}’, friendlyDate);
    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(index + 1, 12).setValue(url)

    })

    }

    1. BrownBearWhatDoYouSee says:

      I would check the details of your spreadsheet where you create the sheet variable. This error indicates that there was something wrong with getting the spreadsheet.

  25. Amal says:

    Thanks a lot Jeff…you are really a life saver
    Can I put all the records in the same Doc file.
    And can I put more than one record in one page.
    For example 4 records in first A4 page and the next 4 records in the second A4 page ,and so on….
    And then convert and save the whole doc to pdf file

    1. BrownBearWhatDoYouSee says:

      Hi Amal,

      Sorry for the late reply. Here are a few links to other tutorials that might help with putting all of the records in one doc and getting it as a PDF:
      https://jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/
      https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/

      On the last one, look at the first few comments for an explanation of the PDF

  26. Igor says:

    Hi Jeff,
    I was able to create my own project following your tutorial, thank you!
    What I would like to do is: IF a row is empty, delete corresponding placeholder in the created document. Let’s say Hourly Wage field is empty, then delete {{ Hourly Wage}} from the document before saving it. I presume there should be some sort of loop. I wonder if it is possible.
    Thanks

    1. BrownBearWhatDoYouSee says:

      Hi Igor,
      Sorry for the late reply. I would actually just pass in the blank value to the replaceText method as that will do the same as deleting the replacement tag.

  27. Igor says:

    Hi Jeff,
    Nice tutorial.
    I was wondering how your code can be modified to append anchor tags to a cell that contains url on form submission.
    Let’s say in my Google form I have 3 fields: Name, email, website. If field contains email or url I’d like it to be wrapped in Email/Website. So, when it is copied to the Google doc it looks like a hyperlink.
    Thank you

    1. BrownBearWhatDoYouSee says:

      Hi Igor,
      Yes, you should be able to do something like that with a few different methods. First, I would use the findText method to locate the replacement tag of your link: https://developers.google.com/apps-script/reference/document/text#findText(String)
      And the you can use some of the code here to actually change the link:
      https://stackoverflow.com/questions/32602495/how-to-add-a-hyperlink-in-a-google-docs-using-a-google-script

  28. Joe McKamey says:

    Great info!

    Is there a way to have the script run automatically when a new row is added – say via a google form or Zapier, etc.

    Thanks…

    1. BrownBearWhatDoYouSee says:

      Hi Joe,
      Thanks for reading and apologies for the late reply. Yes, to the Google Form for sure. Here is a tutorial on that: https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/

      The Zapier, or any third-party integration, is a bit tricker to approach because the method of integration changes based on the product being integrated.

  29. Patricia says:

    Hello, thank you for this tutorial! I tried this tutorial but for some reason I encountered a problem that I couldn’t find in google. The date from the spreadsheet is always minus 1 day to the google docs. For example, I entered 01/19/2021 in the spreadsheet and when it’s autofill from google docs it becomes 01/18/2021. Timezone from my spreadsheet setting is correct tho. Is there anyway to fix it?

  30. Patricia says:

    Hello, thank you for this tutorial! I tried this tutorial but for some reason I encountered a problem that I couldn’t find in google. The date from the spreadsheet is always minus 1 day to the google docs. For example, I entered 01/19/2021 in the spreadsheet and when it’s autofill from google docs it becomes 01/18/2021. Timezone from my spreadsheet setting is correct tho. Is there anyway to fix it? Thanks!

    1. BrownBearWhatDoYouSee says:

      Hi Patricia,

      I’ve never had anyone bring up that issue, but maybe this date formatting utility might help: https://developers.google.com/apps-script/reference/utilities/utilities#formatDate(Date,String,String)

      Regards,
      Jeff

  31. Heather says:

    Hi Jeff,
    Thanks for the great tutorials. I have a question on dates. I have multiple columns with dates, is there a way to use friendlydate for more than 1 row of data? Or is there another way to have the dates show up in a regular mm/dd/yy(yy) format? Right now I get “Sun Jan 03 2021 03:00:00 GMT-0500 (Eastern Standard Time)” format for every date except the one I used the friendlydate for. Thank you for all your tutorials and any additional help!

    1. BrownBearWhatDoYouSee says:

      Yeah, just do the same thing you did to create the friendlyDate variable to all of the other columns you want to manipulate. Obviously, you will have to create new descriptive variable names for those pieces of data.

      Regards,
      Jeff

  32. How do you pass empty text to Google docs if your cell in the below row was not filled out?

    I don’t want this {{Text Field}} to be left inside the document. I would rather it be blank.

    body.replaceText(‘{{Text Field}}’, row[22]);

    1. BrownBearWhatDoYouSee says:

      Just the way you are doing it is fine. If there is nothing in row[22] it should replace the tag with blank values.

      JE

  33. Dean says:

    Hi, Jeff. Learn a lot from your tutorial.
    I’ve created a spreadsheet which data gathered from Google Form. Successfully done it by learning from your tutorial. Thank you so much for the knowledge.

    One of the item question in the form is upload an image. So, in the cell (column) of the spreadsheet will contain url link of the uploaded image file in Drive. I want to use the url in the Google Docs to be appear as an image, replacing the curly brace text, e.g. {{image}}. It is possible to get this scenario? Appreciate your help and thanks in advance.

    1. BrownBearWhatDoYouSee says:

      Hi Dean,

      I haven’t had time to write this up into a proper tutorial, but someone wrote some code to this (or something similar) in the comments section on this post: https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/

      Thanks for reading, and apologies for the late reply.

  34. Erin says:

    Hello! Thank you so much for this tutorial. It’s extremely helpful. One question: I keep getting the “exceeded maximum execution time” error. I have 90 rows and 11 columns. I’m pulling info from only three columns into another document (name, date, address going into letters). How can I fix this so it can pull the remaining ten rows before it times out? Thank you in advance!

    1. BrownBearWhatDoYouSee says:

      Hi, Sorry for the late reply.

      This is a common occurrence since Google Apps Script stuff is limited to these quotas: https://developers.google.com/apps-script/guides/services/quotas

      In this example though, since we check a column to see if a doc has already been made, we should be able to just run the function again and it would pick up where it left off. If that doesn’t work, you could create a function that only process the next item and is called on a time-based trigger: https://spreadsheet.dev/triggers-in-google-sheets

  35. Dana says:

    I really appreciate your ability to instruct those of us who are not trained in coding whatsoever to utilize google script – this is helping me a lot with creating an automated workflow system!

    I’m using a google form to collect some of the data needed to eventually merge with a google doc template, however there is more data I need to include that has to be either input manually or use a formula to calculate from original data responses, so I’ve used IMPORTRANGE from the original response sheet of the google form to populate a second sheet in which I have some additional columns for just this purpose.

    Here’s my question:
    I want to make sure the script runs and creates new docs ONLY when the additional columns have a value (aka, I input the final piece of data). Is there an additional line of code I can put into the google script to make this happen?

    1. BrownBearWhatDoYouSee says:

      Hi, sorry for the late reply.

      You should be able to use a variation of this line to check whichever columns you want: if (row[5]) return;

      In your case, you would want to return the function without running code if there is not a value present, which would look like this:
      if (!row[5]) return;

      We use the negation operator to check if there is NOT a value: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Logical_NOT

  36. Ricky Barnes says:

    Hey Jeff,

    The script works great! The only issue I’m running into is that I’ve got set formulas all the way down certain columns and as a result, this script is creating docs for blank rows as well. Is there a way to only create the doc when say column “A” is not empty so that I can keep my set formulas across other columns?

    1. BrownBearWhatDoYouSee says:

      Hi Ricky,
      The easiest way would be to add another if statement to check for a value in a particular row. Using the negation operator (!), you can check if there is NOT something there and return the function without executing anything else:
      if (!row[A]) return;

      JE

  37. Adam says:

    I have some columns already populated waiting for further data to be entered in that row before creating the doc from template. however when I run the script it creates docs that I don’t want created yet. how can i tell the script to stop creating docs if there is no data in a certain row?
    i hope that makes sense.

    1. BrownBearWhatDoYouSee says:

      Hi Adam,
      Sorry for the late reply. If you look at the example using if statements on line 18 and 20, you could use something similar to check your column/row to see if you want to process it. If you are looking for the absence of data, that would be something like this: if (!row[5]) return;

      The ! negation operator basically changes that line to read, if there is no data, return the function without running the code below.

      Thanks for reading,
      Jeff

  38. Srimonta says:

    Please share a code to convert auto fill doc to PDF

    1. BrownBearWhatDoYouSee says:

      Working on a more fleshed out tutorial, but here is a link to the docs: https://developers.google.com/apps-script/reference/drive/file#getascontenttype

  39. Heather says:

    Hi Jeff,
    Thank you for your tutorials they have been so helpful. I am trying to autofill a document that has multiple different dates. I used friendly date for one but the others show up formatted like “Thu Jan 07 2021 03:00:00 GMT-0500 (Eastern Standard Time).” Is there a way to get a friendly date format for more than one date (column/row)? I appreciate any help.

    1. BrownBearWhatDoYouSee says:

      Yeah, just do the same thing you did to create the first friendly date with each row: new Date(row[3]).toLocaleDateString()

      It might be a good idea to create more descriptive variable names for the other dates.

      Regards,
      Jeff

  40. Srimonta Barik says:

    Thank you for the tutorial it is very helpful.I sucessfully created Doc & PDF .Only problem that Docs that are created not removed.Please advised.

    1. BrownBearWhatDoYouSee says:

      You can call the setTrashed method on the Doc file after you’ve converted it to a PDF: https://developers.google.com/apps-script/reference/drive/file#setTrashed(Boolean)

  41. David Stone says:

    First of all, huge thanks for all the awesome tutorials, has helped me improve quite a few workflows for my co-workers.
    I was wondering if there was a way to format a number on the document/template similar to the way Jeff formatted the date? My sheet has quite a few cells with repeating decimals that I have formatted to two decimal places (x.xx) but when my script pulls them in to the doc/template, they revert back to non formatted numbers. I have tried using SetNumberFormat but I’m not knowledgeable enough to get it working properly.
    My script is almost a mirror of what was show in the tutorial.
    If I left any information out that is needed to help with this, apologies.
    Thank you

    1. BrownBearWhatDoYouSee says:

      Hi David, Apologies for the late reply, but you should be able to use the getDisplayValues function instead of getValues to have things be formatted the same way as the spreadsheet: https://developers.google.com/apps-script/reference/spreadsheet/range#getDisplayValues()

      JE

  42. Tivon Ganzy says:

    Thank you for sharing you knowledge!
    I am having a complication with my code placing the text in the wrong places. Also this Error – ” TypeError: Cannot read property ‘values’ of undefined
    autofillGoogleDocFromForm @ Code.gs:2 ” How do I Fix this bug

  43. James says:

    Thank you Jeff, The tutorial was extremely helpful. Do you have any suggestions if I wanted to use a larger template document that was populated with multiple rows? So rather than one document for each row, it would iterating down the rows and be passing information until the variables in the template are full then moving to the next document?

    1. BrownBearWhatDoYouSee says:

      Hey James,

      I have something like this, where all of the data is appended to a table in one particular document: https://jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/

      Not sure if that is any closer to what you are looking for.

      Cheers, JE

  44. Caroline says:

    Hi Jeff,

    Thank you so much for this tutorial- I am a massive beginner at script writing but excited to see if I can make it work. I have just tried to create a script, but I keep getting this error: “Attempted to execute myFunction, but could not save.” “Syntax error: SyntaxError: Unexpected end of input line: 36 file: Untitled 2.gs”. I wonder if you could help? Thank you in advance!

    Script:
    function createNewGoogleDocs() {
    const googleDocTemplate = DriveApp.getFileById(‘1q6qhxtIZqWyeVSHyk0f3LfcFjI564OZM52_LBJOzjCw’);
    const destinationFolder = DriveApp.getFolderById(’18J2GcJZ2zjSHxBh4a0UqR95Qj1rdIjpn’)
    const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(‘Test’)

    const rows = sheet.getDataRange().getValues();
    rows.forEach(function(row, index){
    if (index === 0) return;
    if (row[5]) return;
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Order , destinationFolder)
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();

    body.replaceText(‘{{Date}}’, row[11]);
    body.replaceText(‘{{Customer Name}}’, row[7]);
    body.replaceText(‘{{Address}}’, row[9]);
    body.replaceText(‘{{Item name}}’, row[0]);
    body.replaceText(‘{{SKU}}’, row[1]);
    body.replaceText(‘{{Cell dispensing choice}}’, row[0]);
    body.replaceText(‘{{Quantity}}’, row[3]);
    body.replaceText(‘{{Currency}}’, row[10]);
    body.replaceText(‘{{Price}}’, row[2]);
    body.replaceText(‘{{Total}}’, row[4]);
    body.replaceText(‘{{Shipping}}’, row[5]);
    body.replaceText(‘{{Final Total}}’, row[6]);
    body.replaceText(‘{{customer email}}’, row[8]);

    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(index + 1, 13).setValue(url)

    })

    }

    1. BrownBearWhatDoYouSee says:

      It looks like you are missing the closing backtick in this line for the string document title: const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Order , destinationFolder)

      It should be: const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Order` , destinationFolder)

      Hope that works! JE

  45. Alysa says:

    I am getting an error message for rows.forEach…”rows.forEach is not a function”

    Thanks!

    1. BrownBearWhatDoYouSee says:

      Generally that means that there was an error grabbing the spreadsheet data as an array, so I’d look at those lines to make sure all of the values match the sheet (id, sheet name, etc.) JE

  46. Matthew says:

    I am receiving this error and cannot determine why. Can you please elaborate?

    12:38:39 PM Error
    ReferenceError: googleDocTemplate is not defined
    (anonymous) @ Code.gs:20
    createNewGoogleDocs @ Code.gs:16

    Below is my script

    function onOpen () {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu(‘AutoFill Docs’);
    menu.addItem(‘Create New Docs’, ‘createNewGoogleDocs’);
    menu.addToUi();

    }

    function createNewGoogleDocs () {

    const createNewGoogleDocs = DriveApp.getFileById (‘1bxiMmgCS4Sq4pHttdpXxGoYsgR23WBwNq-E_mN0aZxs’);
    const destinationFolder = DriveApp.getFolderById (‘1uD9I1gIClagzvgI57Nr87oWHEzgBQMnG’);
    const sheet = SpreadsheetApp.getActiveSpreadsheet ().getSheetByName (‘Form Responses 1’);
    const rows = sheet.getDataRange().getValues();

    rows.forEach(function(row, index){
    if (index === 0) return;
    if (rows [13]) return;

    const copy = googleDocTemplate.makeCopy (`${row[1]}, ${row [2]} Donation Letter`, destinationFolder);
    const Doc = DocumentApp.openById(copy.getid())
    const body = doc.getBody();
    const friendlyDate = new Date (row[1]).toLocaleDateString();

    body.replaceText (`{{Date}}`, friendlyDate);
    body.replaceText (`{{MR/MRS/MS}}`, row[2]);
    body.replaceText (`{{FULL NAME}}`, row[3]);
    body.replaceText (`{{Address}}`, row[5]);
    body.replaceText (`{{City}}`, row[7]);
    body.replaceText (`{{State}}`, row[8]);
    body.replaceText (`{{Zip Code}}`, row[9]);
    body.replaceText (`{{MR/MRS/MS}}`, row[2]);
    body.replaceText (`{{Donation Amount}}`, row[11]);

    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(index +1, 15).setValue(url)
    })

    }

    1. BrownBearWhatDoYouSee says:

      There is an error in this line: const createNewGoogleDocs = DriveApp.getFileById (‘1bxiMmgCS4Sq4pHttdpXxGoYsgR23WBwNq-E_mN0aZxs’);

      It should be const googleDocTemplate = DriveApp.getFileById (‘1bxiMmgCS4Sq4pHttdpXxGoYsgR23WBwNq-E_mN0aZxs’);

  47. Alex Kidd says:

    Fantastic tutorial!!! Is there a way to modify the script so that it automatically creates the new document when someone creates the form? Right now, I have to open the spreadsheet and select the dropdown menu.

    1. BrownBearWhatDoYouSee says:

      Yup, there is a write-up on that exact thing here: https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/

Leave a Reply

Your email address will not be published. Required fields are marked *