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

Image for post
Image for post
Google Apps Script

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

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

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

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

Image for post
Image for post
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…

Image for post
Image for post
เมนู 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 ชื่อองค์กร

Image for post
Image for post
Deploy as web app

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

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

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

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

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

Image for post
Image for post
ตัวอย่างข้อมูลใน 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…

Image for post
Image for post

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

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

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

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

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

cncx

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

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store