Google sheet 試算表表單串接api

要用到活動或是問卷，相信大家最常會用到 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

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 不安全

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

把上面的 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 小時可以搞定。

接續文章: 客戶讀取、取消訂單

google excel 試算表 實作功能