要用到活動或是問卷,相信大家最常會用到google form,非常快速的創出填表功能,但google form存在許多限制,我沒辦法創立特別的欄位,沒辦法做出一個複雜訂餐表單,表單長太醜(痾…其實我對google表單設定不太熟悉)。

或是不希望讓使用者點擊網址離開才能填表。最後網站只能鑲上google form 的 iframe又無法追蹤填表操作動作,google analytics轉換目標也不能設定。上面這些問題都可以用google excel的api來解決。

炸雞demo

炸雞訂購網頁

google excel 試算表 實作功能

這邊要利用google 試算表 增加資料 撈取 來實作一個網頁,簡單的鹹酥雞訂餐服務,老闆也可以修改處理狀態,讓顧客可以再取餐前查詢,到底老闆有沒有幫我做好鹹酥雞勒,還是放生我了XD。

只需要搭配html + css + javascript。
(因為我家附近有間鹹酥雞,每次買都實在等太久,所以才想玩玩看。)

google sheet method 介紹

google 官方文件 spreadsheet method,裡面有非常詳盡的介紹,包括可以讀取欄位、讀取資料、排序資料、插入資料等等的功能,其實某方面來說算是功能齊全的類資料庫了。有興趣可以點開來看裡面文件。

文件裡面method一大堆,還是直接實作比較快。

STEP.1 打開google雲端硬碟 建立google excel、建立google AppScript

google creatExcel

google creatAppScript

STEP.2 選擇剛剛創立的 google excel 網址參數

xxxxxxxxxxx/edit,/edit前面這段參數
https://docs.google.com1JbXssixJaqTL_BMgnmOiMxLyAZm0jOkw173VdewqO9c1/edit#gid=0

STEP.3 打開AppScript 加上剛剛複製的編碼 以及 javascript

首先在myFunction 上加入參數,因為之後要透過call 這個api 帶入資料,下面是預期會帶入的物件名稱name、phone、time、order、price。這些都可以根據你想要帶的資料名稱變化,也可以刪減。

後面是依靠 google sheet的 method來選取表單,後面貼上你剛剛excel的編碼,再來是用.getSheets()[0]來選取第1個工作表單。這樣就成功的選取到這整個table了。

這邊先處理接到值傳進來先命名,再來選取你要填入的google excel編碼、excel tab。

ps.這邊用的是doGet function,代表這個function會被api的Get調用到,也可以用doPost,但是你串接api就要改用Post。

function doGet(e) {
//接帶入值 轉為變數 - 可根據需求修改
var params = e.parameter;
var name = params.name;
var phone = params.phone;
var time = params.time;
var order = params.order;
var price = params.price;

//這是選擇這個google excel
var SpreadSheet = SpreadsheetApp.openById("貼上你剛剛複製的編碼");
//取得 第一個表單
var Sheet = SpreadSheet.getSheets()[0];

...
}

這邊比較搞剛的是要處理訂單編號,以便於日後我們比對資料方便,剩下最重要的塞剛剛帶進來的值。就完成的簡單的插入資料function了!

  ...
//取得 LastRow = 有資料的最後一行的位置
var LastRow = Sheet.getLastRow();


// 這邊是處理訂單編號 - 我先選取 有資料最後一行 的 第6欄
var range = Sheet.getRange(LastRow, 6);
// 創立訂單編號 (最後一行的第六欄 +1)
var orderNum = parseFloat(range.getValues())+1;
//若最後一行是第1行 需要轉為訂單編號1
if(LastRow == 1){
orderNum = 1;
}

//開始寫入資料 同樣是 getRange 選擇 (行,欄) setValue 帶入值name phone time order price values進來
Sheet.getRange(LastRow+1, 1).setValue(name);
Sheet.getRange(LastRow+1, 2).setValue(phone);
Sheet.getRange(LastRow+1, 3).setValue(time);
Sheet.getRange(LastRow+1, 4).setValue(order);
Sheet.getRange(LastRow+1, 5).setValue(price);
Sheet.getRange(LastRow+1, 6).setValue(orderNum);

//寫入結束後傳回true
return ContentService.createTextOutput(true);
}

STEP.4 建立 debug.gs 來測試剛剛的 建立資料

//call
function debug() {
var Result = doGet(
{
"parameter": {
"name": "測試先生",
"phone": "0912345678",
"time": "2018/02/10 22:46:00",
'order':"鹹酥雞 * 1",
'price':"40",
}
}
);
Logger.log("Result: %s" , Result);
}

STEP.5 編輯 允許權限

ㄧ路點選允許到底即可

這邊需要點進階 前往xxx 不安全
google userright
debugRun

STEP.6 部署為網路應用程式

google deployAppScript
adjustAppscript
AppScriptapi

把上面的URL複製起來,這就是我們API的URL。

STEP.7 建立html 串接資料

快速簡單的建出了一個網頁,點擊下方的送出,然後call send function 非同步送出資料,然後成功再回傳給使用者感謝,

這個頁面的javasctipt都在處理 沒填寫資料要被擋,還有組出訂購的品量數量。


上半部為觸發call function
...
var data = {
'name' : name,
'phone':phone,
'time': filltime,
'order': order,
'price': price,
}
send(data)
}
function send(data){
$.ajax({
type: "get",
url: "https://script.google.com/macros/s/AKfycbz3fU3FVDmIQehtWX6ecKuV_A67lE64FEQ-ekmOZ_jZDyY96z0/exec",
data: data,
dataType: "JSON",
success: function (response) {
alert('感謝您的訂購!!');
}
});
}

鹹酥雞訂購 範例網頁

鹹酥雞訂購 Excel

詳細的code HTML Javasctipt + Appscript code

剩下部份就客戶讀取、取消訂單,最後連結是進階處理。

總結一下步驟:
建立excel、appscript -> 編輯 appscript -> 部署公開串接 -> 網頁前端串接api ->>> over

使用心得

目前服務的公司也經常使用google excel api,假設填表資料需要分享觀看、編輯,也不希望合作夥伴觀看公司後台,通常就會使用google excel來處理填表,優點就是創立非常快速、不需要後端幫忙,只要前端處理 css + html + javascript 大概2小時可以搞定。