Post Views: 20,247

เนื่องด้วยการจัดเก็บข้อมูลในลักษณะของ Spreadsheet ทำได้ง่าย หน่วยงานต่าง ๆ จึงมีการนำใช้ในการจัดเก็บและประมวลผลข้อมูล โดยโปรแกรมที่นิยมนำมาใช้งาน เช่น Microsoft Excel และ Google Sheet สำหรับบทความนี้จะโฟกัสไปที่ Google Sheet นะครับ เมื่อเรามีข้อมูลมากขึ้นและต้องการเผยแพร่ออกไป วิธีที่ง่ายที่สุดที่เราสามารถทำได้คือ การแชร์ไฟล์ หรือ การเผยแพร่ไปยังเว็บไซต์ เพื่อให้ผู้อื่นสามารถเข้าถึงข้อมูลเหล่านี้ได้ แต่สองวิธีข้างตนอาจไม่สะดวกนักหากเป็นการแบ่งปันข้อมูลระหว่างระบบด้วยกันเอง โดยมากการแบ่งปันข้อมูลระหว่างระบบนิยมใช้หลักการแบบ API ที่ส่งมอบข้อมูลในรูปแบบของ JSON มากกว่า

Google Sheet มี API หรือ ชุดคำสั่งที่ทำให้นักพัฒนาสามารถเข้าถึงเอกสาร Google Sheet ได้ ผ่านการเขียนโปรแกรมด้วยภาษาต่าง ๆ เช่น JavaScript, .NET, Google Apps Script และ อื่น ๆ

ขั้นตอน

  1. เตรียมไฟล์ Google Sheet สำหรับเป็นแหล่งข้อมูล
  2. เขียน Google Apps Script (GAS) เพื่อให้บริการข้อมูล
  3. การ Publish GAS Project และ ทดสอบร้องขอข้อมูล

การเตรียม Google Sheet

การเตรียมไฟล์ Google Sheet มีข้อเสนอแนะเบื้องต้น ดังนี้

  1. โครงสร้างของข้อมูล
    – WorkSheet => Table คือ Sheet ข้อมูลเปรียบเหมือนตารางข้อมูล 1 ชุด
    – Columns => Attributes of Records คือ คอลัมภ์ เป็นรายละเอียดที่ต้องจัดเก็บในตาราง เช่น รหัส ชื่อ สกุล หมายเลขโทรศัพท์ และ อื่นๆ
    – Rows => Data คือ ข้อมูลจริง ๆ ที่สอดคล้องกับข้อมูลในชื่อ Column โดยกำหนดให้แถวแรก เป็นส่วนหัวของตาราง
  2. กำหนดให้ข้อมูลมี Column ที่ทำหน้าที่เป็น Key ซึ่งเป็นค่าที่ไม่ซ้ำสำหรับใช้ในการเข้าถึง และ การสร้างความสัมพันธ์ เช่น รหัส เป็นต้น

เขียน Google Apps Script (GAS) เพื่อให้บริการข้อมูล

  1. เปิดโปรแกรมแก้ไขสคริปต์ สามารถเรียกผ่าน https://script.google.com/home หรือ ที่ Google Sheet เลือกเมนู เครื่องมือ > โปรแกรมแก้ไขสคริปต์ ตั้งชื่อโครงสร้างให้เรียบร้อย
  2. ที่ไฟล์แรกเริ่ม code.gs หรือ รหัส.gs ให้ลบโค้ดเริ่มต้นออกทั้งหมด และ วางโค้ดส่วนนี้ลงไป
function config() {

  var sheetID = "รหัสของ Google Sheet"; //ดู Note [1] ด้านล่าง
  
  var links = [];
  links["ชื่อข้อมูล1"] = "ชื่อ Sheet1";   //ตัวอย่าง links["students"] = "Student";
  links["ชื่อข้อมูล2"] = "ชื่อ Sheet2";

  return { sheetID: sheetID, links: links };
}


function doGet(e) {
  var configs = config();
  var params = e.parameter;
  var query = params.q != undefined ? params.q : "";
  var sheetName = configs.links[query];
  var dt = [];

  if (sheetName != null) {

    dt = readWorkSheet(configs.sheetID, sheetName);

  }

  const jResponse = JSON.stringify(toObject(dt));
  return ContentService.createTextOutput(jResponse).setMimeType(
    ContentService.MimeType.JSON  );
}


function readWorkSheet(sheetID, sheetName) {
  var ss = SpreadsheetApp.openById(sheetID);
  var ws = ss.getSheetByName(sheetName);
  var datatable = ws
    .getRange(1, 1, ws.getLastRow(), ws.getLastColumn())
    .getValues();

  return datatable;
}


