Google Sheet RESTful API 試算表表單

嗨，接下來要來分享 google excel api 之前未完成的部分，前面介紹了建立 api，讓我們能在 google excel 新資料，但是透過 api 管理資料還差讀取、更新表單資料，簡單講就是 API 的 CRUD。

後面會完成 API 串接做查詢、更新資料，還有簡單的前端操作畫面。

查詢訂單 api

鹹酥雞訂購的填表，還需要增加一個功能，就是客人需要查詢訂單的狀態，有沒有成功送出訂單，還有老闆有沒有幫我確認製作，不然跑去現場然後老闆忘記做餐…。

會是用填表姓名還有電話查詢。這邊用比較特別的處理方法，我們直接拉出電話陣列，直接比對電話號碼是否存在，有存在號碼會再比對姓名是否相符。才會回拋出資料。我沒特別研究 BigQueryDataSourceSpecBuilder，貌似可以直接下 query 語法。

這邊改用 doPost，首先起手一樣用 getSheets 先取得我們的 tab，使用 getSheetValues 來拿到整包資料。拿到的資料會是陣列，我們可以跑迴圈來找出每一筆的 name、phone，有沒有在陣列內，有的話才要增加到回傳的陣列。最後回傳資料會是一包陣列裡面會是{data: [ ], index: x}，index 方便我們後續做更新的操作。

getSheetValues(startRow, startColumn, numRows, numColumns)

ps.儲存發布記得要再新增版本，這邊我卡了 1 小時…。

function doPost(e) {
  var params = e.parameter;
  var name = params.name;
  var phone = params.phone;

  var SpreadSheet = SpreadsheetApp.openById(
    "1JbXssixJaqTL_BMgnmOiMxLyAZm0jOkw173VdewqO9c"
  );
  var Sheet = SpreadSheet.getSheets()[0];

  var LastRow = Sheet.getLastRow();
  var data = [];

  // get All data as Array
  // first row, first column, last row, seven columns
  var listAll = Sheet.getSheetValues(1, 1, LastRow, 7);

  // find phone and name mapping data and Push Array
  for (var i = 0; i < list.length; i++) {
    if (listAll[i].indexOf(phone) === 1 && listAll[i].indexOf(name) === 0) {
      data.push({ data: listAll[i], index: i + 1 });
    }
  }
  // return data as JSON
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(
    ContentService.MimeType.JSON
  );
}

google sheet getSheetValues

這樣就完成了查詢訂單的 api，當我們用 post 請求 api，並且帶上 name、phone 的參數，就會幫我們查詢並回傳符合比對的資料。

建議安裝 postman，方便直接測試 api。

更新訂單 api

客人送出訂單後，可能需要取消訂單，這時候就需要對資料更新了。我們可以搭配查詢 api，讓客人看到自己的訂單，再對訂單更新狀態。我們用前面做好的 doPost function，直接擴展增加更新的功能。

增加 action，作為查詢、刪除的判斷，再稍微整理一下拆分共用的邏輯，把執行部分打包成 function。建立 updateData，專門更新訂單的狀態，我們取得 index，直接查詢這個 row 的資料，拿請求的 name、phone 直接比對是否符合，符合的話才讓使用者可以更新訂單狀態。

另外我們定義處理狀態 0 代表訂單取消，1 代表完成訂單。

