嗨,接下來要來分享 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。

postmanapi post
postmanapi post

更新訂單 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; // add action filter post function 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 }) } } return data; } // update use function 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(status); 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 那段寫得比較快,有問題再麻煩留言,感謝。