使用 Google App Scripts 連接 CoinGecko 更新 $LIKE 及 $OSMO 等幣價
之前我曾經分享使用 CoinGecko API 於 Python 及 Telegram 中取得 $LIKE 等幣價。今次我們換個地方，於 Google Spreadsheet 中製作幣價列表，再配合 Apps Script 定期更新。
▶️ 連接 Apps Script
在上方的 "Extension" 找到 Apps Script，按下後會打開 Apps Script 代碼編輯器。
我們的主要代碼將會撰寫到預設的 myFunction 函數中。將來熟習後，亦建議按需要為函數更名為更貼切的名稱，以便在其他設定畫面更有效指定相關執行函數。
▶️ 寫入現時的日期時間至試算表
首先，我們練習一下使用程式來將內容寫入 Google Sheet 中。我們通過 SpreadSheetApp 的 `getActiveSheet` 方法取得現有的（也是預設的）第一張 sheet。
var ss = SpreadsheetApp.getActiveSheet();
然後所有有關這張試算表的操作都通過這 ss 來進行。例如，我們可以通過 `getRange` 指定當中某一格，再通過 `setValue` 設定值。以下代碼在 A2 格設定現時日期。
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
// set current date
var now = new Date();
ss.getRange("A2").setValue(now);
}
接著當我們按上方的運行（Run）時，我們會被要求授權使用這段程式碼於所屬的 Google Spreadsheet 檔案。
▶️ 授權我們的代碼可控制試算表
這個授權要求，是要確保我們了解所執行程式碼的風險，尤其是如果我們是執行第三方代碼，則需要了解清楚審視程式源代碼。而這是我們自己寫的，所以理應是可以相信的。
按著步驟，選擇我們的帳號，以授權使用這段 Apps Script 程式碼。
由於我們剛剛撰寫的是新建立的程式，所以 Google 會提醒我們正在授權的程式未被驗證。我們按顯示進階選項（Show Advanced），再授權我們的程式碼。
最後，按允許以完成授權。
注：從授權內容中可以得知。所授權的程式擁有很大的修改權，所以若果授權第三方程式時，請務必審視其源碼及風險。而現在，我們是授權自己的代碼，所以還算可控範圍內。
完成授權後，可以看到執行結果，而試算表亦更新了。
於試算表中，可見 A2 格寫入了執行代碼時的日期/時間。而如果只見到日期，可以於儲存格的格式中，設定為顯示日期＋時間的格式。
▶️ 於 Google App Scripts 取得 CoinGecko API
接著，我們嘗試用 `UrlFetchApp` 取得幣值結果。結果為文字格式，我們再使用 JSON.parse 將返回的文字內容變為 JavaScript 物件格式。
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
// set current date
var now = new Date();
ss.getRange("A2").setValue(now);
// Fetch Price
var url = `https://api.coingecko.com/api/v3/simple/price?ids=likecoin&vs_currencies=usd`;
var res = UrlFetchApp.fetch(url);
var content = res.getContentText();
var data = JSON.parse(content);
console.log(data);
}
執行結果如下，若成功，我們可以在代碼編輯器下方查看結果。
▶️ 於 Google Apps Script 使用 CoinGecko 的問題
當我們使用 Google Apps Script 連接 CoinGecko API 時，會有大半機會得到以下錯誤。
原因是 CoinGecko API 有使用限制：
4.2 Rate limit for the CoinGecko API is 8 calls each second per Internet Protocol ("IP") address, although such rate limit may be varied by CoinGecko at any time in its sole discretion without notice or reference to you or any Users. You agree not to exceed or circumvent (or make any attempts thereto) the aforesaid rate limitation, limitations on the calls and use of CoinGecko API as may be implemented by CoinGecko from time to time in its sole discretion (without any notice or reference to you), or otherwise use the CoinGecko API in a manner that can be anticipated to exceed reasonable request volume, constitute excessive or abusive usage, or otherwise fail to comply or is inconsistent with any part of this API Terms, the API Documentation, our Privacy Policy, our Website Terms of Use, or the limitations of your selected usage plan.
對於同一個 IP 地址有限制。而由於所有 Google Apps Script 都使用同一組 IP 地址，所以很有可能我們和其他使用者分享這個限制。故得出以下 CoinGecko 返回錯誤的信息。長遠解決之計為使用其他 API 取得幣值，屆時只要按文檔更新連接 API URL 及處理好返回欄位結構，其餘邏輯思維將類同。
▶️ 規劃我們的 Prices 試算表
成功取得 $LIKE 值後，我們的目標是動態的取得各幣種列表，再一次性取得這些幣種的幣值。結構如下，B 欄由 B2 開始，為 CoinGecko API 所使用的名稱，由我們於試算表中填上。而 C 欄對應 C2 開始，為每個幣種由程式所取得的幣價結果，由我們的程式填上。
而由於 API 可能因超過限制而未能返回值，所以我亦在旁加入三個時間儲存格，由程式於啟動時、執行時、執行成功後分別填上時間，以清晰所顯示幣價為何時的價值。
取得 B 欄的名稱列表
為著 B 欄，我們使用 `getRange("B2:B99")` 來取得這欄的所有值，並加到 JavaScritp Array 列表中。由於此段程式較為獨立，我另起了一個函數取得這個列表。
function getCoinNames() {
var ss = SpreadsheetApp.getActiveSheet();
range = ss.getRange("B2:B99");
coinNames = []
for (var coinName of range.getValues()) {
if (coinName[0] != "") {
coinNames.push(coinName[0]);
}
}
return coinNames;
}
接著修改主要的 myFunction 函數。把取得的名稱以「,」逗號連接並交予 CoinGecko API 的網址。
var coinNames = getCoinNames();
var url = `https://api.coingecko.com/api/v3/simple/price?ids=${coinNames.join(",")}&vs_currencies=usd`;
例如若我們填上 BTC, ETH, LIKE, OSMO, CRO 等五個幣種，則其 API URL 為：
所返回的 JSON 結構如下：
在取得這些值後，我們再逐格填上回試算表。注意的是我們使用 for i in coinNames，JavaScript 的這個 i (index) 是由 0 開始。而試算表是從 1 開始，我們的內容由 B2, C2 開始，故對每一格，我們使用 `getRange(i*1+1+1, 3)` 來定位， i*1 是確保我們使用數字而非文字。3 為 C 欄。
for (var i in coinNames) {
var coinName = coinNames[i];
var price = data[coinName]["usd"];
// i starts from 0, +1 to starts from 1,
// rows start from 2 (+1)
range = ss.getRange(i*1+1+1, 3);
range.setValue(price);
}
此時，整個 myFunction 函數如下。
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
// set date
var now = new Date();
ss.getRange("G2").setValue(now);
var coinNames = getCoinNames();
var url = `https://api.coingecko.com/api/v3/simple/price?ids=${coinNames.join(",")}&vs_currencies=usd`;
var res = UrlFetchApp.fetch(url);
var content = res.getContentText();
var data = JSON.parse(content);
console.log(data);
for (var i in coinNames) {
var coinName = coinNames[i];
var price = data[coinName]["usd"];
range = ss.getRange(i*1+1+1, 3); // i starts from 0, +1 to starts from 1, rows start from 2 (+1)
range.setValue(price);
}
// set completion date
var now = new Date();
ss.getRange("G3").setValue(now);
}
若手動執行，除了偶而遇上限制錯誤外，執行成功時便可以得出以下試算表結果。
▶️ 執行代碼的觸發事件
我們可以設定不同的方式來觸發我們剛剛完成的程式。在 Apps Script 左邊側欄，選擇 Triggers 觸發器設定。
可見現時未有觸發器。我們可以按右下方的按鈕添加。
按下添加觸發器後，可以選擇從試算表事件觸發或按時間定時觸發。
若選擇從試算表事件觸發，可以選擇開啟檔案時、修改時、結構改變時、有新提交時執行。
而若選擇按時間（Time-driven），可以短至每分鐘自動觸發。我對價格沒有太敏感，且礙於 CoinGecko API 所限，設定太密也沒多少用。所以我設定了每小時更新一次。
例如我的試算表設定了開啟時、有任何數值修改時、及每小時自動更新。
最終程式碼成果
源代碼：
function getCoinNames() {
var ss = SpreadsheetApp.getActiveSheet();
range = ss.getRange("B2:B99");
coinNames = []
for (var coinName of range.getValues()) {
if (coinName[0] != "") {
coinNames.push(coinName[0]);
}
}
return coinNames;
}
function myFunction() {
var ss = SpreadsheetApp.getActiveSheet();
// set date
var now = new Date();
ss.getRange("G2").setValue(now);
var coinNames = getCoinNames();
var url = `https://api.coingecko.com/api/v3/simple/price?ids=${coinNames.join(",")}&vs_currencies=usd`;
var res = UrlFetchApp.fetch(url);
var content = res.getContentText();
var data = JSON.parse(content);
console.log(data);
for (var i in coinNames) {
var coinName = coinNames[i];
var price = data[coinName]["usd"];
range = ss.getRange(i*1+1+1, 3); // i starts from 0, +1 to starts from 1, rows start from 2 (+1)
range.setValue(price);
}
// set completion date
var now = new Date();
ss.getRange("G3").setValue(now);
}
— 麥麥寫的 麥誠 Makzan，2022-02-06。