ขยาย Google ชีต

ใช้ Google Apps Script เพื่อขยายชีต เพิ่มเมนูที่กำหนดเอง กล่องโต้ตอบ และ แถบด้านข้างลงในชีต เขียนฟังก์ชันที่กำหนดเองสำหรับชีต และ ผสานรวมกับบริการอื่นๆ ของ Google เช่น Google ปฏิทิน, Google ไดรฟ์ และ Gmail

สคริปต์ส่วนใหญ่ที่ออกแบบมาสำหรับชีตจะจัดการอาร์เรย์เพื่อโต้ตอบกับเซลล์ แถว และคอลัมน์ในสเปรดชีต หากไม่คุ้นเคยกับอาร์เรย์ใน JavaScript ทาง Codecademy มีโมดูลการฝึกอบรมที่ยอดเยี่ยมสำหรับอาร์เรย์ หลักสูตรนี้ไม่ได้พัฒนาโดย Google และไม่ได้เชื่อมโยงกับ Google

หากต้องการดูข้อมูลเบื้องต้นเกี่ยวกับการใช้ Apps Script กับชีตอย่างรวดเร็ว โปรดดูคู่มือเริ่มใช้งานฉบับย่อ 5 นาทีสำหรับมาโคร เมนู และฟังก์ชันที่กำหนดเอง

เริ่มต้นใช้งาน

Apps Script มี API พิเศษสำหรับสร้าง อ่าน และแก้ไขชีตแบบเป็นโปรแกรม Apps Script จะโต้ตอบกับ ชีตได้ 2 วิธีคือ สคริปต์ใดก็ได้สามารถสร้างหรือแก้ไขสเปรดชีต หากผู้ใช้สคริปต์มีสิทธิ์ที่เหมาะสม และสคริปต์ยังเชื่อมโยงกับสเปรดชีตได้ด้วย สคริปต์ที่ผูกไว้มีความสามารถพิเศษในการแก้ไขอินเทอร์เฟซผู้ใช้หรือตอบสนองเมื่อเปิดสเปรดชีต หากต้องการสร้างสคริปต์ที่ผูกไว้ ให้เลือกส่วนขยาย > Apps Script จากภายในชีต

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

อ่านข้อมูล

สมมติว่าคุณมีรายการชื่อผลิตภัณฑ์และหมายเลขผลิตภัณฑ์ที่จัดเก็บไว้ในสเปรดชีต ดังที่แสดงในรูปภาพต่อไปนี้

ตัวอย่างต่อไปนี้แสดงวิธีดึงและบันทึกชื่อผลิตภัณฑ์และหมายเลขผลิตภัณฑ์

function logProductInfo() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

ดูบันทึก

หากต้องการดูข้อมูลที่บันทึกไว้ ให้คลิกบันทึกการดำเนินการที่ด้านบนของเครื่องมือแก้ไขสคริปต์

เขียนข้อมูล

หากต้องการจัดเก็บข้อมูล เช่น ชื่อและหมายเลขผลิตภัณฑ์ใหม่ลงในสเปรดชีต ให้เพิ่มโค้ดต่อไปนี้ที่ส่วนท้ายของสคริปต์

function addProduct() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

โค้ดก่อนหน้าจะต่อท้ายแถวใหม่ที่ด้านล่างของสเปรดชีตโดยมี ค่าที่ระบุ หากเรียกใช้ฟังก์ชันนี้ ระบบจะเพิ่มแถวใหม่ลงใน สเปรดชีต

เมนูและอินเทอร์เฟซผู้ใช้ที่กำหนดเอง

ปรับแต่งชีตโดยการเพิ่มเมนู กล่องโต้ตอบ และแถบด้านข้างที่กำหนดเอง ดูข้อมูลพื้นฐานเกี่ยวกับการสร้างเมนูได้ที่คำแนะนำเกี่ยวกับเมนู ดูข้อมูลเกี่ยวกับการปรับแต่งเนื้อหาของกล่องโต้ตอบได้ที่คู่มือบริการ HTML

แนบฟังก์ชันสคริปต์กับรูปภาพหรือภาพวาดภายในสเปรดชีต โดยฟังก์ชันจะทํางานเมื่อผู้ใช้คลิกรูปภาพหรือภาพวาด ดูข้อมูลเพิ่มเติมได้ที่รูปภาพและภาพวาดในชีต

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

เชื่อมต่อกับ Google ฟอร์ม

เชื่อมต่อ Google ฟอร์มกับชีตผ่านบริการฟอร์มและสเปรดชีต ฟีเจอร์นี้จะสร้าง Google ฟอร์มโดยอัตโนมัติ ตามข้อมูลในสเปรดชีต นอกจากนี้ Apps Script ยังให้คุณใช้ทริกเกอร์ เช่น onFormSubmit เพื่อดำเนินการที่เฉพาะเจาะจงหลังจากที่ผู้ใช้ตอบแบบฟอร์ม หากต้องการดูข้อมูลเพิ่มเติมเกี่ยวกับการเชื่อมต่อชีตกับฟอร์ม ให้ลองใช้ การจัดการคำตอบสำหรับฟอร์ม ฉบับเริ่มต้นอย่างรวดเร็ว 5 นาที

จัดรูปแบบข้อมูล

คลาส Range มีเมธอด เช่น setBackground เพื่อเข้าถึงและแก้ไขรูปแบบของเซลล์หรือช่วงของเซลล์ ตัวอย่างต่อไปนี้ จะตั้งค่ารูปแบบแบบอักษรของช่วง

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

การตรวจสอบข้อมูล

