How I use G Suite (3): เขียน Web Service โดยใช้ Spreadsheet เป็นฐานข้อมูล

Saac Sornchai
Sep 26, 2019 · 4 min read

จากตอนที่ 2 ที่ใช้วิธีส่งเมล เหมาะสำหรับข้อมูลที่สรุปแน่นอน ไม่มีการเปลี่ยนแปลงแล้ว แต่ถ้าเป็นข้อมูลที่เปลี่ยนแปลงได้ล่ะ จะทำอย่างไร ตามไปส่งเมลแก้ไขเรื่อย ๆ ก็คงจะไม่ดี รวมถึงโควต้าส่งเมลก็มีจำกัดด้วย

เตรียม Google Apps Script

สร้างไฟล์ Apps Script โดยคลิกปุ่ม NEW > More > Google Apps Script

Google Apps Script

ถ้าเพิ่งใช้งานครั้งแรก จะยังไม่มีเมนูนี้ ให้เลือก + Connect more apps แล้วค้นหา Google Apps Script แล้วคลิกปุ่ม CONNECT

ถ้า Connect แล้ว จะเปลี่ยนเป็นปุ่ม RATE IT

เมื่อได้ไฟล์ Google Apps Script แล้ว จะมี function doGet(e) มาให้ในไฟล์ Code.gs

ให้ทดลองสร้างไฟล์ index.html โดยคลิกที่เมนู File > New > HTML File แล้วตั้งชื่อไฟล์ว่า index

Dialog Create File หลังจากกดเมนู File > New > HTML File

ที่ไฟล์ Code.gs ให้แก้ไข Code ดังนี้

function doGet(e) {
var template = HtmlService.createTemplateFromFile("index");
template.data = {
title: "Test Title"
}
return template.evaluate();
}

HtmlService.createTemplateFromFile(“index”) เป็นคำสั่งที่สร้าง template จากไฟล์ index.html

template.data ใช้กำหนด data ที่จะนำไปแสดงในหน้า index.html

return template.evaluate() ใช้ประมวลผลเป็นหน้าเว็บ

ลองแสดงข้อมูล title จากที่กำหนดใน template.data ในหน้า index.html

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h1><?= data.title ?></h1>
</body>
</html>

คำสั่งที่แสดง title คือ <?= data.title ?>

จากนั้นให้ publish web app โดยเลือกเมนู Publish > Deploy as web app…

เมนู Deploy as web app…

ระบุข้อความใน Project version เพื่อสร้าง version ใหม่ของ web app

Execute the app as: เลือก User accessing the web app

Who has access to the app: เลือก Anyone กรณีที่เป็น Account ของ G-Suite จะมีตัวเลือก Anyone within ชื่อองค์กร

Deploy as web app

เสร็จแล้วคลิกปุ่ม Deploy แล้วจะได้ URL ของ web app version ปัจจุบัน นำไปทดสอบใน Browser ได้

URL ของ web app version ปัจจุบัน
ทดสอบ URL ที่ได้ ผ่าน Browser จะเห็นข้อความ Test Title ที่กำหนดไว้ใน template.data

จบขั้นตอนการเตรียม Google Apps Script

การเตรียมไฟล์ Spreadsheet

สิ่งที่ต้องมีในไฟล์ Google Spreadsheet คือ column ที่มีข้อมูล Gmail ของผู้ใช้งานที่ต้องใช้ระบบนี้ โดยให้เรียงลำดับข้อมูลตาม column Gmail นี้ไว้ด้วย

ตัวอย่างข้อมูลใน Spreadsheet โดยต้องเรียงลำดับข้อมูลตาม Gmail ด้วย

และต้อง Share Spreadsheet โดยให้มีสิทธิ์ในการ View เท่านั้น

ฟังก์ชันการค้นหาข้อมูลจากไฟล์ Spreadsheet

เขียนฟังก์ชันที่ใช้หา record ที่ตรงกับ email ของผู้ที่เข้าดู

function _searchStudentDataByEmail(email) {
var sheet = SpreadsheetApp.openById("your-spreadsheet-id")
.getSheetByName("Sheet1");
var sourceData = sheet.getDataRange().getDisplayValues();
sourceData.splice(0, 1);
return _binarySearch(sourceData, email, 1);
}

เปลี่ยน your-spreadsheet-id เป็น ID ของไฟล์ Spreadsheet

sourceData.splice(0, 1) ใช้สำหรับตัดบรรทัด header row ออก

