ในบทความนี้ เราจะสร้างเว็บแอปที่ช่วยให้เราสามารถ เขียน อ่าน อัปเดต และลบข้อมูล ใน Google Sheets ด้วย Google App Script
1. สร้าง Google Sheets และตั้งชื่อชีตเป็น "data"
2. ไปที่เมนูส่วนขยาย เปิด Apps Script
3. สร้างไฟล์ดังนี้ code.gs, index.html, entry.js.html, read.js.html, update.js.html และ delete.js.html
code.gs
function doGet(){
return HtmlService
.createTemplateFromFile('index.html')
.evaluate()
.setTitle("CRUD Example")
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function include(filename){
return HtmlService
.createHtmlOutputFromFile(filename)
.getContent();
};
//Google sheet location and sheet name
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("data");
//newEntry function
function newEntry(sd){
const zeroPad = (num, places) => String(num).padStart(places, '0')
const rnd =Math.floor(Math.random() * 500) + 500;
const id = "E"+zeroPad(rnd,6); //unique id for each entry
let flag = 1 ;
const lr = sheet.getLastRow();
for(i = 1; i <= lr; i++){
const vid = sheet.getRange(i, 3).getValue();
if(vid == sd[1]){// checking if Email already exist.
flag = 0;
const data ="This email is already in our data base.";
return data;
}
}
if(flag==1){
sheet.appendRow([id,sd[0],sd[1],sd[2],sd[3],sd[4]]);
sheet.getRange(i, 5).setNumberFormat('@STRING@');
const data = 'Entry successfully made with entry Id:'+id;
return data;
}
};
//read function
function readId(txt){
let flag = 1 ;
const lr = sheet.getLastRow();
for(i = 1;i <= lr;i++){
const vid = sheet.getRange(i, 1).getValue();
if(vid === "E"+txt){
flag = 0;
const b1 = sheet.getRange(i, 2).getValue();
const b2 = sheet.getRange(i, 3).getValue();
const b3 = sheet.getRange(i, 4).getValue();
const b4 = sheet.getRange(i, 5).getValue();
const b5 = sheet.getRange(i, 6).getValue();
const data =["Data Fetched",b1,b2,b3,b4,b5];
return data;
}
}
if(flag==1){
const data =["ID not found.",,,,,];
return data;
}
};
//update function
function updateId(sd){
let flag = 1 ;
const lr = sheet.getLastRow();
for(i = 1;i <= lr;i++){
const vid = sheet.getRange(i, 1).getValue();
if(vid == "E"+sd[0]){
flag = 0;
sheet.getRange(i, 2).setValue(sd[1]);
sheet.getRange(i, 3).setValue(sd[2]);
sheet.getRange(i, 4).setValue(sd[3]);
sheet.getRange(i, 5).setValue(sd[4]).setNumberFormat('@STRING@');
sheet.getRange(i, 6).setValue(sd[5]);
const data ="Update successfully made.";
return data;
}
}
if(flag==1){
const data ="ID not found.";
return data;
}
};
//Delete ID
function deleteId(obj){
let flag = 1 ;
const lr = sheet.getLastRow();
for(i = 1;i <= lr;i++){
const vid = sheet.getRange(i, 1).getValue();
if(vid == "E"+obj){
flag = 0;
sheet.deleteRow(i)
const data ='Id successfully deleted.';
return data;
}
}
if(flag==1){
const data ="ID not found.";
return data;
}
};
index.html
<!DOCTYPE html>
<html>
<style>
html{
background: url('https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjay3tKitUnIbtKFPy0Mf-DwEU4VbdXudGTL48jyD3k55WY9TpWHESBAE2RrX_4yt1mrYMP9Np937GYmrFWqKKyODFzqmmKvM9SNuglX9VnChDRESiBTUWSGYNbKTnH78fq6HdW6tU3S7aW/s1600/bg-body.jpg') no-repeat center center fixed;
-webkit-background-size: cover;
-moz-background-size: cover;
-o-background-size: cover;
background-size: cover;
}
body{
display: flex;
align-items: center;
justify-content: center;
margin: 0 auto;
height: 100vh;
}
div {
width: 600px;
padding: 20px;
background-color: #DBF9FCBB;
}
table, th, td {
border-collapse: collapse;
font-family:times;
}
td,th{
width:50%;
}
input:focus,select:focus,
textarea:focus {
outline: 1px dashed green;
}
textarea{
width:98%;
height:100px;
font-family:times;
border:1px solid black;
}
select,input{
width:50%;
font-family:times;
padding:1px;
border:1px solid black;
}
input:hover{
background:yellow;
}
button{
width:15%;
font-family:times;
}
button:focus {
outline: 1px dashed green;
}
button:hover{
background:green;
color:white;
}
</style>
<body>
<div class="center">
<table border=0 style="width:100%">
<tr>
<th colspan="2">CRUD WEB APP: GOOGLE SCRIPT</th>
</tr>
<tr><td colspan="2"><hr></td>
<tr>
<td colspan="2">
ID: E <input id="id" placeholder="Enter Number....">
<button onclick="read()">Read</button>
</td>
</tr>
<tr><td colspan="2"><hr></td>
<tr>
<td>Name: <input id="name"></td>
<td>Email id: <input id="eid"></td>
</tr>
<tr>
<td>Apply for:
<select id="selected">
<option>Select </option>
<option value="Web Developer">Web Developer </option>
<option value="Form Designer">Form Designer </option>
</select>
</td>
<td>Date: <input type="date" id="date"></td>
</tr>
<tr>
<td colspan="2">Note:
<textarea placeholder="Write something....."id="note"></textarea>
</td>
</tr>
<tr>
<td colspan="2"align="center">
<button onclick="submit()">New Entry</button>
<button onclick="update()">Update</button>
<button onclick="delet()">Delete</button>
</td>
</tr>
<tr>
<td colspan="2">Response: <b id="result"></b></td>
</tr>
</table>
</div>
<?!= include('entry.js') ?>
<?!= include('read.js') ?>
<?!= include('update.js') ?>
<?!= include('delete.js') ?>
</body>
</html>
entry.js.html
<script>
function submit(){
const tx1 = document.querySelector('#name').value;
const tx2 = document.querySelector('#eid').value;
const tx3 = document.querySelector('#selected').value;
const tx4 = document.querySelector('#date').value;
const tx5 = document.querySelector('#note').value;
const sd = [tx1,tx2,tx3,tx4,tx5]; //sending data in arry
const info = document.querySelector('#result');
info.innerHTML = "Making new entry...";
function onFailure(error){
info.innerHTML ="<span style='color:red'>"+error+"</span>";
};
function onSuccess(response){
info.innerHTML = "<span style='color:green'>"+response+"</span>";
};
google.script.run.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.newEntry(sd);
};
</script>
read.js.html
<script>
function read(){
const txt = document.querySelector('#id').value;
const info = document.querySelector('#result');
if (txt === ""){
info.innerHTML = "<span style='color:red'>Please enter id to read!</span>";
}else{
function onFailure(error){
info.innerHTML = "<span style='color:red'>"+error+"</span>";;
};
function onSuccess(response){
info.innerHTML = "<span style='color:green'>"+response[0]+"</span>";;
document.getElementById('name').value = response[1];
document.getElementById('eid').value = response[2];
document.getElementById('selected').value = response[3];
document.getElementById('date').value = response[4];
document.getElementById('note').value = response[5];
};
info.innerHTML = "Reading detail...";
google.script.run.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.readId(txt);}
};
</script>
update.js.html
<script>
function update(){
const txt = document.querySelector('#id').value;
const tx1 = document.querySelector('#name').value;
const tx2 = document.querySelector('#eid').value;
const tx3 = document.querySelector('#selected').value;
const tx4 = document.querySelector('#date').value;
const tx5 = document.querySelector('#note').value;
const sd = [txt,tx1,tx2,tx3,tx4,tx5]; //sending data in arry
const info = document.querySelector('#result');
if (txt === "" || tx1 === ""){
info.innerHTML = "<span style='color:red'>Please search for the data you want to update!</span>";
}else{
function onFailure(error){
info.innerHTML = "<span style='color:red'>"+error+"</span>";;
};
function onSuccess(response){
info.innerHTML = "<span style='color:green'>"+response+"</span>";;
};
info.innerHTML = "Updating detail...";
google.script.run.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.updateId(sd);}
};
</script>delete.js.html
<script>
function delet(){
const txt = document.querySelector('#id').value;
const info = document.querySelector('#result');
if (txt === ""){
info.innerHTML = "<span style='color:red'>Please search for the data you want to delete!</span>";
}else{
function onFailure(error){
info.innerHTML ="<span style='color:red'>"+error+"</span>";
};
function onSuccess(response){
info.innerHTML = "<span style='color:green'>"+response+"</span>";
};
info.innerHTML = "Deleting detail...";
google.script.run.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.deleteId(txt);
}
};
</script>