App Script Project to Auto Fill Google Docs Template from Google Sheets Data & Download it in Drive

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

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

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

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

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.