Introduction to Spreadsheet Apps Script

Image by pixabay.com

Introduction

Apps Script

Picture 1. Apps Script Menu
Picture 2. Apps Script Project

Create Menu

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

Read - Write Data

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);
}
Picture 5. Run Menu

API Call (REST)

[{
"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"
}]
+---------+-------------+-----------+
| Country | Province | Confirmed |
+---------+-------------+-----------+
| India | Maharashtra | 6363442 |
+---------+-------------+-----------+
| France | | 6232682 |
+---------+-------------+-----------+
| Turkey | | 5968838 |
+---------+-------------+-----------+
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()
}
Picture 6. Add a Library
Picture 7. Authorization Required
Picture 8. Data Successfully Pulled

Triggers

Picture 9. Add Trigger

Speed

Conclusion

Software Engineer | Traveler | Guitarist | J-Lovers