function doPost(e) {
  var params = e.parameter;
  var name = params.name;
  var phone = params.phone;
  var status = params.status;
  var index = params.index;
  var action = params.action;
  
  var SpreadSheet = SpreadsheetApp.openById("1JbXssixJaqTL_BMgnmOiMxLyAZm0jOkw173VdewqO9c");
  var Sheet = SpreadSheet.getSheets()[0];
  var data = '';
  if (action === 'query') {
    data = queryData(Sheet, name, phone);
  } else if (action === 'delete'){
    data = updateData(Sheet, name, phone, status, index);
  }
  return ContentService.createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

function queryData (Sheet, name, phone){
  var LastRow = Sheet.getLastRow();
  
  var data = [];
  
  var listAll = Sheet.getSheetValues(1, 1, LastRow, 7);
  
  for(var i = 0;i < listAll.length; i++){
    if(listAll[i].indexOf(phone) === 1 && listAll[i].indexOf(name) === 0){
      data.push({data: listAll[i],index: i + 1 })
    }
  }
  return data;
}

function updateData(Sheet, name, phone, status, index){
  var checkName = Sheet.getSheetValues(index, 1, 1, 1) == name;
  var checkPhone = Sheet.getSheetValues(index, 2, 1, 1) == phone;
  if(checkName && checkPhone){
    Sheet.getRange(index, 7).setValue(0);
  
    return 'success';
  }
  return null;
}

前端畫面

這樣就完成了查詢、更新訂單的 api 了，剩下就是網頁前端部分，網頁需求是讓使用者輸入姓名、電話查詢訂單，或是查詢後可以取消訂單。

前端主要的 function 就這些，主要就是查詢拉資料，更新 List 清單，點擊取消訂單的按鈕，請求 API 更新狀態等等。

前端畫面比起 API 來說，就比較單純這邊不多贅述了。要用 vanilla javascript、jquey、或 react、vue，看你喜歡都可以做到一樣功能。

      <div class="col-lg-12 text-center">
        <h1 class="mt-5">查詢訂單</h1>
        <div class="form-group col-xs-12">
          <label for="pwd">姓名:</label>
          <input type="text" class="form-control" id="name">
        </div>
        <div class="form-group col-xs-12">
          <label for="pwd">電話:</label>
          <input type="number" class="form-control" id="phone">
        </div>
        <div class="form-group col-xs-12">
          <button type="button" id="send" class="btn btn-primary col-xs-12">送出查詢</button>
        </div>
        <div class="form-group col-xs-12">
          <table class="table table-striped" style="display: none;">
            <thead>
              <tr>
                <th>Name</th>
                <th>Phone</th>
                <th>Time</th>
                <th>Item</th>
                <th>Price</th>
                <th>Status</th>
                <th>Update</th>
              </tr>
            </thead>
            <tbody id="dataView">
            </tbody>
          </table>
        </div>
        <div class="col-xs-12 text-center">純屬線上訂購 demo</div>
      </div>
...
  <script>
    $(function(){
        $('#send').click(function(e){
            var status = true;
            var name = $('#name').val();
            var phone = $('#phone').val();
            $('input').focus(function(){
              $(this).css('border','');
            });
            if(name == ''){
              $('#name').css('border','1px solid #ff0000');
              status = false;
            }
            if(phone == ''){
              $('#phone').css('border','1px solid #ff0000');
              status = false;
            }

            if(status){
              var data = {
                'name' : name,
                'phone':phone,
                'action': 'query'
              }
              sendQuery(data);
            }
      });
    });
    function handleData(response){
        var content = '';
        response.forEach(element => {
          var [name, phone, time, item, price,, status] = element.data;
          var index = element.index;
          var statusWording = convertStatus(status);
          content +=
              `<tr>
                <td>${name}</td>
                <td>${phone}</td>
                <td>${new Date(time)}</td>
                <td>${item}</td>
                <td>${price}</td>
                <td class="status">${statusWording}</td>
                <td>
                  ${status !== '' ? '-' :
                    `<button type="button" onclick="updateStatus('${name}', '${phone}', ${index}, this)" class="btn btn-primary">取消訂單</button>`
                  }
                </td>
              </tr>`
        });
        if (content) {
          document.getElementsByClassName('table-striped')[0].style.display = 'table';
        } else {
          alert('查無資料');
        }
        var dataView = document.getElementById('dataView');
        dataView.innerHTML = content;
    }
    function convertStatus(status){
        var text = '';
        switch (status) {
          case '':
            text = '尚未處理';
            break;
          case 0:
            text = '訂單已取消';
            break;
          case 1:
            text = '訂單處理中';
            break;
          case 2:
            text = '完成製作';
            break;
          case 3:
            text = '完成交易';
            break;
          default:
            break;
        }
        return text;
    }
    function sendQuery(data){
      $.ajax({
        type: "post",
        url: "https://script.google.com/macros/s/AKfycbz3fU3FVDmIQehtWX6ecKuV_A67lE64FEQ-ekmOZ_jZDyY96z0/exec",
        data: data,
        dataType: "JSON",
        success: function (response) {
          handleData(response);
          $('#name').val('');
          $('#phone').val('');
        }
      });
    }
    function updateStatus(name, phone, index, that){
      var data = {
        name: name,
        phone: phone,
        action: 'delete',
        index: index,
        status: 0
      };
      $.ajax({
        type: "post",
        url: "https://script.google.com/macros/s/AKfycbz3fU3FVDmIQehtWX6ecKuV_A67lE64FEQ-ekmOZ_jZDyY96z0/exec",
        data: data,
        dataType: "JSON",
        success: function (response) {
          if(response === 'success'){
            $(that).parent().siblings('.status').text(convertStatus(0));
            $(that).remove();
          } else {
            alert('更新失敗！');
          }
        }
      });
    }
  </script>

鹹酥雞查詢訂單範例

可以直接右鍵，查看原始碼，直接看我怎麼寫前端畫面的。

心得

會寫進階查詢的 API 是因為平常滿多網友有問我，所以乾脆直接寫一篇文章分享。現在工作就沒繼續接觸 sheet script API，所以前置花了點時間研究語法，我查詢資料的做法偏向暴力解，拉整包跑迴圈比對，如果有人研究出 select query 的作法，再麻煩跟我分享一下，感激不盡。

對了，其實還缺老闆查詢全部資料、更新訂單狀態，這要再加上會員登入權限的 api，要擋有權限的帳號才可以查詢全部的資料，這個就讓我富奸一下下次再做吧 XD。

api 那段寫得比較快，有問題再麻煩留言，感謝。