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

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

完成畫面: 鹹酥雞查詢訂單
google sheetapiquery

查詢訂單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 “google sheet getSheetValues”)

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

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

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那段寫得比較快,有問題再麻煩留言,感謝。