Simple Reporting with Google Apps Script

5 min readSep 24, 2020

There are lots of reporting tools for different programming language in market, but sometimes we just want something very simple. We may only want to generate a few reports, so doesn’t want to spend time on complex setup procedure and troubleshooting

Today we will go through how to create a simple report generator with google drive and google script. No setup require, no server required, all you need is just a Google account.

This tutorial will create a cover letter generator based on the template in Google docs

Login to your Google Drive
Create new google scripts

If “Google Apps Script” not found in menu, click more -> connect more apps, then you can get it from marketplace

3. Create a new Google Script

We will create 2 functions

createDocument(companyName, positionName, recruiter)

This function responsible to generate the document

var TEMPLATE_ID = '';

This is the id of our document template, we will talk about it later.

drivedoc.setName(“cover_letter2_” + companyName + “_” + positionName);

This specific the file output name

body.replaceText('{date}', Utilities.formatDate(new Date(), "GMT+14", "dd MMM yyyy"));

This line will generate the date by specific format and timezone.

if (!recruiter) {
    body.replaceText('{recruiter}', " " + "HR Manager");
  } else {
    body.replaceText('{recruiter}', " " + recruiter);
  }

This replace the placeholder {recruiter} with the GET parameter. If it doesn’t supply this value, we just replace it with “HR Manager”

body.replaceText('{companyName}', companyName);
body.replaceText('{positionName}', positionName);

This replace the placeholder {companyName}, {positionName} with the GET parameters.

drivedoc.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);

Everyone can access the generated document with the download link can edit it. If you want more secure, you can replace “ANYONE_WITH_LINK” with “PRIVATE” and “EDIT” with “VIEW”. For detail spec of access permission here and drive permission here

var html = "<HTML><a href='" + "https://docs.google.com/document/d/" + documentId + "/export?format=pdf" + "'>Download Cover Letter</a></HTML>";

This generated document will be export with pdf and the link will have the wordings “Download Cover Letter”

The full code snippet

function createDocument(companyName, positionName, recruiter) {
  var TEMPLATE_ID = '';  
  var documentId = DriveApp.getFileById(TEMPLATE_ID).makeCopy().getId();
  
  drivedoc = DriveApp.getFileById(documentId);
  drivedoc.setName("cover_letter2_" + companyName + "_" + positionName);
  
  doc = DocumentApp.openById(documentId);
  
  var body = doc.getBody();
  
  if (!recruiter) {
    body.replaceText('{recruiter}', " " + "HR Manager");
  } else {
    body.replaceText('{recruiter}', " " + recruiter);
  }  
  body.replaceText('{date}', Utilities.formatDate(new Date(), "GMT+14", "dd MMM yyyy"));
  body.replaceText('{companyName}', companyName);
  body.replaceText('{positionName}', positionName);
  drivedoc.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);var html = "<HTML><a href='" + "https://docs.google.com/document/d/" + documentId + "/export?format=pdf" + "'>Download Cover Letter</a></HTML>";
  
  return html;
}

doGet(e)

This is a “trigger” function which defined in the spec of Google App Script -
“runs when a user visits a web app or a program sends an HTTP GET request to a web app.” In other words, this function serve our request and read the GET parameters

//Read recruiter, company name, position name from urlfunction doGet(e) {
  var recruiter = e.parameter.recruiter;
  var companyName = e.parameter.companyName;
  var positionName = e.parameter.positionName;
  var url = createDocument(companyName, positionName, recruiter);
  
  return HtmlService.createHtmlOutput(url);
}

5. Save it and Open a new Tab in browser to the same Google Drive.

6. Create a document template with Google docs

7. This is my cover letter template created in Google Docs. Nothing special, except we have to use the curly braces to contain those placeholder

We have to create 3 placeholders here (companyName, recruiter and positionName) as we defined above and they should consistent with our coding in function doGet(e)

8. In the URL, we can see something like this

https://docs.google.com/document/d/1sUmG1kESW_KTy0oXpg3ADk4_p4IBDc4nzHiAUkyMPJ4/edit

Copy “1sUmG1kESW_KTy0oXpg3ADk4_p4IBDc4nzHiAUkyMPJ4” (this is my document ID, your document ID should be different)

9. Go back to the tab of Google Script, place the above String in the Google in the TEMPLATE_ID in your Google Script. It will be something like this

var TEMPLATE_ID = '1sUmG1kESW_KTy0oXpg3ADk4_p4IBDc4nzHiAUkyMPJ4'

10. Everything done. We’re going to publish it now!

Allow the permission for your cover letter generator

This is the URL to access your cover letter generator, copy it and same in somewhere. It would be something like this.

https://script.google.com/macros/s/AKfycbw6GnXKKz7wCVEizCUVNldArNFo_9HiUtgkhF4tDMs40Mnlqvk/exec

Try it in browser, it is not working

Because we have to add the GET parameters, supply the information of company name, recruiter and position, which are different for each cover letter

https://script.google.com/macros/s/AKfycbxwVVNbn2jORE_uRZ2SgREkY_cNdMJbiMi31wfz/exec?companyName=BNZZZ&positionName=Senior Analyst Programmer — Java&recruiter=Human Resource Manager

Just remember, ‘companyName’, ‘positionName’ and ‘recruiter’ has to be consistent with the document template as well as in the script function — doGet(e)

Try again in browser

No more error this time, you can click “Download Cover Letter” to download it

Optional. If you want to generate a lot of cover letter, it may not convenient to edit the URL manually.

Just do it with Google Spreadsheet

By input the company name, position name and recruiter name in spreadsheet, it will generate the download link itself.

Simple Reporting with Google Apps Script

Written by Johnny Lai