Making API call from Google Sheet

February 18, 2022

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.

2022 02 19 1

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 text “Get Air Temperature”. Click save and close to create the new button.

2022 02 19 2

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.

2022 02 19 3

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 get input values from Google Sheets, pop up an alert button for user confirmation. Once it’s confirmed, it makes an API call with UrlFetchApp.fetch to get the Singpaore temperature and finally it loop through all the results to display the output.

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

2022 02 19 4

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

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

2022 02 19 5

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

2022 02 19 6

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

2022 02 19 7

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 given that you can do API calls from it 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!


Profile picture

Experience in software development, application architecture, and deploying cloud solutions for enterprise customers. Strong hands-on skills with a Master's degree in Computer Science and business acumen with a master of business administration (MBA) in Finance. Certified in Amazon Web Services (AWS), Google Cloud Platform (GCP), Microsoft Azure, Kubernetes (CKA, CKAD, CKS, KCNA) and Scrum (PSM, PSPO) with experience in building banking products from scratch. Connect on Linkedin

© 2022, @victorleungtw