• February 19, 2022
• in zh
• 3 min read

從Google試算表發起API呼叫

想象一下，你是一位商業用戶，你最喜歡的工具是Excel。你可能不足夠技術化來編碼，但你很擅長寫函數、在試算表中計算和繪圖。在這篇文章中，我將向你展示如何在Google試算表中編寫簡單的指令碼，這樣你就可以從中發出一個簡單的API呼叫，並做更多強大的計算。使用起來非常容易，無需安裝。

首先，開啓一個新的空白Google試算表。然後我們會利用兩個單元格作為輸入坐標。我們可以稍後從Google試算表獲取這個單元格的值。在這個例子中，我們將B1和B2作為日期時間和日期的變數。

其次，我們創建一個按鈕。从上方的导航工具中，简单地点击“插入”然后选择“画图”选项。然后我们会画一个带有“获取空气温度”文字的矩形按钮。点击保存并关闭以创建新按钮。

在新创建的按钮上点击右键并选择“分配脚本”。我们现在先输入“getAirTemperature”，稍后我们再来创建这个方法。

第三步，在导航栏中，选择“扩展”然后选择“应用脚本”。我们将编写一些Javascript代码，类似于微软Excel的视觉基本宏脚本，所有的魔力都会在这里发生。

在代码编辑器里，复制并粘贴下面的代码：

function getAirTemperature() {
    // 使用Google試算表獲取輸入值
    let ui = SpreadsheetApp.getUi();
    let sheet = SpreadsheetApp.getActiveSheet();

    // Google試算表單元格 - [行, 列]
    let inputCoordinates = {
        datetime: [1, 2],
        date:
  created: [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(
        '請確認以下參數：',
        'datetime' + ":" + datetime + "\n" +
        'date' + ":" + date + "\n",
        ui.ButtonSet.YES_NO);

    if (result == ui.Button.YES) {

        // 對天氣API發送HTTP呼叫
        let apiCaller = ApiCaller()
        let response = apiCaller.getAirTemperature(datetime, date);
        Logger.log(response.getContentText());

        ui.alert('成功獲取氣象數據');

        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++) {
            // 將輸出值設置到Google試算表
            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('權限被拒絕。');
    }

}

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)
        }
    }

}

如果你不理解这段代码在做什么，别慌，我来解释：当点击按键时，getAirTemperature方法会被触发。这个方法会从Google试算表中获取输入值，弹出一个等待用户确认的提示框。一旦用户确认，它就会使用UrlFetchApp.fetch发出一个API呼叫，获取新加坡的温度，然后它会遍历所有结果并显示输出。

第一次在按钮上点击，你会看到下面这条消息。

继续进行，登陆并选继续进行不安全的选项进行开发。

然后一個彈出式視窗會詢問是否確認輸入值：

點擊“是”繼續。如果一切順利，你應該可以看到成功確認的信息。

最后，我們從API調用中獲取溫度值，並將結果輸出到Google試算表。

就是这么简单。

如果你是一位开发人员而不是商业用户，你可能希望在你最喜欢的代码编辑器进行脚本更改，如VScode，并获取更好的源版本控制。你可以使用命令行界面clasp在你本地进行开发，而不是在线使用Apps Script编辑器。一旦你已经安装了Node.js，你可以使用以下的npm命令进行安装：

npm install @google/clasp

然后根据这个文档的说明操作：

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

例如：

clasp login
clasp clone <你的脚本ID>

在你的VS code编辑器里进行更改，然后上传新的应用脚本。

clasp push

注意：你可能需要在访问https://script.google.com/home/usersettings后，再重新启用 Apps Script API。

总的来说，Google试算表是一个强大的用于计算的工具，从中直接发起API调用對於更复杂的使用场景而言更有用。你可以将它用作进行计算的前端，而对于商务用户来说，它比使用定制的UI更加友好。如果你有任何关于從Google試算表發出API調用的問題，請让我知道。编码愉快！