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 >>>
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.
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.
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.
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();
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
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)
})
}
What error messages are you getting?
Hello thanks for the tutorial
Im getting this error
exception unexpected error while getting the method or property getfolderbyid on object driveapp
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
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.
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
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!
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.
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?
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
How do I show a column with an amount with a dollar sign and comma? for example, &66,666.67
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()
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?
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
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!
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.
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
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.
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
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)
})
}
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.
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.
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
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.
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
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();
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
Hey i would like to know if its possible to work this code with multiple spread sheets at the same time?
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
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?
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
getDataRangemethod, which gets a range that contains the maximum extent of your data, you could try using thegetRangemethod, which allows you to select a more specific range. You can still callgetValuesorgetDisplayValuesand 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
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)
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
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!
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
newFoldervariable?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
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!
Yes, with something like this (which assumes the variable name is GMN):
if (!GMN) {
GMN = 'Your new value';
}
Regards,
Jeff
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);
})
}
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
createFiledirectly on thepdffoldervariable to keep things a little bit more precise: https://developers.google.com/apps-script/reference/drive/folder#createFile(String,String)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);
})
}
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!
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)
})
}
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.
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
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
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
Hi Igor,
Sorry for the late reply. I would actually just pass in the blank value to the
replaceTextmethod as that will do the same as deleting the replacement tag.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
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
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…
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.
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?
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!