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.

152 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/

  48. Stephanie Boxall says:

    Great tool! New at this as well and trying to get the Autofill to hit multiple templates at once. Is there a code that should be included at the end of each file to move to the next one? Thanks!

    1. BrownBearWhatDoYouSee says:

      Sorry for the late reply, but not sure I have an answer for that one. Thanks for reading, Jeff

  49. Eszter says:

    Thank you so much, you saved me so much time!

    1. BrownBearWhatDoYouSee says:

      Thanks for the kind words! JE

  50. Hey everyone,

    Jeff, first, thanks for this. I wanted to share a little addition that I made — I was able to take the google docs I created from the spreadsheet and save them to separate folders that were dependent on one of the fields in the form. I teach music at a university and I’m sorting the docs based on the instruments our students are playing. Hope it’s of use to someone!

    function onOpen() {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu(‘AutoFill Docs’);
    menu.addItem(‘Create New Docs’, ‘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(‘1rljT4U_n4s650qEjxHWQi0egHsNLZzWpzSlSlfiIs0w’);

    //This value should be the id of the folder where you want your completed documents stored
    const saxophoneFolder = DriveApp.getFolderById(‘1seokCmYPR-e1sWujn1AGmI3PzTIjwHH0’);
    const clarinetFolder = DriveApp.getFolderById(‘1Cl8JnV4TW57PDjpcYt08F-vFGSjpfyuB’);
    const oboeFolder = DriveApp.getFolderById(‘166vHXrHDIzV8ULYS0-DgtPxQOXVpNqtQ’);
    const bassoonFolder = DriveApp.getFolderById(‘1VOONXF7TXJbZyxfx98qAJqy7H2b9knXf’);
    const fluteFolder = DriveApp.getFolderById(‘1hrWcWALo-KCkMdsfG81ySI3_JJZrdvaN’);
    //Here we store the sheet as a variable
    const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(‘Spring 2021 Juries’);

    //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, depending on instrument type
    if (row[2] === “Saxophone”){
    const copy = googleDocTemplate.makeCopy(`${row[1]} Jury Comment Form`, saxophoneFolder);
    }
    if (row[2] === “Flute”) {
    const copy = googleDocTemplate.makeCopy(`${row[1]} Jury Comment Form`, fluteFolder);
    }
    if (row[2] === “Oboe”) {
    const copy = googleDocTemplate.makeCopy(`${row[1]} Jury Comment Form`, oboeFolder);
    }
    if (row[2] === “Bassoon”) {
    const copy = googleDocTemplate.makeCopy(`${row[1]} Jury Comment Form`, bassoonFolder);
    }
    if (row[2] === “Clarinet”) {
    const copy = googleDocTemplate.makeCopy(`${row[1]} Jury Comment Form`, clarinetFolder);
    }
    //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();

    1. BrownBearWhatDoYouSee says:

      Awesome, thanks so much for sharing. Having stuff like this in the comments is very useful for me to point people to. Thanks for reading, Jeff.

  51. Brian Moore says:

    I just want to thank you for such a well presented article that has helped me tremendously in so many ways! I am sure to have some questions but right now I am just basking in the glow of having found this gem. It was exactly what I needed and also has proven to be a great base resource for learning so much more. This sort of stuff is not my expertise by any means and trying to find clear, concise and germane material on this particular issue has proven to be quite a challenge while I am trying to take more gain more understanding of how to work with google scripts. Thank you Thank you!

    1. BrownBearWhatDoYouSee says:

      Hi Brian,

      Thanks for the kind words! Comments like yours always are what keep the content coming. Best of luck, Jeff

  52. David Steele says:

    Hello Jeff,

    Thank you for the tutorial. Would you please be able to provide some code that would allow the document to also be saved as a PDF and for the PDF link to be found in the spreadsheet? If you could provide any code it would be extremely helpful! Thank you!

  53. David Steele says:

    I figured it out! 🙂

    1. BrownBearWhatDoYouSee says:

      Awesome! Glad you were able to work through it on your own. Thanks for reading and commenting! JE

  54. Andres Fernandez says:

    Hello Jeff! Thank you so much for your guide, it is incredibly clear and easy to follow even for people who are not so much tech oriented. I’m encountering an issue when I try to implement the script, in the last part of it, with the getRange funcion. Apparently according to the documentation it has several different implementations, and it gives me an error “Error
    Exception: The parameters (String,number) don’t match the method signature for SpreadsheetApp.Spreadsheet.getRange”

    I believe it is because it is not accesing the (row, column) format, and instead it is using the “A1” notation, which brings the error. Do you know how I can use the getRange function in a way that lets me use the code you provided?

    1. BrownBearWhatDoYouSee says:

      Based on the error code, I would check the values you are passing into the getRange function. They both need to be numbers, and one is clearly evaluating as a string. The A1 notation method actually is only available on the Spreadsheet object (a collection of sheets) and not on the Sheet as we use here. Feel free to post code if you need another set of eyes.

  55. Kim says:

    HI Jeff

    Thank you so much for the clear video. I have never used script before and am well impressed that I have gotten my document 90% over the line.

    The only thing I can’t sort is the date. Regardless of what solution I try (the friendly date, the other things in the comments) I still get the full ‘Tue May 25 2021 15:35:48 GMT+0100 (British Summer Time)’ appearing in the document.

    I just want it to show the day month year.

    I have tried (2 hours + so far) to read all of the comments/link suggestions but they either give errors (like below) or don’t work. The .local option in the original instructions don’t change it either.

    Can you give the exact line of code that this needs to be and also where to insert it as I am not sure if it needs to be inserted in a different place either. Thanks

    Using this option const friendlysignDate = Utilities.formatDate(new Date(row[2]),’GMT+1′,’dd.MM.yyyy’);

    I get an error
    Syntax error: SyntaxError: Invalid or unexpected token line: 27 file: Code.gs

    1. BrownBearWhatDoYouSee says:

      Sorry, I haven’t messed around with that utility method a ton, so I can’t really speak to how best to format the timezone and format, but I can tell there are a few syntax errors in the line you copied, specifically with the way the quotation marks wrap the strings:
      Utilities.formatDate(new Date(row[2]),"GMT+1","dd.MM.yyyy");
      That could be because of how you copied it, but there were different types of quotes used which don’t actually enclose the string.

  56. Janine Smith says:

    Hi, do you know if this would work with Google Slides instead of Google Docs? Thank you.

    1. BrownBearWhatDoYouSee says:

      Yes, there is a similar method available on the SlidesApp that lets you do just that: https://developers.google.com/apps-script/reference/slides/presentation#replaceAllText(String,String)

  57. Adam says:

    Hi, this script has help a ton in day to day work. I just need one last thing that would complete this for me. Once it creates the url link, right at the end, can i have the text be the value of another cell. So instead of the long url, it is showing a name from an existing cell.

  58. Rodrigo says:

    Exxelent video and article, great explanation.

  59. Tara says:

    Loving this Jeff, thanks so much! I’m using it in a school setting. Wondering if I can run the template through the same code twice consecutively? Let me explain; based on whether the answer is “yes” or “no” I have a column in gsheets which generates a unique body for the letter. I’ve successfully pulled that body into the template but now the letter body has fields that need to be filled from the same sheet. What do you think?

  60. Tara says:

    Hi again! I did a bit of guessing and checking and was able to make some progress. On the initial execution, I got the GoogleDoc ID to post in the first row.. So now I need to run a second function through the data to get a second document using the first row’s document ID.

  61. Tara says:

    Me again 🙂
    In running my second function I’m not able to direct the function to find the ID from the same sheet. Here’s the line I used:
    const LetterTemplate = DriveApp.getFileById(‘$row[0]’);

    I’ve tried many variations (such as no dollar sign, no parenthese…) but this seems to be the point where it always gets held up.

  62. Derek says:

    I got it!

  63. Tara says:

    Nevermind, I got it! I learned about stack overflow and may have scratched an itch I didn’t even know existed. I’m so excited to explore more! Thanks for being my gateway into confidence 🙂

  64. fixnhour says:

    This a good post to read. Well done!

  65. Krista says:

    This was very helpful! How would I have it generate a Doc ONLY if one condition is true in one of the columns? I have a drop down that says yes,no, NA and I only want to create the Doc if that option is Yes.

    1. BrownBearWhatDoYouSee says:

      Thanks for reading. Yes, you can do that with an if statement. You can use the if statement to check if the value of your multiple choice question is what you want, and then execute the code inside the block.

      if (multipleChoice === "Print It"){
      // run code to create doc here
      }

  66. Clark Jaschke says:

    Hi Jeff! Is there a way to be able to repeat the template on the same page until it has to create a new page? i would want to do all of this without creating a new doc every time it fills in the template.

    1. BrownBearWhatDoYouSee says:

      Yes, you can do that using the DocumentApp. Instead of find and replace, you would append the document parts to the open document: the document parts

  67. Rob says:

    Hi, thanks for the great article!

    I have an issues with importing numbers and was wondering if you could help.

    The following code will import the name but not the number of shares and I cannot for the life of me figure it out, any ideas??

    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(‘1aHjN0O9bN9wbCWe-y1cbz2gD_qFgxs_gCZC2IkgecK0′);
    const destinationFolder = DriveApp.getFolderById(’10g3DbfNarc4O_t4DONtUFG14G9GPNAFl’);
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Data’);
    const rows = sheet.getDataRange().getValues();

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

    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[2]} Employee Details` , destinationFolder);
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();
    const friendlyDate = new Date(row[3]).toLocaleDateString();

    body.replaceText(‘{{Name}}’, row[0]);
    body.replaceText(‘{{Shares}} ‘, row[1]);

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

    })

    }

    1. BrownBearWhatDoYouSee says:

      I would first validate that your data is what you expect using Logger.log(row) inside of your loop. If that looks good, then maybe pass a hardcoded value for Shares to see if that works: body.replaceText(‘{{Shares}}‘, "5"); If it does, maybe the number value needs to be converted; if it doesn’t, likely an issue with the template tag matching, which would be my guess

  68. Roberto Barcenas says:

    Great post, has helped me a lot in generating auto fill documents.
    Wanted to ask, is there a way to auto fill a field set in the Google doc header? I set the token in the place where I want the value to be filled in the header but it is not being replaced

    1. Jeff Everhart says:

      Thanks for reading, yes you should be able to do that. Since the header is not in the body, you’ll need a few more lines:

      const header = doc.getHeader();
      header.replaceText("pattern", "text");

      https://developers.google.com/apps-script/reference/document/document#getheader
      https://developers.google.com/apps-script/reference/document/header-section#replacetextsearchpattern,-replacement

      Cheers, Jeff

  69. sunny yadav says:

    Hi, thanks for the post.
    I have my invoice in excel format. Can you please tell how to get sheets file, Like “const googleDocTemplate = DriveApp.getFileById(‘1XNtBVnDd7v4YvFqkk1X_AU0tZ9h2h_dHWU8WOEr255Y’);”

    Pasting Sheets id in this line dosent works.

    1. Jeff Everhart says:

      Most of this stuff only works on Google Doc or Sheets resources

  70. Yueh says:

    Hi Jeff,

    Thank you so much for this tutorial it was extremely helpful in my project. I ran into an issue during execution where the script generates new files for empty lines with empty drop down fields. It seems that the empty dropdowns are seen as populated fields and the script goes to generate a new file for that line. Is there a way to update the script so that empty dropdown fields can be seen as a normal empty cells so no new files are created for an empty line with empty dropdown fields? Thank you for your help!

    1. Jeff Everhart says:

      Yes, you can log out what is in the dropdown fields and then check to see if that is the value of the cell, it’s most likely an empty string: ''
      https://jeffreyeverhart.com/2020/01/24/debugging-in-google-apps-script/

  71. Charles Freeman says:

    I am getting an error
    TypeError: copy.getID is not a function

    here is my code

    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(‘1j8CDS9PdHJ5xaX80K7YrspmH947v8CjUxwsr_nfkECw’);
    const destinationFolder = DriveApp.getFolderById(‘1r8k2oIzD7XgnxZMKRSvr4gLq4aJLWr8B’);
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Data’);
    const rows= sheet.getDataRange().getValues();

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

    const copy = googleDocTemplate.makeCopy(`${row[0]} Benchmark Report`,destinationFolder);
    const doc = DocumentApp.openById(copy.getID())
    const body = doc.getBody();

    body.replaceText(‘{{Student}}’, row[0]);
    body.replaceText(‘{{Grade}}’, row[1]);
    body.replaceText(‘{{MAAP Score}}’ , row[2]);
    body.replaceText(‘{{PL}}’, row[3]);
    body.replaceText(‘{{Perc Corr}}’, row[4]);
    body.replaceText(‘{{Proj Ach Lvl}}’, row[5]);
    body.replaceText(‘{{Case Proj Scale}}’, row[6]);
    body.replaceText(‘{{Growth}}’, row[7]);

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

    })

    }

    1. Jeff Everhart says:

      copy.getID should be a lowercase ‘d’: copy.getId

  72. Andrea says:

    Great guide, really interesting and it was what I was looking for for a long time.
    THANK YOU.

    I edited the code like this but it keeps giving me error:
    TypeError: Cannot read properties of null (reading ‘getDataRange’)

    function onOpen() {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu(‘AutoFill Docs’);
    menu.addItem(‘Create New Docs’, ‘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(‘MY DOC ID’);

    //This value should be the id of the folder where you want your completed documents stored
    const destinationFolder = DriveApp.getFolderById(‘MY DRIVE FOLDER’)
    //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[9]) 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(‘{{Nome e Cognome}}’, row[3]);
    body.replaceText(‘{{Email}}’, row[3]);
    body.replaceText(‘{{Phone}}’, row[13]);
    body.replaceText(‘{{Luogo di Nascita}}’, row[14]);
    body.replaceText(‘{{Data di Nascita}}’, row[15]);
    body.replaceText(‘{{Indirizzo}}’, row[17]);
    body.replaceText(‘{{Città}}’, row[18]);
    body.replaceText(‘{{Provincia}}’, row[319]);
    body.replaceText(‘{{CAP}}’, row[20]);
    body.replaceText(‘{{Nazionalità}}’, row[21]);
    body.replaceText(‘{{Codice Fiscale}}’, row[16]);
    body.replaceText(‘{{Firma}}’, row[23]);

    //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)

    })

    }

    1. Jeff Everhart says:

      That means there was an error getting your sheet by id, I’d double check that those are correct. JE

  73. Priyoma says:

    Hi, Thank you for the great tutorial. I am new to script and could use some help.

    In my google sheet, I have one column which has youtube links, column name {{VideoLink}}
    In my doc temple, I have a replacement tag {{VideoLink}}thus.
    But in my newly created docs, the video link does not show up as a URL that is clickable. I was wondering if you could help me with that section.

    I guess, I might need to pass it as a formatted var (like you did with the date), but could not figure out how. Thanks in advance.

    1. Jeff Everhart says:

      Yeah, this is a little more complicated. Try this for that element instead of replaceText: body.findText('{{VideoLink}}').getElement().setText('text of your link').setLinkUrl('http://whatever.com')

  74. Jack Mackey says:

    Great article. I’m so close on getting this working but for some reason I’m only getting the loop to display the first result from my spreadsheet. Any tips for me?

    function onOpen() {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu(‘Print Menu’);
    menu.addItem(‘Create New Print Menu’, ‘createNewMenu’)
    menu.addToUi();
    }

    function createNewMenu() {
    //This value should be the id of your document template.
    const googleDocTemplate = DriveApp.getFileById(‘1xzlvVOSW_LsgOrm2N2o1-FKYzfqp_2EAbhqrLTn53yk’);
    const theFileReference = DriveApp.getFileById(‘1xzlvVOSW_LsgOrm2N2o1-FKYzfqp_2EAbhqrLTn53yk’);
    const oldFileName = theFileReference.getName();
    //This value should be the id of your google folder where you want to create print menus.
    const destinationFolder = DriveApp.getFolderById(’11HU0TvSPMMXUZaK5gTgg8jvofxTTt02j’);

    //Get all spreadsheet sheets.
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Draft List’);
    const sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Tapping Soon’);
    const sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Bottles and Cans’);
    //Get all of the values as 2D arrays
    const rows = sheet.getDataRange().getValues();
    const rows2 = sheet2.getDataRange().getValues();
    const rows3 = sheet3.getDataRange().getValues();
    //Logger.log(rows);

    //Create date for filename.
    const dateData = new Date();
    const curr_date = dateData.getDate();
    const curr_month = dateData.getMonth() + 1; //Months are zero based
    const curr_year = dateData.getFullYear();
    const theDate = curr_year + “-” + curr_month + “-” + curr_date;
    const newFileName = theDate + ‘ – Blue Moon Print Menu’;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy(newFileName, 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();

    rows.forEach(function(row, index) {
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText(‘{{onTapName}}’, row[0]);
    body.replaceText(‘{{onTapDescription}}’, row[1]);
    body.replaceText(‘{{onTapPrice}}’, row[2]);
    // Logger.log(row);
    });

    rows2.forEach(function(row2, index) {
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText(‘{{tappingSoonName}}’, row2[0]);
    body.replaceText(‘{{tappingSoonDescription}}’, row2[1]);
    body.replaceText(‘{{tappingSoonPrice}}’, row2[2]);
    // Logger.log(row2);
    });

    rows3.forEach(function(row3, index) {
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText(‘{{bottlesCansList}}’, row3[0]);
    Logger.log(row3);
    });

    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    }

    1. Jeff Everhart says:

      So, in this code, you are making only one copy of the document and then each time you loop through the rows you are calling the replace functions, which replace the text {{onTapName}} with a value from the sheet the first time. All subsequent loops, look for the replacement tag, which has already been replaced, and then since it can’t find the replacement tag (which has been replaced with your values by the first loop pass) just does nothing. How many documents do you want at the end?

  75. Amy says:

    Hi Jeff.
    I have two questions. First, an issue. I am using this to merge scores from three evaluators into one feedback form for each student. My Sheet is not creating the URLs. Here is the script I am using…

    function onOpen () {
    const ui = SpreadsheetApp. getUi();
    const menu = ui.createMenu(“Feedback Forms”);

    menu.addItem (‘Create Feedback Forms’, ‘createFeedbackForms’)
    menu.addToUi();
    }

    function createFeedbackForms () {
    const feedbackTemplate = DriveApp.getFileById (‘1uPfR_i60HCZs-bsxrdrPm-EkszvX4Ij1Sv_3wwaEt60’)
    const destinationFolder = DriveApp.getFolderById (‘1HdlALp-mXMmqX9DDf_PwnBRoSRV_cAJH’)
    const sheet = SpreadsheetApp.getActiveSpreadsheet ().getSheetByName (‘SUMMARY’)
    const rows = sheet.getDataRange ().getValues ();

    rows.forEach (function(row, index){
    if (index === 0) return;
    if (row [32]) return;
    if (row [6] != ‘x’) return;

    const copy = feedbackTemplate.makeCopy(`${row[1]}, ${row[0]} Feedback Forms ` , destinationFolder)
    const doc = DocumentApp.openById (copy.getId ())
    const body = doc.getBody();

    body.replaceText (‘{{Participant}}’, row[2]);
    body.replaceText (‘{{Last}}’, row[8]);
    body.replaceText (‘{{Church Name}}’, row[10]);
    body.replaceText (‘{{Church City}}’, row[11]);
    body.replaceText (‘{{Category}}’, row[0]);
    body.replaceText (‘{{Division}}’, row[33]);
    body.replaceText (‘{{Evaluator 1 Selection}}’, row[12]);
    body.replaceText (‘{{Evaluator 1 Communication}}’, row[13]);
    body.replaceText (‘{{Evaluator 1 Presentation}}’, row[14]);
    body.replaceText (‘{{Evaluator 1 Ministry}}’, row[15]);
    body.replaceText (‘{{Evaluator 1 Total}}’, row[16]);
    body.replaceText (‘{{Evaluator 1 Comments}}’, row[17]);
    body.replaceText (‘{{Evaluator 2 Selection}}’, row[18]);
    body.replaceText (‘{{Evaluator 2 Communication}}’, row[19]);
    body.replaceText (‘{{Evaluator 2 Presentation}}’, row[20]);
    body.replaceText (‘{{Evaluator 2 Ministry}}’, row[21]);
    body.replaceText (‘{{Evaluator 2 Total}}’, row[22]);
    body.replaceText (‘{{Evaluator 2 Comments}}’, row[23]);
    body.replaceText (‘{{Evaluator 3 Selection}}’, row[24]);
    body.replaceText (‘{{Evaluator 3 Communication}}’, row[25]);
    body.replaceText (‘{{Evaluator 3 Presentation}}’, row[26]);
    body.replaceText (‘{{Evaluator 3 Ministry}}’, row[27]);
    body.replaceText (‘{{Evaluator 3 Total}}’, row[28]);
    body.replaceText (‘{{Evaluator 3 Comments}}’, row[29]);
    body.replaceText (‘{{Overall Rating}}’, row[31]);

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

    })

    }

    Second is a question.
    When the Sheet is created from the form submissions, I created a secondary Sheet called “SUMMARY” (which is where I want the URLs to go). I hand-created the first content row by linking to info from the first Sheet with the original submissions. Ideally, I would like all of the 3 evaluator’s submissions for one student to be on the same row (which is how I’ve got it laid out on the SUMMARY Sheet).
    BUT… when I go to use the copy handle to copy that down to subsequent rows, it copies every duplicate entry (all 3 evaluators’ forms for the same student). Is there a way I can eliminate duplicate students/categories matches on the SUMMARY sheet so I only have one row per student entry in that category, instead of 3 for each student entry in each category in the SUMMARY Sheet?

    1. Jeff Everhart says:

      Hi Amy, thanks for reaching out. In regards to question number 1, I know you said the sheet isn’t creating the URLs, but is it creating documents? Either way, maybe checking out this article on debugging could help. Maybe using Logger.log would help you examine some of the values you expect to be there, like the URL for example. There should also be some sort of error output somewhere if things are really wrong. To answer your spreadsheet question, I’m actually not the best person to ask. I’m more of a coder than I am a spreadsheet person, so maybe the content on Ben Collins’ site might help answer that question: https://www.benlcollins.com/

      I would say that combining the results into the structure outline in the script just with spreadsheet internals might be pretty challenging. Regards, Jeff

  76. KeDric says:

    Hello and thanks for the video. Any assistance would be greatly appreciated. I believe this will really help in reducing time at my nonprofit school. I’m running into an issue. When I click the ‘AutoFill’ button I get Exception: The document is inaccessible. My script error shows (anonymous) @ Code.gs:31 createNewGoogleDocs @ Code.gs 22

    function onOpen() {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu(‘AutoFill Docs’);
    menu.addItem(‘Create New Docs’, ‘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(‘1ugOe0qBANrSgZPTHPCuBh_NBDpbJ6Y0T’);

    //This value should be the id of the folder where you want your completed documents stored
    const destinationFolder = DriveApp.getFolderById(‘1OyNk4HzUWixqI3TNRWYEiDRY1g7kRdz_’)
    //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().getDisplayValues();

    //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[13]) 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]} Scholarship 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[12]).toLocaleDateString();

    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText(‘{{First Name ST}}’, row[0]);
    body.replaceText(‘{{Last Name ST}}’, row[1]);
    body.replaceText(‘{{First Name PT}}’, row[2]);
    body.replaceText(‘{{Last Name PT}}’, row[3]);
    body.replaceText(‘{{Tuition Rate}}’, row[4]);
    body.replaceText(‘{{Student Fees}}’, row[5]);
    body.replaceText(‘{{FES or Schlarship}}’, row[6]);
    body.replaceText(‘{{FES Payment 2}}’, row[7]);
    body.replaceText(‘{{FES Payment 3}}’, row[8]);
    body.replaceText(‘{{FES Payment 4}}’, row[9]);
    body.replaceText(‘{{Internal Scholarship}}’, row[10]);
    body.replaceText(‘{{Total Due}}’, row[11]);
    body.replaceText(‘{{Due Date}}’, friendlyDate);

    //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, 14).setValue(url)

    })

    }

    1. Jeff Everhart says:

      This error “The document is inaccessible” means that the script can’t access the doc to make a copy. I’d double check that you have edit permissions and also check to see if you are logged into multiple google accounts at once. If you are, sometimes scripts won’t execute as the right person and thus not have the right privileges.

  77. Tee-Rex says:

    Thanks for the article and video as well, quick question how would i approach if i wanted to “group” similar first name + last name concat ?, and is it possible to apply some filter conditions or rules before writing the doc file ?

    1. Jeff Everhart says:

      Hi, I’m not sure I understand the first question. Can you give me an example or elaborate? You can do conditional checking with if/else statements if that’s what you mean: https://www.w3schools.com/js/js_if_else.asp

  78. Amy says:

    Hi Jeff –
    The documents aren’t creating at all, even in the folder.

    1. Jeff Everhart says:

      Thanks, can you try a few of the debugging steps outlined in this article: https://jeffreyeverhart.com/2020/01/24/debugging-in-google-apps-script/

      If nothing is happening when you trigger the script, we should be able to get some additional error details.

  79. Jill Murrin says:

    Hello,
    Thank you for sharing your knowledge. This will be very helpful. I am a complete noob to this. I am getting an error message:
    Exception: You do not have permission to call DocumentAPP.openById. Required premissions: https://www.googleapis.com/auth/documents.
    Any help you can give would be greatly appreciated

    1. Jeff Everhart says:

      It looks like there may be a typo in your code. DocumentAPP.openById should be DocumentApp.openById, so give that a try first. After that, make sure you have edit permissions or higher on the doc in question.

  80. Matt says:

    My “document is inaccessible” error was caused by my template being in docx format. The template must be saved in Google Doc format. If your template is in docx, open it then save as Google Doc format. Note that the file ID will probably change. Worked like a charm after that. See stackoverflow question 57512089. Leaving a breadcrumb here in case anyone else runs into the same problem.

    1. Jeff Everhart says:

      Awesome, thanks for following-up! Indeed this script needs the document to be a Google Doc to open it and operate properly.

  81. Salah Rushdy says:

    You are an absolute Gem!

    Thank you so much.

    I was wondering if it’s possible if you to have a column that included passport pictures of students through a Google Drive URL to be populated on the google doc template?

    1. Jeff Everhart says:

      What are you looking for to happen, a link to the image or embed the image in the doc? Here is a write-up I just did on creating a clickable link: https://jeffreyeverhart.com/2023/02/19/find-replace-text-in-google-doc-with-clickable-link/

  82. Pascoal says:

    Hello Jeff !
    Thank very much, I really appreciate your tutorial and code ! Learned a lot from that. I implemented it and I keep getting this error : “ReferenceError: Drive is not defined”.
    Would you have any thoughts to share about what may be causing it and what the solution might be. Thank you

  83. Pascoal says:

    Hello Jeff,
    So I figured out the solution :
    1) In Apps Script > Services (plus sign on the right), I scrolled down to find Drive and added this “Service”.
    2) I replaced Drive.getFileById by DriveApp.getFileById.

    Thank you for all publications.

    1. Jeff Everhart says:

      Great, glad you were able to get it working!

Leave a Reply

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