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

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

炸雞訂購網頁

google excel 試算表 實作功能

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

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

google sheet method 介紹

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

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

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

google creatExcel
google creatExcel

google creatAppScript
google creatAppScript

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

xxxxxxxxxxx/edit,/edit 前面這段參數
https://docs.google.com…1JbXssixJaqTL_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
google userright

debugRun
debugRun

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

google deployAppScript
google deployAppScript

adjustAppscript
adjustAppscript

AppScriptapi
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 小時可以搞定。