Generate word document from google sheet

Generate a document out of google sheet is one of the tasks businesses wants when maintaining their data in spread sheets. It can be from PO, Bill of Lading, Invoice or what ever. Google makes it really easy for us to do this through App scripts. All  you need to have is a bit of java script knowledge.

Google does this magic by combining google api's and javascript run on it server (not everything in java script works here).

Lets assume we have a simple google sheet as below.

Lets say we want to generate a document as below.

There are 2 ways to do this.

  • Add a App script bound to the sheet (applies only to this script).

– Pros: We dont need to leave the sheet we are working on.

Can create a menu link to trigger script easily.

Write script by taking current selected range into consideration.

Business people loves it as they dont need to modify anything in the script.

– Cons: The script is applicable only to that sheet. There are ways to make it                                       available to other sheets also.

  • Create a independent App script and send sheet_id, rows to generate document as a variables.

– Pros:  It can be used for any sheet (by supplying the sheet id and row range info).

– Cons: You should know where the App script lives,  enter the the sheets id and                    rows range. It might be painful for the business people to enter all this low level                  details.

I will explain the first approach here(my favorite).

Create a document template:

Create a order summary template like this.

order summary template

Create a App script:

On the sheet menu go to `Tools` –> `Script Editor`

script editor

Give the project a name and replace the following script in it.

function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu(‘XYZ Menu’)
.addItem(‘Generate PO’, ‘createDcument’)
.addToUi();
}

function createDcument() {
var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
var activeRange = activeSheet.getActiveRange();
var row_data = activeRange.getDisplayValues();
var scriptProperties = PropertiesService.getScriptProperties();
var template_id = scriptProperties.getProperty(‘template_id’); // document template

/***Modify indexs if you the spread sheet columns shuffle **/
var attributes = {
bill_no: “0”,
vendor: “1” ,
date: “7” ,
description: “2” ,
length: “3” ,
width: “4”,
height: “5”,
weight: “6”
}
/****************/
var documentId = DriveApp.getFileById(template_id).makeCopy().getId();
DriveApp.getFileById(documentId).setName(‘Purchase Order PO# ‘+ row_data[0][0]);
var body = DocumentApp.openById(documentId).getBody();

for (var prop in attributes) {
var index = attributes[prop];
var propValue = row_data[0][index];
propValue = propValue ? propValue : “”;
body.replaceText(‘##’+prop+’##’, propValue);
}
}

You can find the script here also.

Settings:

I made the template_id as a script variable which can be set from File –> Project Properties so that you dont need to modify the script again.

script properties

Last thing you need to do is add Sheets API and Drive API by going into Resources –> Advanced Google services and also dont forget them to enable in Console.

advanced google services

phew! all the hard work is done sit back and refresh your sheet. You should be seeing the new menu XYZ Menu —> Generate PO

new menu from app script

Click on it (for first time it asks for permissions. grant it). Go to you template folder and you should see the generated document with the bill no included in the file name.

document generated

Hope this will save you sometime. Ping me with any concerns/ comments.

adiós!