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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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 代表完成訂單。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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,看你喜歡都可以做到一樣功能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
      <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那段寫得比較快,有問題再麻煩留言,感謝。