[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

[PHP] 使用 Google api client 操作 Google Spreadsheet API (Google Docs - Excel)

最近在整理資料，由於使用情境尚未明朗，想了一下就把資料塞到 Excel 來呈現，要排序、輸出跟轉 PDF 檔都 ok ，再加上有定期更新的需求，就來找一下 Google Spreadsheet API 來用用：https://developers.google.com/sheets/ ，程式擺在：

https://github.com/changyy/codeigniter-library-google-spreadsheet

由於同事比較熟 PHP ，就繼續挑 PHP 路線了 :P 沒想到 PHP 相關的 library 滿少的，大概都推薦 github.com/asimlqt/php-google-spreadsheet-client 這套了，但他採用的 API 所實作出來的有些侷限，但已經可以滿足八成的需求了！

最後為了達成自己想幹的事，在 k 完文件跟摸索後，還是自己下海包一份當作筆記（實在是官方文件都沒寫很清楚，只能自己追細節），包括的功能：

• 支援單獨使用，以及和 PHP CodeIgniter 合用
• 使用 composer.json 維護相依性：只有使用 google/apiclient: https://github.com/google/google-api-php-client
• 使用  Google Spreadsheet API v4
• 取得 Spreadsheet 清單 (使用 v3 API)
• 建立 Spreadsheet
• 修改 Spreadsheet name
• 建立 Worksheet
• 刪除 Worksheet
• 可對 Worksheet 凍結視窗(frozenRow/frozenColumn)
• 更新 Cells 資料
• 搭配 Web server (如 CodeIgniter Controller 架構)，可以完成 Google OAuth 流程，取得 Access Token 跟 Refresh Token
• 搭配 Google OAuth offline 權限，可以自動 renew access_token

整體上，若不需要建立 Spreadsheet 跟 frozenRowColumn 的功能，建議直接用 asimlqt/php-google-spreadsheet-client 即可，我只先完成自己需要的部分。