เข้าถึงกฎการตรวจสอบข้อมูลที่มีอยู่ในชีตหรือสร้างกฎใหม่ ตัวอย่างเช่น ตัวอย่างต่อไปนี้แสดงวิธีตั้งค่ากฎการตรวจสอบข้อมูล ที่อนุญาตเฉพาะตัวเลขระหว่าง 1 ถึง 100 ในเซลล์

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  const cell = SpreadsheetApp.getActive().getRange('B4');
  const rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

ดูรายละเอียดเพิ่มเติมเกี่ยวกับการทำงานกับกฎการตรวจสอบข้อมูลได้ที่ SpreadsheetApp.newDataValidation DataValidationBuilder และ Range.setDataValidation

แผนภูมิ

ฝังแผนภูมิในสเปรดชีตที่แสดงข้อมูลในช่วงที่เฉพาะเจาะจง ตัวอย่างต่อไปนี้สร้างแผนภูมิแท่งแบบฝัง โดยสมมติว่าคุณมีข้อมูลที่สร้างแผนภูมิได้ ในเซลล์ A1:B15

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

  const chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

ดูข้อมูลเพิ่มเติมเกี่ยวกับการฝังแผนภูมิลงในสเปรดชีตได้ที่ EmbeddedChart และเครื่องมือสร้างแผนภูมิที่เฉพาะเจาะจง เช่น EmbeddedPieChartBuilder

ฟังก์ชันที่กำหนดเองใน Google ชีต

ฟังก์ชันที่กำหนดเองคล้ายกับฟังก์ชันสเปรดชีตในตัว เช่น =SUM(A1:A5) ยกเว้นว่าคุณกำหนดลักษณะการทำงานของฟังก์ชันด้วย Apps Script เช่น คุณอาจสร้างฟังก์ชันที่กำหนดเอง in2mm() ซึ่งแปลงค่าจากนิ้วเป็นมิลลิเมตร แล้วใช้สูตรในสเปรดชีตโดยพิมพ์ =in2mm(A1) หรือ =in2mm(10) ลงในเซลล์

หากต้องการดูข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชันที่กำหนดเอง ให้ลองใช้ เมนูและฟังก์ชันที่กำหนดเอง ฉบับเริ่มต้นอย่างรวดเร็วใน 5 นาที หรือดูข้อมูลเชิงลึกเพิ่มเติมใน คำแนะนำเกี่ยวกับฟังก์ชันที่กำหนดเอง

มาโคร

มาโครเป็นอีกวิธีหนึ่งในการเรียกใช้โค้ด Apps Script จาก UI ของชีต คุณจะเปิดใช้งานฟังก์ชันเหล่านี้ได้ด้วยแป้นพิมพ์ลัดหรือผ่านเมนูชีต ซึ่งต่างจากฟังก์ชันที่กำหนดเอง ดูข้อมูลเพิ่มเติมได้ที่มาโครของชีต

ส่วนเสริมสำหรับ Google ชีต

ส่วนเสริมคือโปรเจ็กต์ Apps Script ที่ได้รับการแพ็กเกจเป็นพิเศษ ซึ่งทำงานภายในชีตและ ติดตั้งได้จากร้านค้าส่วนเสริมของชีต หากคุณพัฒนาสคริปต์สำหรับ ชีตและต้องการแชร์ Apps Script จะช่วยให้คุณ เผยแพร่สคริปต์เป็น ส่วนเสริมเพื่อให้ผู้ใช้รายอื่นติดตั้งได้

ประสิทธิภาพและการปรับขนาด

เมื่อชุดข้อมูลมีขนาดใหญ่ขึ้น คุณอาจพบปัญหาด้านประสิทธิภาพ วิธีเพิ่มประสิทธิภาพ สเปรดชีตและสคริปต์

• ทำตามแนวทางปฏิบัติแนะนำ: อ่านคู่มือแนวทางปฏิบัติแนะนำ เพื่อดูเคล็ดลับในการลดการโทรติดต่อฝ่ายบริการและการใช้การดำเนินการแบบเป็นชุด
• เพิ่มประสิทธิภาพสูตร: หากสเปรดชีตทำงานช้าเนื่องจากสูตรที่ซับซ้อน (เช่น VLOOKUP, ARRAYFORMULA หรือ IMPORTRANGE) ให้ลองใช้ Apps Script เพื่อทำการคำนวณเหล่านี้ในหน่วยความจำและเขียนผลลัพธ์กลับเป็น ชุด
• พิจารณาฐานข้อมูลทางเลือก: สำหรับชุดข้อมูลขนาดใหญ่มาก (ใกล้ถึง 10 ล้านเซลล์) หรือการป้อนข้อมูลที่มีความถี่สูง (เช่น แบบฟอร์มที่เชื่อมต่อจำนวนมาก) ให้พิจารณาใช้ Google Cloud SQL โดยใช้ JDBC หรือ BigQuery

ทริกเกอร์

สคริปต์ที่เชื่อมโยงกับไฟล์ชีต จะใช้ทริกเกอร์แบบง่าย เช่น ฟังก์ชัน onOpen() และ onEdit() เพื่อตอบสนองโดยอัตโนมัติเมื่อผู้ใช้ที่มีสิทธิ์เข้าถึงระดับแก้ไข สเปรดชีตเปิดหรือแก้ไขสเปรดชีต เช่นเดียวกับทริกเกอร์แบบง่าย ทริกเกอร์ที่ติดตั้งได้จะช่วยให้ ชีตเรียกใช้ฟังก์ชันโดยอัตโนมัติเมื่อเกิดเหตุการณ์หนึ่งๆ อย่างไรก็ตาม ทริกเกอร์ที่ติดตั้งได้จะมีความยืดหยุ่นมากกว่าทริกเกอร์อย่างง่าย และรองรับเหตุการณ์ต่อไปนี้ เปิด แก้ไข เปลี่ยน ส่งแบบฟอร์ม และตามเวลา (นาฬิกา)