[GoogleSheet] 透過 Query 取出動態欄位的極限值，以 GOOGLEFINANCE 為例

故事是這樣的，目前透過 GoogleSheet 維護一些資訊，有些服務可以立即幫你長很多筆資料，但有時只需要其中一項極值資訊。

例如 GOOGLEFINANCE - https://support.google.com/docs/answer/3093281 可以一口氣列出幾定天數的數據，如：

=GOOGLEFINANCE("NASDAQ:ZM", "low","1/1/2020",365,"DAILY")

若只想看極值，就可以再透過 Query - https://support.google.com/docs/answer/3093343 處理

最低極值:

=QUERY(GOOGLEFINANCE("NASDAQ:ZM", "low","1/1/2020",365,"DAILY"),"Select Min(Col2) label Min(Col2)''",1)

最高極值:

=QUERY(GOOGLEFINANCE("NASDAQ:ZM", "high","1/1/2020",365,"DAILY"),"Select Max(Col2) label Max(Col2)''",1)

收工！真是美好的一天

[Javascript] Google Sheets 與 Google app script - 處理 JSON API data

之前看到別人用 Google docs - sheets 與表單製作點飲料的功能（有點年紀的大概都會想到訂便當系統？），才發現 Google Sheets 其實有提供像 Excel - VBA 這種制定功能的架構，真是相見恨晚！最近幾年都改用Google docs做了很多事，面對數據整理也都在透過 Google sheets API 匯出後再整理回去，若有些很簡單的功能，不見得需要這樣做了！

舉個例：撈別人家的 API 資料，取出來參考。來個更精準的需求：查看某銀行的幣值變化或是某交易平台的虛擬幣價格。

這時就需要"存取網路"跟"JSON"處理方式，很佛心的，有人已提供了：

Import JSON into Google Sheets, this library adds various ImportJSON functions to your spreadsheet - https://github.com/bradjasper/ImportJSON

此目的是做成很通用的工具，但有時候，有些欄位資料想要自行客製化，那就寫一點 code 吧

1. 存取網路：

var jsondata = UrlFetchApp.fetch(url);

2. 處理 json format：

var object   = JSON.parse(jsondata.getContentText());

剩下的，對會寫程式的，就很簡單了，一切就是寫 javascript 啦，像是將 timestamp 轉 date：

var date_obj = new Date(input_timestamp_value * 1000);
var date_formated =
        date_obj.getFullYear() + '/' + (date_obj.getMonth() < 10 ? '0' : '' ) +(date_obj.getMonth()+1) +'/'+ (date_obj.getDate() < 10 ? '0' : '' ) + date_obj.getDate()
          ' ' + (date_obj.getHours() < 10 ? '0' : '') + date_obj.getHours() + ':' + date_obj.getMinutes() + ':' + (date_obj.getSeconds() < 10 ? '0': '') + date_obj.getSeconds();

後續在 Google app script 包裝成一個 function 後，並定義註解描述後，在 Google sheets 就可以用了！

/**
 * get_json_info
 *
 * @param {url}
 *
 * @customfunction
 **/
function get_json_info(url,fieldname) {
  var jsondata = UrlFetchApp.fetch(url);
  var object   = JSON.parse(jsondata.getContentText());
  return object[fieldname];
}

function main() { // 用來在 Google app script 裡 debug，可以指定跑此函數
  get_json_info("https://ipinfo.io/json","ip");
}

在 Google sheets 就簡單寫

=get_json_info("https://ipinfo.io/json","ip")

收工 XD