Javascript program in Google Spreadsheets: custom function (Appscript)

Peter Chang
2 min readJul 19, 2022

--

Since I discovered the AppsScript integration on Google Sheet, this real bring my work to another level, cron, automation, API integration(slack bot) are such amazing tools for managing me and my teams work.

Google Apps Script provides more than 30 built-in services for interacting with user data, other Google systems, and external systems(interacting thought API call). These services are provided as global objects akin to JavaScript’s standard Math object. For example, just as Math offers methods like random() and constants like PI, Apps Script's Spreadsheet service offers methods like openById(id), classes (child objects) like Range, and enums like DataValidationCriteria.

Step-1 Open AppsScript Editor on Google Sheet

We can easily open AppsScript editor, by Select the menu item Extensions > Apps Script.

Open AppScript Editor on Google Sheet

Example1, creating custom function double

Custom functions are created using standard JavaScript. Here’s a simple custom function, named DOUBLE, which multiplies an input value by 2:

At the top, click Save save.

Example2, creating custom function double

This is another very cool function that I really like AppsScript, it how they manipulate Rang, Rang will turn to a NxM array in javascript.

Conclusion

I used a lot of external API, cron and chat bot as a notification and automation for work, for team work, cross teams work, I could not find a better solution then Google Sheet.

Reference:

https://developers.google.com/apps-script/reference/spreadsheet

--

--

Peter Chang
Peter Chang

No responses yet