因為最近在準備結婚的事，使用了google form來統計賓客資料。用google apps script寫了一個小程式(參考
https://gist.github.com/mhawksey/1442370)，透過
http://tools.5432.tw/zip5api.html提供的API自動查詢3+2碼郵遞區號。分享給大家。
由於API作者有提到不要連續查詢，最好間隔一秒，因此請不要更動code內部sleep的部分。否則會被API作者封鎖來源網址。作者已經將google script的來源封鎖，這讓我在一開始的時候花了不少功夫debug。所以使用者必須提供一個web server供redirect來源繞過封鎖。
google apps script
function onOpen() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "fix郵遞區號",
functionName : "findpostcode"
}];
spreadsheet.addMenu("fix郵遞區號", entries);
};
/**
* Runs when the add-on is installed; calls onOpen() to ensure menu creation and
* any other initializion work is done immediately.
*
* @param {Object} e The event parameter for a simple onInstall trigger.
*/
function onInstall(e) {
onOpen(e);
}
/**
* Opens a sidebar. The sidebar structure is described in the Sidebar.html
* project file.
*/
function findpostcode() {
var o =
4; //4為修正前地址的欄位，可依需求修正
var a =
14;
//將修正後之地址輸出到第14欄，可依需求修正
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Form Responses 1");
var TIDrange = sheet.getRange(1,1,sheet.getMaxRows(), a);
var rows = sheet.getLastRow();
for (i = 2; i <= rows; i++)
{
var dirtyaddress = TIDrange.getCell(i,o).getValue().toString().replace(/ /g,"");
var address = dirtyaddress.split(/^[0-9]*/)[1]; //去除已填寫之郵遞區號（可能為3碼）
var target = TIDrange.getCell(i,a).getValue().toString().replace(/ /g,"");
if (target == "") {
if (typeof address === 'undefined') {address = dirtyaddress;}
var response = UrlFetchApp.fetch("
這邊放入轉址的網站"+address)
Utilities.sleep(1000); //此行勿動
var dataAll = JSON.parse(response.getContentText());
var newaddr = dataAll.new_adrs;
sheet.getRange(i,a).setValue(newaddr);
}
}
webserver PHP
<?php
if (isset($_GET['link'])) {
// echo $_GET['link'];
echo file_get_contents($_GET['link']);
}else{
// Fallback behaviour goes here
}
?>
由於API使用url參數會自動decode % encode，所以一開始遇到一點麻煩，後來發現我不用去store資料，只要直接echo就OK。
將webserver PHP放在webserver如apache下，假設檔名為phpexample.php，domain name為neverregret.net，則google apps script紅字部分
這邊放入轉址的網站為
http://neverregret.net/phpexample.php?link=http://zip5.5432.tw/zip5json.py?adrs=
將google apps script部分複製並貼在工具-指令碼編輯器的內容（將內容先全部清空）
儲存後工具列會出現fix郵遞區號的選項，點選後執行即可使用。
查詢流程為如果尚未修正則修正（修正欄位不為空白），已修正則不處理，所以如果地址有變要記得將修正後欄位也一併清除。這樣做是為了不用每次都全部重新修正。
如有親友想使用請直接與我聯繫，我可以提供我自己的webserver供使用。
參考資料
API
http://tools.5432.tw/zip5api.html
unicode decode encode (didn't use in the end)
http://www.cnblogs.com/txw1958/archive/2013/04/20/unicode-encode-decode.html
http://stackoverflow.com/questions/16943281/javascript-google-scripts-how-to-get-the-title-of-a-page-encoded-with-iso-88
http://stackoverflow.com/questions/332872/encode-url-in-javascript
http://stackoverflow.com/questions/23496750/how-to-prevent-python-requests-from-percent-encoding-my-urls
http://blog.bestdaylong.com/2010/09/phputf8get.html
http://www.ewdna.com/2008/12/online-urlencoderutf-8.html
http://stackoverflow.com/questions/17342671/pass-a-percent-sign-in-a-url-and-get-exact-value-of-it-using-php
http://blog.lunatech.com/2009/02/03/what-every-web-developer-must-know-about-url-encoding
PHP
http://stackoverflow.com/questions/5884807/get-url-parameter-in-php
http://stackoverflow.com/questions/959063/how-to-send-a-get-request-from-php
determine undefine
http://stackoverflow.com/questions/2647867/how-to-determine-if-variable-is-undefined-or-null
JSON array
http://stackoverflow.com/questions/14408281/access-elements-in-json-object-like-an-array
javascript split
http://stackoverflow.com/questions/650022/how-do-i-split-a-string-with-multiple-separators-in-javascript
google apps script
https://gist.github.com/mhawksey/1442370
http://ctrlq.org/code/19871-get-post-requests-google-script
https://developers.google.com/apps-script/guides/html/
https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#getRequest(String,Object)
https://discuss.ninjablocks.com/t/google-script-for-parsing-json-to-google-spreadsheets/1088/4
https://developers.google.com/apps-script/reference/spreadsheet/range
http://stackoverflow.com/questions/11334296/google-docs-script-set-cell-value
google spreadsheet
https://support.google.com/docs/answer/3093335?hl=zh-Hant
javascript
http://stackoverflow.com/questions/2499567/how-to-make-a-json-call-to-a-url/2499647#2499647
http://stackoverflow.com/questions/18910939/how-to-get-json-key-and-value-in-javascript
http://stackoverflow.com/questions/12070631/how-to-use-json-file-in-html-code
JSON test site
http://developers.squarespace.com/view-json-data/
http://base-template.squarespace.com/blog/?format=json-pretty
