2017年12月30日 星期六

[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