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!
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
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!
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
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]);
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
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.
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.
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!
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
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?
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
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?
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
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.
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
Please share a code to convert auto fill doc to PDF
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
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.
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
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.
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)
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
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
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
I explain some of that here: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
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?
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
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)
})
}
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
I am getting an error message for rows.forEach…”rows.forEach is not a function”
Thanks!
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
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)
})
}
There is an error in this line:
const createNewGoogleDocs = DriveApp.getFileById (‘1bxiMmgCS4Sq4pHttdpXxGoYsgR23WBwNq-E_mN0aZxs’);It should be
const googleDocTemplate = DriveApp.getFileById (‘1bxiMmgCS4Sq4pHttdpXxGoYsgR23WBwNq-E_mN0aZxs’);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.
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/
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!
Sorry for the late reply, but not sure I have an answer for that one. Thanks for reading, Jeff
Thank you so much, you saved me so much time!
Thanks for the kind words! JE
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();
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.
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!
Hi Brian,
Thanks for the kind words! Comments like yours always are what keep the content coming. Best of luck, Jeff
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!
I figured it out! 🙂
Awesome! Glad you were able to work through it on your own. Thanks for reading and commenting! JE
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?
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.
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
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.
Hi, do you know if this would work with Google Slides instead of Google Docs? Thank you.
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)
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.
Exxelent video and article, great explanation.
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?
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.
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.
I got it!
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 🙂
This a good post to read. Well done!
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.
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
}
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.
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
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)
})
}
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 guessGreat 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
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
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.
Most of this stuff only works on Google Doc or Sheets resources
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!
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/
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)
})
}
copy.getIDshould be a lowercase ‘d’:copy.getIdGreat 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)
})
}
That means there was an error getting your sheet by id, I’d double check that those are correct. JE
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.
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')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();
}
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?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?
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.logwould 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
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)
})
}
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.
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 ?
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
Hi Jeff –
The documents aren’t creating at all, even in the folder.
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.
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
It looks like there may be a typo in your code.
DocumentAPP.openByIdshould beDocumentApp.openById, so give that a try first. After that, make sure you have edit permissions or higher on the doc in question.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.
Awesome, thanks for following-up! Indeed this script needs the document to be a Google Doc to open it and operate properly.
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?
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/
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
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.
Great, glad you were able to get it working!