การทำ Dropdown list in google apps script แบบ Autocomplete เลือกข้อมูลจาก Sheet

บทความนี้จะอธิบายการทำ Dropdown list in google apps script แบบออโต้ เพื่ออำนวยความสะดวกในการเลือกหลายชั้น เช่น ตอนแรกเลือกตำแหน่ง ชื่อ สกุล และกลุ่มสาระการเรียนรู้ เป็นต้น ไฟล์ต้นฉบับเป็นของครูอภิวัฒน์ สอนสร้างสื่อ ซึ่งท่านเปิดกลุ่ม VIP ที่จ่ายเพียงครั้งเดียวใช้งานได้ตลอดเลย คุ้มสุดๆเลยครับ ถ้าท่านได้ทำงานที่เกี่ยวข้องกับโปรเจคต่างๆ แนะนำให้เข้ากลุ่มอาจารย์อภิวัฒน์เลยครับผม

เริ่มจากการ copy ไฟล์ Google Sheet คลิกที่นี่ จากนั้นก็คลิก ทำสำเนา

ท่านจะเห็นไฟล์ google sheet 2 ชีตด้านล่าง ชีตแรกชื่อ Data มีข้อมูลที่หัวคอลัมภ์ 4 อันดังภาพ

ส่วนอีกชีต ชื่อ list มีข้อมูล Demo อยู่ดังภาพ

ทำเป็นเว็บแอป โดยไปที่ ส่วนขยาย > Apps Script

จะเห็น Code 2 ไฟล์ คือ code.gs และ index.html

ให้ทำเป็นเว็บแอป โดยไปที่ การทำให้ใช้งานได้ > การทำให้ใช้งานได้รายการใหม่ (ขั้นตอนนี้จะทำเพียงครั้งเดียวเท่านั้น)

เลือก ทุกคน > การทำให้ใช้งานได้

ให้สิทธิ์เข้าถึง Sheet กดที่ ให้สิทธิ์เข้าถึง

เลือก อีเมล

ขั้นตอนนี้ เนื่องจากผมใช้อีเมลองค์กร จะมีหน้าตาแบบนี้ ถ้าเป็น gmail ส่วนตัวจะต้องกดที่คำว่า ขั้นสูง (หรือ Advanced) > แล้วกด go to webapp display google sheet (unsafe) แล้วค่อยกดคำว่า Allow

จากนั้นก็กด เสร็จสิ้น

เมื่อเรียบร้อยแล้วก็ไปที่ด้านบนขวา การทำให้ใช้งานได้ > การนำการทดสอบไปใช้งาน (เมนูนี้ใช้เวลาเราทดสอบเว็บแอปของเราก่อนจะใช้งานจริง)

คลิกที่ลิงค์เพื่อทดสอบเว็บแอป

ก็จะได้เว็บแอป ออกมาใช้งานได้ดังภาพ ทดสอบ dropdown โดยเลือก ตำแหน่ง ชื่อ สกุล และกลุ่มสาระการเรียนรู้ ตามลำดับ แล้วกดบันทึก

เมื่อกด บันทึก แล้วข้อมูลจะถูกบันทึกไปที่ชีตชื่อ Data ดังภาพ

ที่ไฟล์ code.gs โค้ดที่มีหน้าที่ทำ dropdown คือ ฟังก์ชัน getData มีโค้ดดังนี้ จะเห็นว่า มีการดึงข้อมูลจาก ColA ColB และ ColC ทั้งหมด 3 คอลัมภ์ด้วยกัน โดยไปดึงมาจากชีตชื่อ list

function getData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1]
  var data = sheet.getDataRange().getDisplayValues().slice(1)
  var obj ={}
      data.forEach(([colA,colB,colC])=>{
      const firstCol = obj[colA]
      if(!firstCol){
          obj[colA] = {}
          obj[colA][colB] = [colC]
      }else{
        const secondCol = firstCol[colB]
        if(!secondCol){
          firstCol[colB] = [colC]
        }else{
          secondCol.push(colC)
        }
      }
    })
    Logger.log(obj)
  return obj
  }

แต่ถ้าต้องการ dropdown เพียงแค่ 2 คอลัมภ์ ให้แก้ไขโค้ด โดยคอมเม้นท์โค้ดด้านบนออก โดยการลากครอบ แล้วกด Ctrl+/ แล้ว Copy โค้ดต่อไปนี้ไปวาง

function getData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  var data = sheet.getDataRange().getDisplayValues().slice(1);
  var obj = {};

  data.forEach(([colA, colB, _]) => {
    const firstCol = obj[colA];
    if (!firstCol) {
      obj[colA] = { [colB]: true };
    } else {
      obj[colA][colB] = true;
    }
  });

  Logger.log(obj);

  return  obj
}

เมื่อคอมเม้นท์และวางจะได้ดังภาพ แล้วกด Save ลองทดสอบดู