將Google試算表當作簡易資料庫,利用Google apps cript 在網頁上操作查詢
若我有一試算表資料
縣市 status aqi 新北市 普通 62 臺北市 普通 66 桃園市 普通 61 臺中市 良好 44 臺南市 普通 56 高雄市 普通 77 宜蘭縣 普通 74
要如何在web上操作查詢, 要分成前後端來看
後端是google apps script
function doGet(e) {
let params = e.parameters;
let url = ScriptApp.getService().getUrl();
// return HtmlService.createHtmlOutput(url);
return HtmlService.createTemplateFromFile('index')
.evaluate();
}
function getMsg() {
return 'Hello,world';
}
function getItems(name){
Logger.log("query name:"+name);
let url = 'https://docs.google.com/spreadsheets/d/14bY56g1SuMleCfY-67pBkfJi0j7exj04aLvOYopSEj0/edit?usp=sharing';
let SpreadSheet = SpreadsheetApp.openByUrl(url);
let sheet = SpreadSheet.getSheets()[0];
let lastRow = sheet.getLastRow();
let items = [];
//init get all items
if(name === "init"){
for(let i=2; i<lastRow+1; i++){
let item = {};
item.name = sheet.getSheetValues(i, 1,1,1)[0][0];
item.status = sheet.getSheetValues(i, 2,1,1)[0][0];
item.aqi = sheet.getSheetValues(i, 3,1,1)[0][0];
items.push(item);
}
} else {
for(let i=2; i<lastRow+1; i++){
let item = {};
item.name = sheet.getSheetValues(i, 1,1,1)[0][0];
item.status = sheet.getSheetValues(i, 2,1,1)[0][0];
item.aqi = sheet.getSheetValues(i, 3,1,1)[0][0];
if(name === item.name){
items.push(item);
}
}
}
// Logger.log(items);
return items;
}
doGet() 是處理 http get的方式
getItems()則是讀取試算表的資料
前端面頁 index.html
<!DOCTYPE html>
<html>
<head>
<script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
<base target="_top">
</head>
<body>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css"> </script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.bundle.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
<div class="container">
<div class="row h-100 justify-content-center align-items-center">
<br>
<div id="app">
<br>
<br>
<br>
<form>
<div class="row">
<div class="col">
<select v-model="selected">
<option v-for="choice in choices" v-bind:value="choice">
{{ choice }}
</option>
</select>
</div>
<div class="col">
<button type="button" @click="getItem" class="btn btn-primary">查詢</button>
</div>
</div>
</form>
<!-- {{items}} -->
<table class="table">
<thead>
<tr>
<th scope="col">縣市</th>
<th scope="col">status</th>
<th scope="col">aqi</th>
</tr>
</thead>
<tbody>
<tr v-for="item in items">
<td>{{item.name}}</td>
<td>{{item.status}}</td>
<td>{{item.aqi}}</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<script>
//created stage
google.script.run.withSuccessHandler(onCreated).getMsg();
google.script.run.withSuccessHandler(onCreatedItems).getItems("init");
var app = new Vue({
el: '#app',
data: {
msg: '',
items:[],
choices:[],
selected:'',
},
methods: {
getItem: function () {
console.log(this.selected);
google.script.run.withSuccessHandler(onCreatedItems).getItems(this.selected);
}
}
})
function onCreated(msg) {
console.log(msg);
app.msg = msg;
}
function onCreatedItems(items) {
console.log("query app selected:" + app.selected);
console.log("get items: "+items);
app.items = items;
if(app.selected===''){
items.forEach(item=>{
//選項不重覆
if(!app.choices.includes(item.name)){
app.choices.push(item.name);
}
})
}
}
</script>
</body>
</html>
為了要方便操作DOM, 我使用了vue.js, 最主要的語法在callback 這一段
google.script.run.withSuccessHandler(onCreatedItems).getItems("init");
參考 https://developers.google.com/apps-script/guides/html/reference/run
畫面
查詢縣市
END
