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