Making API call from Google Sheet

Imagine you are a business user and your favourite tool of all time is Excel. You are not technical enough to do coding, but you are comfortable writing functions, calculating in the spreadsheet and plotting graphs. In this article, I am going to show you how to write simple scripts in Google Sheets, so that you can make a simple API call from it and do a lot more powerful tabulation. It is easy to use with no installation required.

Firstly, open a new blank Google Sheet. We would then make use of two of the cells as the input coordinates. We can get the values from this cell using Google Sheets later on. In this example, we make B1 and B2 as variables for the datetime and date respectively.

Secondly, we create a button. From the top navigation tool, simply click on “Insert” and then “Drawing” option. And we would draw a rectangle button with the text “Get Air Temperature”. Click save and close to create the new button.

Right-click on the new button and select “Assign a script”. We would input the value “getAirTemperature” for now and we will create the method later.

The third step, in the navigation bar, select “Extensions” and then select “App Scripts”. Similar to Microsoft Excel Visual Basic macro script, we are going to write some Javascript instead, where all the magic happens.

In the code editor, copy and paste the code below:

function getAirTemperature() {
// Getting input values from Google Sheet
let ui = SpreadsheetApp.getUi();
let sheet = SpreadsheetApp.getActiveSheet();
// Google Sheet Cell - [Row, Column]
let inputCoordinates = {
datetime: [1, 2],
date: [2, 2],
}
let outputCoordinates = {
stations: [2, 4],
readings: [2, 6]
}
let datetime = sheet.getRange(
inputCoordinates.datetime[0],
inputCoordinates.datetime[1]
).getValue();
let date = sheet.getRange(
inputCoordinates.date[0],
inputCoordinates.date[1]
).getValue();
let result = ui.alert(
'Please confirm the following parameter:',
'datetime' + ":" + datetime + "\n" +
'date' + ":" + date + "\n",
ui.ButtonSet.YES_NO);
if (result == ui.Button.YES) {
// Make HTTP call to Weather API
let apiCaller = ApiCaller()
let response = apiCaller.getAirTemperature(datetime, date);
Logger.log(response.getContentText());
ui.alert('Get waether data successfully');
let data = JSON.parse(response.getContentText());
let stations = data["metadata"]["stations"];
let readings = data["items"][0]["readings"];
for (let i = 0; i < stations.length; i++) {
// Setting output values to Google Sheet
sheet.getRange(
outputCoordinates.stations[0] + i,
outputCoordinates.stations[1]
).setValue([stations[i]["name"]]);
sheet.getRange(
outputCoordinates.readings[0] + i,
outputCoordinates.readings[1]
).setValue([readings[i]["value"]]);
}
} else {
ui.alert('Permission denied.');
}
}
ApiCaller = function () {
var weatherApi = {
"airTemperature": {
"method": "GET",
"endpoint": "https://api.data.gov.sg/v1/environment/air-temperature"
}
}
return {
getAirTemperature: function (datetime, date) {
datetime = datetime.replace(/"/g, "")
date = date.replace(/"/g, "")
var method = weatherApi.airTemperature.method
var url = weatherApi.airTemperature.endpoint + "?date_time=" + datetime + "&date=" + date
var options = {
"method": method,
"contentType": "application/json",
"headers": {
"Content-Type": "application/json"
}
};
return UrlFetchApp.fetch(url, options)
}
}
}

If you don’t understand what’s the code is doing, don’t panic, let me explain: when the button is clicked, the getAirTemperature function is triggered. It then gets input values from Google Sheets, pops up an alert button for user confirmation. Once it’s confirmed, it makes an API call with UrlFetchApp.fetch to get the Singapore temperature and finally it loop through all the results to display the output.

The first click on the button, you would be prompted with this message.

Go ahead and click continue, login and continue with unsafe options for development purposes.

Then an alert would pop up to asked for confirmation of input values:

Click “Yes” to continue. If everything goes well, you should see the success confirmation.

Finally, we get the temperate values from API call and output the result in the Google Sheet.

As simple as that.

If you are a developer instead of a business user, you may want to edit the script in your favourite code editor, such as VScode and get a better source version control. Instead of editing using the online Apps Script editor, you can use the Command Line Interface clasp to develop locally. Once you’ve installed Node.js, you can use the following npm command to install:

npm install @google/clasp

Then follow the instructions on this doc:

https://developers.google.com/apps-script/guides/clasp

e.g.

clasp login
clasp clone <your Script ID>

Make changes in your VS code editor, then upload the new AppScript.

clasp push

Note: you may need to enable Apps Script API by visiting https://script.google.com/home/usersettings then retry.

Overall, Google Sheets is a powerful tool for you to do computation. It is even more useful since you can do API calls directly for more complex use cases. You can use it as a frontend for doing calculations and it’s very user friendly to business users instead of using custom build UI. Let me know if you have any questions about making API call from Google Sheet. Happy coding!

Originally published at http://victorleungtw.com on February 18, 2022.