function toObject(dt) {
  //กำหนดให้แถวแรกเป็น header
  const header = [];
  const objs = [];

  if (dt != null && dt.length > 0) {
    for (var i = 0; i < dt[0].length; i++) {
      header.push(dt[0][i]);
    }

    for (var i = 1; i < dt.length; i++) {
      var row = dt[i];
      var newObj = {};
      for (var j = 0; j < row.length; j++) {
        newObj[header[j]] = row[j];
      }
      objs.push(newObj);
    }
  }

  return objs;
}

คำอธิบายโค้ดโปรแกรม

function config()
– ใช้สำหรับตั้งค่าการทำงาน โดยให้คัดลอก รหัสของ Google Sheet ที่เป็นแหล่งข้อมูลมาแทนที่ใน ” ” ดู Note [1] ด้านล่าง
– ในส่วนของ links “ชื่อของข้อมูล” ที่อยู่ภายใน links[ ] จะถูกใช้อ้างถึงในการเรียกใช้งาน คือ https://google-apps-script-url/exec?q=ชื่อของข้อมูล
– “ชื่อ Sheet” ให้ระบุชื่อของ Worksheet
– links[“ชื่อข้อมูล1”] = “ชื่อ Sheet1” อธิบายคือ เมื่อมีการสอบถามข้อมูล q=ชื่อข้อมูล1 จะใช้ข้อมูลจาก sheet ที่ชื่อ “ชื่อ Sheet1”

function doGet(e)
– เป็น function หลักเมื่อผู้ใช้งาน ร้องขอข้อมูล (HTTP_GET)
– รับค่า query string ที่ชื่อว่า q จาก request มาพักไว้ที่ตัวแปร query
– เอาค่า q หรือ query ไปหาความสัมพันธ์กับชื่อ Sheet
– ทำการอ่านข้อมูลในชีตที่กำหนดผ่านฟังก์ชั่น readWorkSheet(sheetID, sheetName) ได้ผลลัพธ์เป็น DataTable
– นำ DataTable ไปแปลงเป็น Array of Object ด้วยฟังก์ชั่น toObject(dt)
– ส่งออกข้อมูลด้วยการแปลง Array of Object เป็นข้อมูลแบบ JSON

function readWorksheet(sheetID, sheetName)
– ภายในฟังก์ชั่นนี้จะมีการเรียกใช้ Google Sheet API เพื่อเข้าถึง Spreadsheet และ Sheet
– ใช้คำสั่ง getRange ในการดึงข้อมูลในขอบเขตที่กำหนดคือ ตั้งแต่ แถวแรก คอลัมภ์แรก ถึง แถวและคอมลัมภ์สุดท้าย
– getValues เป็นการดึงค่าในขอบเขต (Range) โดยยังให้ความสำคัญกับประเภทของข้อมูลในแต่ละ cell ด้วย ทั้งนี้สามารถเปลี่ยนเป็น getDisplayValues หากต้องการให้ข้อมูลทั้งหมดเป็นเพียงข้อความ

function toObject(dt)
– รับข้อมูลที่เป็นรูปแบบของตาราง มาแปลงเป็น object

การ Publish GAS Project

– การ Publish ทำได้ด้วยการคลิกที่ ปุ่ม การทำให้ใช้งานได้ > การทำให้ใช้งานได้รายการใหม่ และ กำหนดสิทธิ์ในการเข้าถึงบริการนี้

– กดปุ่ม การทำให้ใช้งานได้ จะได้หน้าจอ แสดงลิงค์ที่สามารถนำไปให้บริการ ให้ทำการ คัดลอก URL ที่ปรากฎไปเรียกใช้ผ่าน Browser โดยให้เพิ่ม ?q=ชื่อข้อมูล ที่ด้านท้าน URL 

https://script.google.com/macros/s/AKfycbzx2Ggf============29rfg0u7AYW9H/exec?q=students

สรุป

บทความนี้เป็นการนำเสนอแนวทางในการนำ Google Sheet มาประยุกต์ใช้ในการจัดเก็บข้อมูล และให้บริการข้อมูลในลักษณะของ API อย่างง่าย เพื่อให้นักพัฒนาสามารถมาเชื่อมโยงข้อมูลเหล่านี้ได้

Note

[1] รหัสของ Google Sheet สามารถคัดลอกได้จากตำแหน่งนี้ของ URL

[2] การเปิดโหมดทดสอบ ต้องมีการ Publish Project ไปก่อนแล้ว 1 ครั้ง

Reference

NONTAPON RATTANAPITTAYAPORN

More Posts

Author Avatar

NONTAPON RATTANAPITTAYAPORN

วิธีการจัดการสมาชิกในอีเมล์กลุ่มโดยผู้ใช้
เพิ่มความปลอดภัยให้บัญชี Google ด้วยการเปิดใช้การยืนยันตัวตน 2 ขั้นตอน (2SV)

Discussion

Leave a Comment