function _binarySearch(sourceData, target, colNumOfGmail) {
// binary search
var l = 0;
var r = totalStudent - 1;
var data = undefined;
while (r >= l) {
var m = Math.floor((l + r) / 2);
if (sourceData[m][colNumOfGmail] == target) {
data = {
id: sourceData[m][0],
gmail: sourceData[m][1],
name: sourceData[m][2],
time: sourceData[m][7],
room: sourceData[m][8],
number: sourceData[m][9]
};
break;
}
if (sourceData[m][colNumOfGmail] > target) {
r = m - 1;
} else {
l = m + 1;
}
}
return data;
}

การค้นหาที่เร็วและมีประสิทธิภาพในกรณีที่ข้อมูลเรียงลำดับแล้ว คือใช้ binary search ตัวอย่างฟังก์ชันที่เขียนนี้ จะรับข้อมูล (parameter sourceData) ทั้งหมดซึ่งเป็น array และรับข้อมูล email ที่ต้องการค้นหา (paramter target) สุดท้ายรับ column number ที่เป็น column ของ email (colNumOfGmail) โดยหมายเลข column เริ่มจาก 0 (column A)

ประกอบร่าง

ไฟล์ Code.gs

var totalStudent = 100;function doGet(e) {
var userEmail = Session.getActiveUser().getEmail();
var studentData = _searchStudentDataByEmail(userEmail);
if (studentData != undefined) {
var template = HtmlService.createTemplateFromFile("index");
template.data = studentData;
return template.evaluate();
}
var template = HtmlService.createTemplateFromFile("404");
template.data = {email: userEmail};
return template.evaluate();
}
function _searchStudentDataByEmail(email) {
var sheet = SpreadsheetApp.openById("your-spreadsheet-id")
.getSheetByName("Sheet1");
var sourceData = sheet.getDataRange().getDisplayValues();
sourceData.splice(0, 1);
return _binarySearch(sourceData, email, 1);
}
function _binarySearch(sourceData, target, colNumOfGmail) {
// binary search
var l = 0;
var r = totalStudent - 1;
var data = undefined;
while (r >= l) {
var m = Math.floor((l + r) / 2);
if (sourceData[m][colNumOfGmail] == target) {

data = {
id: sourceData[m][0],
gmail: sourceData[m][1],
name: sourceData[m][2],
time: sourceData[m][7],
room: sourceData[m][8],
number: sourceData[m][9]
};
break;
}
if (sourceData[m][colNumOfGmail] > target) {
r = m - 1;
} else {
l = m + 1;
}
}
return data;
}

ผมกำหนดตัวแปร global ชื่อ totalStudent แทนที่จะอ่านจำนวน record จากข้อมูลในไฟล์ เนื่องจากว่า ผมมักจะสรุปข้อมูลทางสถิติของผลคะแนนต่าง ๆ ในตอนท้ายของไฟล์ไว้ด้วย ซึ่งไม่อยากจะนำไปนับรวมเป็นจำนวน record ในข้อมูลจริง

ในฟังก์ชัน doGet(e) คำสั่ง Session.getActiveUser().getEmail(); ใช้สำหรับดู email ของผู้ที่เข้าดูข้อมูล ดังนั้นผู้ที่เข้าดูข้อมูล จะต้องเลือก account ของตนเองก่อน

ไฟล์ index.html (ปรับรูปแบบการจัดวาง และความสวยงามได้เลยครับ)

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h1>ประกาศห้องสอบ</h1>
<p><?= data.name ?></p>
<p>สอบห้อง <?= data.room ?></p>
<p>เวลาสอบ <?= data.time ?></p>
</body>
</html>

ไฟล์ 404.html (ปรับรูปแบบการจัดวาง และความสวยงามได้เลยครับ)

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
ไม่พบข้อมูลของผู้ใช้ account นี้
</body>
</html>

ลองทดสอบ

หลังจาก Deploy as web app… ใน version แรก อาจจะเข้าทดสอบโดยไม่ต้อง deploy ซ้ำ โดยใช้ URL latest code จากเมนู Publish > Deploy as web app…

เมื่อจะลองนำ URL ไปรันผ่าน Browser จะขึ้น Dialog ให้ REVIEW PERMISSIONS เนื่องจากจะต้องขอสิทธิ์ในการเข้าถึงไฟล์ใน Google Drive

REVIEW PERMISSIONS
ผลลัพธ์เมื่อ login ด้วย account ที่ตรงกับ record ใน Spreadsheet

เมื่อทดสอบเป็นที่พอใจแล้ว ก็ Deploy as web app เป็น version ใหม่ แล้วนำ URL ใหม่ไปใช้งานนะครับ

จบตอนที่ 3 เพียงเท่านี้ครับ หลังจากดองมากว่า 2 ปี 😐

ไม่มีตอนต่อไปครับ

cncx

KU’s com-sci now evolve to the next level

Saac Sornchai

Written by

เด็กน้อยที่เรียนรู้จากความไม่รู้

cncx

cncx

KU’s com-sci now evolve to the next level

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade