<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<style>
table,
th,
td {
border: 1px solid black;
}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
</head>
<body>
<h1>歡迎來到John不是shiny的資料網</h1>
<form action="javascript:writeDataToExcel();" method="post" enctype="multipart/form-data">
<div id="text-entry">
<!-- text 普通文本輸入框 -->
<!-- {"username":"請輸入姓名"} -->
姓名：<input type="text" id="Name" name="username" value="" placeholder="請輸入姓名"><br>
</div>
<br>
<!-- 單選框 radio name值必須相同-->
<div id="single-choice">
性別：<input type="radio" name="sex" value="man" checked>男
<input type="radio" name="sex" value="woman">女
</div>
<br>
<!-- 多選框 checkbox name值必須相同並且加[]-->
<div id="multi-choice">
愛好：<input type="checkbox" name="like[]" value="football" checked>足球
<input type="checkbox" name="like[]" value="basketball">籃球
<input type="checkbox" name="like[]" value="reading">看書
</div>
<br>
<!-- 文件上傳 file -->
上傳文件：<input type="file" name="file"><br>
<br>
<div id="selection">
地區：
<!-- 下拉框 select 必須要和option使用-->
<select name="add" id="">
<option value="NT" selected>新界</option>
<option value="KW">九龍</option>
<option value="HKI">香港島</option>
</select>
</div>
<br>
<!-- 文本域 textarea -->
個人簡介：
<br>
<textarea name="in" id="writingPart" cols="30" rows="5"></textarea><br>
<br>
<!-- password 密碼輸入框 -->
寫入資料密碼：<input type="password" id="setDataPassword" name="password" placeholder="請輸入密碼"><br>
<br>
<!-- 提交按鈕 -->
<input type="reset" value="重置">
<input type="submit" value="確定"><br>
</form>
<br>
<div>
取出資料密碼：<input type="password" id="getDataPassword" name="password" placeholder="請輸入密碼"><br>
<br>
<button id="getData" onclick="javascript:getData();">取資料</button>
</div>
<p id="response"></p>
<script>
const url = "https://docs.google.com/spreadsheets/d/1RG8tZMah4scRo1Imz_eelsMJk0vzC68dvGqSfTpMNGU/edit#gid=0";
const scriptUrl = "https://script.google.com/macros/s/AKfycbw-kLoc9fmsJjNFkMFwfVm9cjd8D3LD5Sl6oRY541UPOfOHg-BQz7Dxd8MPUtjZwkBA/exec";
function writeDataToExcel() {
var parameter = {
url: url ,
sheetName: "Sheet1",
method: "post",
password: $('#setDataPassword').val(),
information: JSON.stringify({
name: $('#Name').val(),
sex: [...document.querySelector("#single-choice").children].filter(item => item.checked)[0].value,
favourite: [...document.querySelector("#multi-choice").children].filter(item => item.checked).map(item => item.value).join(","),
place: document.querySelector("#selection > select").value,
intro: $("#writingPart").val(),
}),
};
document.querySelector('#setDataPassword').value = "";
$.get(scriptUrl, parameter, function(data) {
alert(data);
});
}
function getData() {
var show = $('#response');
var parameter = {
url: url,
sheetName: "Sheet1",
method: "get",
password: $('#getDataPassword').val(),
};
document.querySelector('#getDataPassword').value = "";
$.get(scriptUrl, parameter, function(data) {
try {
CreateTableFromJSON(data);
} catch(e) {
show.text(data)
}
});
}
function CreateTableFromJSON(jsonData) {
// Parse given JSON
const parsed = JSON.parse(jsonData);
// Get keys (=cells) of each items
const keys = Object.keys(Object.assign({}, ...parsed));
// Build the table header
const header = `<thead>` + keys.map(key => `<th>${key}</th>`).join("") + `</thead>`;
// Build the table body
const body = `<tbody>` +
parsed.map(row =>
`<tr>${keys.map(item =>
`<td>${row[item]}</td>`).join("")}
</tr>`
).join("") + `</tbody>`;
// Build the final table
const table = `
<table>
${header}
${body}
</table>
`;
// Append the result into #response element
document.querySelector('#response').innerHTML = table;
}
</script>
</body>
</html>