Introduction to Spreadsheet Apps Script

Image by pixabay.com

Introduction

Hi everyone! It’s been quite a while since I wrote the last article in Jan 21. Finally I’m back! This time I will share a topic, still related with technology which is about Google Spreadsheet! Maybe we usually use Spreadsheet to make a report, calculate data with formula, visualize the data through chart, and etc. However, with Apps Script tools we can improve the way we manage the data better!

Apps Script

With Apps Script tool from Spreadsheet, we can do some operation regarding how we manage the data inside the Sheet. Like writing some row and column. Basically we will write using JavaScript programming language.

In this article, we will try to create new menu which has simple application behind it to pull data from some web services and show it on Spreadsheet. We can find the menu from Extensions and choose App Script like the picture below.

Picture 1. Apps Script Menu

We will redirected to Apps Script project. In this page, we can write some Javascript to write any data to the Sheet. The project name also can be renamed by clicking the Untitled project and just changed it.

Picture 2. Apps Script Project

Create Menu

To add new menu, we can write some script inside onOpen function like the picture below.

function onOpen() {
   var ui = SpreadsheetApp.getUi()
   ui.createMenu('Example Menu')
   .addItem('Run Function', 'myFunction')
   .addToUi()
}

Picture 3. Create Menu

After save the project, go back to Sheet and refresh the page. We will find new menu that we defined on the Apps Script beside Help menu. In this example, we named it Example Menu with Run Function as submenu.

Picture 4. Menu Shown

Read - Write Data

Let’s try to read and write a simple data into first row on A and B column. The data will be executed after we click the Run Function submenu. Then, we need to modify the myFunction like this:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cellA1 = sheet.getRange(1,1);
  var cellB1 = sheet.getRange(1,2);
  
  var today = new Date();
  cellA1.setValue(today);
  var tomorrow = new Date(cellA1.getValue());
  tomorrow.setDate(tomorrow.getDate()+1);
  
  cellB1.setValue(tomorrow);
}

After the script saved, go back to Sheet. Run the menu and see the first row on A and B column will be filled by date.

Based on the script above, we try to get the current date and write on first row A column. How about the read data? The first row B column handle it. As we can see, we will read the value of first row A column and add 1 day into it.

Picture 5. Run Menu

API Call (REST)

As mentioned on the introduction, we will pull data from web services (in this case we will use REST) and write the data into Sheet.

Because of currently (2021) we have pandemic situation, let’s try with Covid-19 API here https://covid19.mathdro.id/api and call this endpoint https://covid19.mathdro.id/api/confirmed

The return of the endpoint will be like :

[{
 "provinceState": "Maharashtra",
 "countryRegion": "India",
 "lastUpdate": 1628691693000,
 "lat": 19.449759,
 "long": 76.108221,
 "confirmed": 6363442,
 "deaths": 134201,
 "recovered": null,
 "active": null,
 "admin2": null,
 "fips": null,
 "combinedKey": "Maharashtra, India",
 "incidentRate": 5167.47099049868,
 "peopleTested": null,
 "peopleHospitalized": null,
 "uid": 35620,
 "iso3": "IND",
 "cases28Days": 190797,
 "deaths28Days": 7981,
 "iso2": "IN"
}, {
 "provinceState": null,
 "countryRegion": "France",
 "lastUpdate": 1628691693000,
 "lat": 46.2276,
 "long": 2.2137,
 "confirmed": 6232682,
 "deaths": 111224,
 "recovered": null,
 "active": null,
 "admin2": null,
 "fips": null,
 "combinedKey": "France",
 "incidentRate": 9549.99020123201,
 "peopleTested": null,
 "peopleHospitalized": null,
 "uid": 250,
 "iso3": "FRA",
 "cases28Days": 483089,
 "deaths28Days": 768,
 "iso2": "FR"
}]

Then will try to write simple table like this:

+---------+-------------+-----------+
| Country | Province    | Confirmed |
+---------+-------------+-----------+
| India   | Maharashtra | 6363442   |
+---------+-------------+-----------+
| France  |             | 6232682   |
+---------+-------------+-----------+
| Turkey  |             | 5968838   |
+---------+-------------+-----------+

Next, let’s modify the script like this:

function onOpen() {
  var ui = SpreadsheetApp.getUi()
  ui.createMenu('Example Menu')
      .addItem('Run Function', 'myFunction')
      .addToUi()
}
function clearTable(table_name) {
  var gs = SpreadsheetApp.getActive().getSheetByName(table_name)
  gs.getRange('A2:O').clearContent()
}
function apiCallGet(url) {
  var httpResponse = UrlFetchApp.fetch(url)
  if (httpResponse) {
    var responseCode = httpResponse.getResponseCode()
    switch (responseCode) {
      case 200:
        var data = JSON.parse(httpResponse.getContentText())
        return (data)
      default:
        return false
    }
  } else {
     return false
  }
}
function recoverToJson(item) {
  return {
    Country: item.countryRegion,
    Province: item.provinceState,
    Confirmed: item.confirmed,
  }
}
function myFunction() {
   clearTable("Sheet1")
   var table = Sheetfu.getTable('Sheet1', 1)
   var recovers = apiCallGet(`https://covid19.mathdro.id/api/confirmed`)
   recovers.map( item => {
     table.add(recoverToJson(item))
   })
   
   table.commit()
}

As we can see from the script above, we use Sheetfu library https://github.com/socialpoint-labs/sheetfu-apps-script to help manage the sheet easily. We can add some library by adding from the left menu and fill the Script ID .

Picture 6. Add a Library

After saved the script, try to re-run the Run Function. Probably we will asked for some permission, just allow it.

Picture 7. Authorization Required

And after the script finished, the data from API successfully pulled into Sheet!!

Picture 8. Data Successfully Pulled

Triggers

The interesting things we can have ability to automatically complete tasks with time-driven triggers. The function can run by specific time like by the minute, hour, day, week, month, and etc. I am not sure but I think the triggers also running on background! It’s mean if we close the Sheet, the trigger always running according to the schedule.

Picture 9. Add Trigger

Speed

To pull and write the data into Sheet, here is two case and the result:

• 3948 rows, 3 columns : 26 seconds => call confirmed endpoint (1.49MB-185ms)
• 195 rows, 1 column : 3 seconds => call countries endpoint (8.75KB-39ms)

For the column, I think it’s not really matter because with same rows, how much column doesn’t really affected the time.

Conclusion

After try to build a simple application by Apps Script, some interesting point that we can conclude are:

1. Easy to use.
2. Support API call.
3. Has time-driven triggers.
4. Don’t need take care about server.
5. Don’t need to build interface.

Maybe we are not fully yet exploring the entire feature and it could be another hidden gems for this Spreadsheet. But as usual, hopefully this article is useful for you, thank you!!