Google Apps Script is cool

I think you probably know Google office apps such as Docs, Sheets, Slides and Forms, but have you ever heard of Google Apps Script? If the answer is “No”, you may have missed out on some really cool stuff that Apps Script does. Well, it depends on how you define “cool”, but anyway, let’s just bear with me for a while and you can decide later whether to keep it in mind or not.

1. What is Apps Script

Apps Script is a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products.

Source: developers.google.com/apps-script

There are 2 types of scripts: Standalone and Container-bound.

A standalone script is any script that is not bound to a Google Sheets, Docs, Slides, or Forms file. These scripts appear among your files in Google Drive. 

Container-bound is on the contrary, and in this blog I would like to focus on this type of script.

I’ll take Sheets as an example, but it can do the same things for other Google office apps as well. If we implement Apps Script in our Sheets, it can helps us with:

• Automation: automate tasks that are related to a sheet.
• Create custom functions: these functions can be called in your sheet to do what you need (like generate reports with extra conditions, etc.)
• Create APIs that can retrieve data from this sheet.

2. How to access Apps Script

Because Container-bound apps script is integrated within each office file, in order to access Apps Script, we’ll need to open this office file first. I’ll call it a container file.

For Google Docs, Sheets, or Slides, open your file, click on extensions tab and select Apps script.

For Google Forms, open a form and click More then select Script editor.

Apps script is like below.

Code.gs is your main script file but you can also create other script files to separate functions that will be called in the main script.

Beside Code.gs, you can create an .html file if you want to create UI for the data you get from that container file. Since Container-bound apps script binds to its original file, it has some functions to access to its origin, such as  getActiveSpreadsheet(), getActiveDocument(), getActivePresentation(), and getActiveForm(). We can also access the user interface of container files to add custom menus, dialogs, and sidebars using getUI().

You can read more about Container-bound script here.

If you still feel blurry about its application, let’s move on to the part on what you can use it for.

3. A small application

I used Apps Script to create my financial tracker years ago, and it’s been working really well since then.

The flow is simple:

• I use a form to collect expense data, anytime I have an expense, i submit the form
• Each expense data will be recorded in a sheet
• I use Apps Script for that sheet to get data and calculate the remaining budget of a month.
• Create an html output from above data so i can check what i have spent on that month and how much is left in the budget from my phone (cause i don’t want to install Google Sheets app on my phone, and i want the ui that focuses on remaining budget only).

Here are the steps to implement. This is just an example, and when you understand what apps script can do, you can start utilizing it your own way.

a) Step 1: Create a Google Form

Create a simple form to collect data.

b) Step 2: Link Form to Sheet

In the Responses tab, choose Link to Sheets. You can either create a new sheet or link to an existing one. After connection is made, the text will be changed to View in Sheets.

When you submit the form, data will be recorded in Sheet.

c) Step 3: Use apps script to calculate remaining budget

Depending on the situation, there are many functions that you can use to retrieve data from the sheet here.

We can always use Logger.log() to check the output.

function getExpense() {
  // this is to specify the sheet you want to get
  const sheet = SpreadsheetApp. getActiveSpreadsheet().getSheetByName("Form Responses 1"); 
  // get all rows of that sheet
  const rows = sheet.getDataRange().getValues();
  let budget = 14000000;
  const expense = [];
  for (let i=1; i<sheet.getLastRow(); i++) {
    const row = rows[i];
    budget -= row[2];
    expense.push(`<div>${row[1]}:  ${Intl.NumberFormat().format(row[2])}</div>`)
  }
  const returnValue = {
    expense: expense,
    budget: Intl.NumberFormat().format(budget),
  }
  Logger.log(returnValue);
  return (returnValue);
}

d) Step 4: Create HTML output

Both standalone scripts and scripts bound to Google Workspace applications can be turned into web apps, so long as they meet the requirements below.

Source: https://developers.google.com/apps-script/guides/web

A script can be published as a web app if it meets these requirements:

• It contains a doGet(e) or doPost(e) function.
• The function returns an HTML service HtmlOutput object or a Content service TextOutput object.

function doGet() {
  const sheet = SpreadsheetApp. getActiveSpreadsheet().getSheetByName("Form Responses 1"); 
  const date = new Date()
  const month = date.getMonth();
  const months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']
  const {expense, budget, total} = getExpense();
// I'm building the web as simple as possible but you can create separate html and return it here
  const output = HtmlService.createHtmlOutput(`<div style="font-family: Kode Mono, monospace; line-height:1.5"><div>${months[month]} spending status 💸 </div><br/><div>Spent: ${total}đ<br/>Remain: ${budget}đ</div><br/><div>(*ᴗ͈ˬᴗ͈)ꕤ*.ﾟ</div><br/><div>-----Expense-----</div><div>${expense}</div></div>`)
  output.addMetaTag('viewport', 'width=device-width, initial-scale=1');
  return output;
}

e) Step 5: Deploy!

Click on Deploy button and select New deployment, fill in the name and start deployment.

After that, click on the Web app link, you’ll see the html output as below.