BUY FULL SOURCE CODE
Welcome folks today in this blog post we will be auto filling the google docs template from sheets data using google apps script. All the full source code of the application is shown below.
Get Started
In order to get started you need to make a new google docs
file template as shown below
As you can see inside the google docs
we have the template of the table constructed inside this we have two columns and three rows which contain the name,age and country.
And now we need to create the google sheets and inside that we will be defining the same rows and columns and insert the data as shown below
And now we need to enter inside the App Script
editor as shown below in google sheets
Now we will be writing the code.gs
file inside that we will be adding the menu inside the google sheets as shown below
code.gs
1 2 3 4 5 6 |
function onOpen() { const ui = SpreadsheetApp.getUi(); const menu = ui.createMenu('AutoFill Google Docs'); menu.addItem('Create New Document', 'createNewGoogleDocs') menu.addToUi(); } |
As you can see in the above code we are making the onOpen() method inside that we are adding the menu
inside the google sheets as shown below
So now when we click the sub menu
item we will be executing the below method inside that we will be inserting the details inside the google doc template from the sheets data as shown below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
function createNewGoogleDocs() { //This value should be the id of your document template that we created in the last step const googleDocTemplate = DriveApp.getFileById('##drivefolderid##'); //This value should be the id of the folder where you want your completed documents stored const destinationFolder = DriveApp.getFolderById('##googledocid##') //Here we store the sheet as a variable const sheet = SpreadsheetApp .getActiveSpreadsheet() .getSheetByName('Sheet1') //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('{{name}}', row[0]); body.replaceText('{{age}}', row[1]); body.replaceText('{{country}}', row[2]); //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, 4).setValue(url) }) } |
As you can see in the above code we need to replace the google drive folder id
and we need to copy paste the google doc file
id and inside that we will be inserting the name,age and country from the google sheets and then we will be saving those details inside the google drive
So now before executing the google apps script we need to create the google drive folder we need to copy paste the id of the folder as shown below in the above code
And after that we need to replace the id of the google docs file as shown below in the above code
So now if you execute the google apps script you will see the below result. The document url will be generated inside the google sheets and our google docs files will be saved inside the google drive folders as shown below
As you can see the data has been successfully fetched from google sheets and inserted inside the google docs template and it has created a new google docs file as shown above.
BUY FULL SOURCE CODE