Skip to content

WebNinjaDeveloper.com

Programming Tutorials




Menu
  • Home
  • Youtube Channel
  • Official Blog
  • Nearby Places Finder
  • Direction Route Finder
  • Distance & Time Calculator
Menu

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

Posted on December 6, 2022

 

 

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

 

 

JavaScript
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

 

 

JavaScript
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.

 

Recent Posts

  • Android Java Project to Crop,Scale & Rotate Images Selected From Gallery and Save it inside SD Card
  • Android Kotlin Project to Load Image From URL into ImageView Widget
  • Android Java Project to Make HTTP Call to JSONPlaceholder API and Display Data in RecyclerView Using GSON & Volley Library
  • Android Java Project to Download Youtube Video Thumbnail From URL & Save it inside SD Card
  • Android Java Project to Embed Google Maps & Add Markers Using Maps SDK
  • Angular
  • Bunjs
  • C#
  • Deno
  • django
  • Electronjs
  • java
  • javascript
  • Koajs
  • kotlin
  • Laravel
  • meteorjs
  • Nestjs
  • Nextjs
  • Nodejs
  • PHP
  • Python
  • React
  • ReactNative
  • Svelte
  • Tutorials
  • Vuejs




©2023 WebNinjaDeveloper.com | Design: Newspaperly WordPress Theme