# 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 stuffs that Apps Script do. Well, it depends on how you define "cool", but anyway, let's just bare with me for a while and you can decide later wether to keep it in mind or not.

### I. 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 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 task that related to a sheet.
- Create custom functions: these function can be called in your sheet to do what you need (like generate report with extra conditions, etc.)
- Create API that can retrieve data from this sheet.


### II. How to access Apps Script

Because Container-bound apps script is integrated within each office file, in order to access the file. I'll call this 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 main script.

Beside `Code.gs`, you can create `.html` file if you want to create UI for the data you get from that container file.

Since Container-bound apps script bounds to it's original file, it has some functions to access to it's origin `getActiveSpreadsheet()`, `getActiveDocument()`, `getActivePresentation()`, and `getActiveForm()`. We can also access the user interface of container file to add custom menus, dialogs, and sidebars using `getUI()`.

You can read more about Container-bound apps script [here](https://developers.google.com/apps-script/guides/bound).
If you still feel blurry about it's application, let's move on to the part on what you can use it for.

### III. A small application

I used appscript 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 appscript for that sheet to get data and calculate 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 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 focus 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.

#### 1) Step 1: Create a Google Form

Create simple form to collect data.


#### 2) Step 2: Link Form to Sheet

In `Responses` tab, choose `Link to Sheets`. You can either create new sheet or link to exisitng 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.


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

Depends on the situation, there are many functions that you can use to retrieve data from the sheet [here](https://developers.google.com/apps-script/reference/document/document-app).

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


#### 4) 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;
}

#### 5) 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.


Ref:
https://developers.google.com/apps-script/reference/document/document-app

<small>
Published date: 2024-08-28 <br/>
Also published <a href="https://josysnavi.jp/2024/blog-00189">here</a>.
</small>