เกี่ยวกับ Codelab นี้
1 บทนำ
ยินดีต้อนรับสู่ส่วนที่ 3 ของ Fundamentals of Apps Script ด้วยเพลย์ลิสต์ Google Codelab
เมื่อดําเนินการ Codelab นี้ให้เสร็จ คุณจะได้เรียนรู้วิธีใช้การจัดการข้อมูล เมนูที่กําหนดเอง และการเรียกข้อมูล API สาธารณะใน Apps Script เพื่อปรับปรุงประสบการณ์การใช้งานชีตและ#39 คุณจะยังคงทํางานกับคลาส SpreadsheetApp, Spreadsheet, Sheet และ Range ที่ Codelab ก่อนหน้าแนะนําไว้ในเพลย์ลิสต์นี้
สิ่งที่คุณจะได้เรียนรู้
- วิธีนําเข้าข้อมูลจากสเปรดชีตส่วนตัวหรือสเปรดชีตแบบแชร์ในไดรฟ์
- วิธีสร้างเมนูที่กําหนดเองด้วยฟังก์ชัน
onOpen() - วิธีแยกวิเคราะห์และจัดการค่าข้อมูลสตริงในเซลล์ของ Google ชีต
- วิธีดึงข้อมูลและจัดการออบเจ็กต์ JSON จากแหล่งที่มา API สาธารณะ
ข้อควรทราบก่อนที่จะเริ่มต้น
นี่คือ Codelab ที่ 3 ในเพลย์ลิสต์พื้นฐานของ Apps Script ที่มี Google ชีต ก่อนเริ่ม Codelab นี้ โปรดตรวจสอบว่าได้กรอกข้อมูลใน Codelab ก่อนหน้าเรียบร้อยแล้ว
สิ่งที่ต้องมี
- ความเข้าใจเกี่ยวกับหัวข้อพื้นฐานของ Apps Script ที่สํารวจใน Codelab ก่อนหน้าของเพลย์ลิสต์นี้
- ความคุ้นเคยกับเครื่องมือแก้ไข Apps Script ในระดับพื้นฐาน
- ทําความคุ้นเคยกับ Google ชีต
- ความสามารถในการอ่านรูปแบบ A1 ของชีต
- ทําความคุ้นเคยกับ JavaScript และชั้นเรียน
Stringของ JavaScript
2 ตั้งค่า
แบบฝึกหัดใน Codelab นี้ต้องใช้สเปรดชีตในการทํางาน ทําตามขั้นตอนต่อไปนี้เพื่อสร้างสเปรดชีตที่จะใช้แบบฝึกหัดเหล่านี้
- สร้างสเปรดชีตใน Google ไดรฟ์ โดยไปที่อินเทอร์เฟซไดรฟ์โดยเลือก New > Google Sheets การดําเนินการนี้จะสร้างและเปิดสเปรดชีตใหม่ของคุณ ไฟล์จะบันทึกลงในโฟลเดอร์ไดรฟ์ของคุณ
- คลิกที่ชื่อสเปรดชีตและเปลี่ยนชื่อจาก "สเปรดชีตที่ไม่มีชื่อและ" เป็น "การปรับแต่งข้อมูลและเมนูที่กําหนดเอง" ชีตควรมีลักษณะดังนี้
- หากต้องการเปิดตัวแก้ไขสคริปต์ ให้คลิกส่วนขยาย> Apps Script
- คลิกชื่อโครงการ Apps Script แล้วเปลี่ยนจาก "Untitle Project" เป็น "จัดการข้อมูลและเมนูที่กําหนดเอง&" คลิกเปลี่ยนชื่อเพื่อบันทึกการเปลี่ยนชื่อ
ด้วยสเปรดชีตเปล่าและโปรเจ็กต์ คุณก็พร้อมเริ่มห้องทดลองแล้ว ไปที่ส่วนถัดไปเพื่อเริ่มดูข้อมูลเมนูที่กําหนดเอง
3 ภาพรวม: นําเข้าข้อมูลด้วยรายการเมนูที่กําหนดเอง
Apps Script ช่วยให้คุณกําหนดเมนูที่กําหนดเองที่ปรากฏใน Google ชีตได้ นอกจากนี้ คุณยังใช้เมนูที่กําหนดเองใน Google เอกสาร, Google สไลด์ และ Google ฟอร์มได้อีกด้วย เมื่อกําหนดรายการในเมนูที่กําหนดเอง คุณจะสร้างป้ายกํากับข้อความและเชื่อมเข้ากับฟังก์ชัน Apps Script ในโครงการสคริปต์ จากนั้นคุณจะเพิ่มเมนูใน UI เพื่อให้ปรากฏใน Google ชีตได้
เมื่อผู้ใช้คลิกรายการในเมนูที่กําหนดเอง ฟังก์ชัน Apps Script ที่คุณเชื่อมโยงไว้จะทํางาน วิธีนี้เป็นวิธีที่รวดเร็วในการเรียกใช้ฟังก์ชันของ Apps Script โดยไม่ต้องเปิดเครื่องมือแก้ไขสคริปต์ นอกจากนี้ยังอนุญาตให้ผู้ใช้สเปรดชีตนี้เรียกใช้โค้ดโดยไม่ต้องรู้วิธีการทํางานของ Apps Script หรือวิธีการทํางานของสคริปต์ เพราะเป็นเพียงรายการในเมนูอีกรายการเท่านั้น
รายการในเมนูที่กําหนดเองจะกําหนดในฟังก์ชัน onOpen() ทริกเกอร์แบบง่าย ซึ่งคุณจะได้เรียนรู้ในส่วนถัดไป
4 ฟังก์ชัน onOpen()
ทริกเกอร์แบบง่ายใน Apps Script เป็นวิธีเรียกใช้โค้ด Apps Script เฉพาะเพื่อตอบสนองเงื่อนไขหรือเหตุการณ์บางอย่าง เมื่อคุณสร้างทริกเกอร์ คุณจะต้องระบุว่าเหตุการณ์ใดทําให้ทริกเกอร์เริ่มทํางาน และให้ฟังก์ชัน Apps Script ที่เรียกใช้สําหรับเหตุการณ์นั้น
onOpen() เป็นตัวอย่างของทริกเกอร์แบบง่าย ทั้งยังตั้งค่าได้โดยง่าย เพียงเขียนฟังก์ชัน Apps Script ที่ชื่อ onOpen() แล้ว Apps Script ก็จะเรียกใช้ทุกครั้งที่เปิดสเปรดชีตที่เกี่ยวข้องหรือโหลดใหม่
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
/* ... */
}
การใช้งาน
มาสร้างเมนูที่กําหนดเองกัน
- แทนที่โค้ดในโปรเจ็กต์สคริปต์ด้วยข้อมูลต่อไปนี้
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Book-list')
.addItem('Load Book-list', 'loadBookList')
.addToUi();
}
- บันทึกโปรเจ็กต์สคริปต์
การตรวจสอบโค้ด
มาตรวจสอบโค้ดนี้เพื่อทําความเข้าใจกันดีกว่า ใน onOpen() บรรทัดแรกจะใช้เมธอด getUi() เพื่อหาออบเจ็กต์ Ui ที่แสดงถึงอินเทอร์เฟซผู้ใช้ของสเปรดชีตที่ใช้งานอยู่ที่มีการเชื่อมโยงสคริปต์นี้
อีก 3 บรรทัดถัดไปจะสร้างเมนู (Book-list) เพิ่มรายการในเมนู (Load Book-list) ในเมนูนั้น แล้วเพิ่มเมนูในอินเทอร์เฟซของสเปรดชีต ซึ่งทําได้โดยใช้เมธอด createMenu(caption), addItem(caption, functionName) และ addToUi() ตามลําดับ
เมธอด addItem(caption, functionName) จะสร้างการเชื่อมต่อระหว่างป้ายกํากับรายการในเมนูและฟังก์ชัน Apps Script ที่จะทํางานเมื่อเลือกรายการเมนู ในกรณีนี้ การเลือกรายการในเมนู Load Book-list จะทําให้ชีตพยายามเรียกใช้ฟังก์ชัน loadBookList() (ซึ่งยังไม่มีอยู่)
ผลลัพธ์
เรียกใช้ฟังก์ชันนี้เพื่อดูลักษณะการทํางานดังนี้
- โหลดสเปรดชีตซ้ําใน Google ชีต หมายเหตุ: โดยปกติแล้วจะเป็นการปิดแท็บที่มีตัวแก้ไขสคริปต์
- เปิดเครื่องมือแก้ไขสคริปต์อีกครั้งโดยเลือก Tools > Script editor
หลังจากโหลดสเปรดชีตใหม่แล้ว เมนู Book-list ใหม่ควรปรากฏในแถบเมนู
เมื่อคลิกรายการหนังสือ คุณจะเห็นเมนูที่ได้
ส่วนถัดไปจะสร้างโค้ดสําหรับฟังก์ชัน loadBookList() และแนะนําหนึ่งในวิธีที่คุณสามารถโต้ตอบกับข้อมูลใน Apps Script ได้คือการอ่านสเปรดชีตอื่นๆ
5 นําเข้าข้อมูลสเปรดชีต
ขณะนี้คุณได้สร้างเมนูที่กําหนดเองแล้ว คุณจึงสร้างฟังก์ชันที่เรียกใช้ได้โดยคลิกรายการในเมนู
ขณะนี้เมนูที่กําหนดเอง Book-list มีรายการเมนู 1 รายการ: Load Book-list. ฟังก์ชันที่ถูกเรียกเมื่อคุณเลือกรายการเมนู Load Book-list loadBookList(), จะไม่มีในสคริปต์ของคุณ ดังนั้นการเลือก รายการหนังสือและg; โหลดรายการหนังสือ จะแสดงข้อผิดพลาด:
คุณแก้ไขข้อผิดพลาดนี้ได้โดยใช้ฟังก์ชัน loadBookList()
การใช้งาน
คุณต้องการให้รายการในเมนูใหม่เต็มไปด้วยข้อมูลสเปรดชีตที่จะใช้งาน เพื่อให้คุณใช้ loadBookList() เพื่ออ่านข้อมูลหนังสือจากสเปรดชีตอื่นและคัดลอกไปยังสเปรดชีตนี้ได้
- เพิ่มโค้ดต่อไปนี้ลงในสคริปต์ในส่วน
onOpen()
/**
* Creates a template book list based on the
* provided 'codelab-book-list' sheet.
*/
function loadBookList(){
// Gets the active sheet.
var sheet = SpreadsheetApp.getActiveSheet();
// Gets a different spreadsheet from Drive using
// the spreadsheet's ID.
var bookSS = SpreadsheetApp.openById(
"1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo"
);
// Gets the sheet, data range, and values of the
// spreadsheet stored in bookSS.
var bookSheet = bookSS.getSheetByName("codelab-book-list");
var bookRange = bookSheet.getDataRange();
var bookListValues = bookRange.getValues();
// Add those values to the active sheet in the current
// spreadsheet. This overwrites any values already there.
sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth())
.setValues(bookListValues);
// Rename the destination sheet and resize the data
// columns for easier reading.
sheet.setName("Book-list");
sheet.autoResizeColumns(1, 3);
}
- บันทึกโปรเจ็กต์สคริปต์
การตรวจสอบโค้ด
แล้วฟังก์ชันนี้ทํางานอย่างไร ฟังก์ชัน loadBookList() ใช้วิธีการส่วนใหญ่จากคลาส Spreadsheet, Sheet และ Range ที่ Codelab ก่อนหน้านี้แนะนํา ด้วยแนวคิดเหล่านี้ คุณสามารถแบ่งโค้ด loadBookList() ออกเป็น 4 ส่วนต่อไปนี้
1: ระบุชีตปลายทาง
บรรทัดแรกจะใช้ SpreadsheetApp.getActiveSheet() เพื่อรับการอ้างอิงออบเจ็กต์ชีตปัจจุบันและจัดเก็บไว้ในตัวแปร sheet นี่คือแผ่นงานที่จะคัดลอกข้อมูลไปให้
2: ระบุแหล่งที่มาของข้อมูล
ในอีกไม่กี่บรรทัดข้างหน้านี้ จะมีตัวแปร 4 ตัวแปรที่อ้างอิงข้อมูลต้นฉบับที่คุณกําลังดึงข้อมูลมา ดังนี้
bookSSจัดเก็บการอ้างอิงในสเปรดชีตที่มีการอ่านข้อมูล โค้ดจะค้นหาสเปรดชีตตามรหัสสเปรดชีต ในตัวอย่างนี้ เราได้ระบุรหัสของสเปรดชีตต้นทางที่จะใช้อ่าน และเปิดสเปรดชีตโดยใช้เมธอดSpreadsheetApp.openById(id)bookSheetจัดเก็บการอ้างอิงไว้ในชีตภายในbookSSที่มีข้อมูลที่คุณต้องการ โค้ดจะระบุแผ่นงานที่จะอ่านตามชื่อcodelab-book-listbookRangeจัดเก็บการอ้างอิงช่วงข้อมูลไว้ในbookSheetเมธอดSheet.getDataRange()จะแสดงผลช่วงที่มีเซลล์ที่ไม่ว่างเปล่าทั้งหมดในชีต ซึ่งเป็นวิธีที่ง่ายในการดูช่วงที่ครอบคลุมข้อมูลทั้งหมดในชีตโดยไม่รวมแถวและคอลัมน์เปล่าbookListValuesคืออาร์เรย์ 2 มิติที่มีค่าทั้งหมดจากเซลล์ในbookRangeเมธอดRange.getValues()จะสร้างอาร์เรย์นี้โดยอ่านข้อมูลจากชีตต้นทาง
3: คัดลอกข้อมูลจากแหล่งที่มาไปยังปลายทาง
ส่วนโค้ดถัดไปจะคัดลอกข้อมูล bookListValues ไปยัง sheet จากนั้นเปลี่ยนชื่อชีตด้วย ดังนี้
- ใช้
Sheet.getRange(row, column, numRows, numColumns)เพื่อระบุตําแหน่งที่จะคัดลอกข้อมูลในsheet - วิธี
Range.getHeight()และRange.getWidth()ใช้เพื่อวัดขนาดของข้อมูลและกําหนดช่วงปลายทางของมิติข้อมูลเดียวกัน Range.setValues(values)คัดลอกอาร์เรย์ 2 มิติของbookListValuesลงในช่วงปลายทางโดยเขียนทับข้อมูลที่มีอยู่แล้ว
4: จัดรูปแบบชีตปลายทาง
Sheet.setName(name) จะใช้เพื่อเปลี่ยนชื่อชีตปลายทางเป็น Book-list บรรทัดสุดท้ายในฟังก์ชันจะใช้ Sheet.autoResizeColumns(startColumn, numColumns) เพื่อปรับขนาด 3 คอลัมน์แรกในชีตปลายทาง ซึ่งช่วยให้คุณอ่านข้อมูลใหม่ได้ง่ายขึ้น
ผลลัพธ์
คุณจะเห็นได้ว่าฟังก์ชันนี้ทํางานอยู่ ใน Google ชีต ให้เลือกBook-list > Loadbook-list เพื่อเรียกใช้ฟังก์ชันในการกรอกข้อมูลในสเปรดชีต
ตอนนี้คุณมีชีตพร้อมรายชื่อหนังสือ ผู้แต่ง และหมายเลข ISBN 13 หลัก ในหัวข้อถัดไป คุณจะได้ดูวิธีแก้ไขและอัปเดตข้อมูลในรายการหนังสือเล่มนี้โดยใช้การดัดแปลงสตริงและเมนูที่กําหนดเอง
6 ภาพรวม: ล้างข้อมูลสเปรดชีต
ขณะนี้คุณมีข้อมูลหนังสืออยู่ในชีตแล้ว แต่ละแถวหมายถึงหนังสือเล่มใดเล่มหนึ่ง โดยจะแสดงชื่อหนังสือ ผู้แต่ง และหมายเลข ISBN ในคอลัมน์แยกกัน แต่คุณยังพบปัญหาบางอย่างเกี่ยวกับข้อมูลดิบนี้ด้วย
- ในแต่ละแถว ชื่อเรื่องและผู้เขียนจะอยู่ในคอลัมน์ชื่อด้วยกัน คั่นด้วยคอมมาหรือสตริง " โดย "
- บางแถวไม่มีชื่อหนังสือหรือผู้แต่ง
ในส่วนถัดไป คุณจะแก้ไขปัญหาเหล่านี้ได้โดยการล้างข้อมูล สําหรับปัญหาแรก คุณจะต้องสร้างฟังก์ชันที่อ่านคอลัมน์ชื่อและแบ่งข้อความทุกครั้งที่เครื่องหมายจุลภาคหรือ " พบ " พบตัวคั่น วางสตริงผู้เขียนและสตริงชื่อที่เกี่ยวข้องในคอลัมน์ที่ถูกต้อง สําหรับมีปัญหาที่ 2 คุณจะเขียนโค้ดที่ค้นหาข้อมูลหนังสือที่หายไปโดยอัตโนมัติโดยใช้ API ภายนอก แล้วเพิ่มข้อมูลนั้นลงในชีต
7 เพิ่มรายการในเมนู
คุณจะต้องสร้างรายการเมนู 3 รายการเพื่อควบคุมการดําเนินการทําความสะอาดข้อมูลที่คุณจะนําไปใช้
การใช้งาน
มาอัปเดต onOpen() เพื่อรวมรายการเมนูเพิ่มเติมที่ต้องการกัน ทำสิ่งต่อไปนี้:
- อัปเดตสคริปต์
onOpen()ในโปรเจ็กต์สคริปต์เพื่อให้ตรงกับสิ่งต่อไปนี้
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Book-list')
.addItem('Load Book-list', 'loadBookList')
.addSeparator()
.addItem(
'Separate title/author at first comma', 'splitAtFirstComma')
.addItem(
'Separate title/author at last "by"', 'splitAtLastBy')
.addSeparator()
.addItem(
'Fill in blank titles and author cells', 'fillInTheBlanks')
.addToUi();
}
- บันทึกโปรเจ็กต์สคริปต์
- เลือก
onOpenจากรายการฟังก์ชันในตัวแก้ไขสคริปต์ แล้วคลิกเรียกใช้ การดําเนินการนี้จะเรียกใช้onOpen()เพื่อสร้างเมนูสเปรดชีตใหม่ คุณจึงไม่ต้องโหลดสเปรดชีตซ้ํา
ในโค้ดใหม่นี้ เมธอด Menu.addSeparator() จะสร้างตัวแบ่งแนวนอนในเมนูเพื่อให้จัดกลุ่มรายการเมนูที่เกี่ยวข้องได้อย่างเป็นระเบียบ จากนั้นเมนูใหม่ในเมนูด้านล่างจะมีป้ายกํากับ Separate title/author at first comma, Separate title/author at last "by" และ Fill in blank titles and author cells
ผลลัพธ์
คลิกเมนู Book-list เพื่อดูรายการในเมนูใหม่ในสเปรดชีต
การคลิกรายการใหม่เหล่านี้ทําให้เกิดข้อผิดพลาดเนื่องจากคุณยังไม่ได้นําฟังก์ชันที่เกี่ยวข้องของรายการดังกล่าวมาใช้ ดังนั้นมาลองทํากันเลย
8 แบ่งข้อความในตัวคั่นคอมมา
ชุดข้อมูลที่คุณนําเข้าในสเปรดชีตมีเซลล์ไม่กี่เซลล์ที่ผู้เขียนผู้เขียนและชื่อรวมกันไม่ถูกต้องในเซลล์เดียว
การแยกสตริงข้อความออกเป็นคอลัมน์แยกจากกันเป็นงานสเปรดชีตทั่วไป Google ชีตมีฟังก์ชัน SPLIT() ที่แบ่งสตริงเป็นคอลัมน์ แต่ชุดข้อมูลมักจะมีปัญหาที่ชีตแก้ไขได้ง่ายๆ ด้วยฟังก์ชันบิวท์อิน ในกรณีเช่นนี้ คุณสามารถเขียนโค้ด Apps Script เพื่อดําเนินการที่ซับซ้อนซึ่งจําเป็นต่อการจัดระเบียบและจัดระเบียบข้อมูลได้
เริ่มล้างข้อมูลโดยใช้ฟังก์ชันชื่อ splitAtFirstComma() ที่แบ่งผู้เขียนและชื่อลงในเซลล์ของเซลล์ก่อน เมื่อพบคอมมา
ฟังก์ชัน splitAtFirstComma() ควรทําตามขั้นตอนต่อไปนี้
- ดูช่วงที่แสดงถึงเซลล์ที่เลือกในปัจจุบัน
- ตรวจสอบว่าเซลล์ในช่วงมีคอมมาหรือไม่
- เมื่อพบคอมมา ให้แบ่งสตริงเป็นสตริงย่อย 2 รายการ (และ 2 รายการเท่านั้น) ในตําแหน่งของคอมมาแรก ในการทําให้เครื่องหมายจุลภาคง่ายขึ้น ให้ถือว่าเครื่องหมายจุลภาคใดๆ บ่งบอกถึงรูปแบบ "[authors], [title]" สตริง คุณยังเดาได้ด้วยว่ามีคอมมาหลายรายการปรากฏขึ้นในเซลล์หรือไม่ ก็ควรแบ่งให้คั่นด้วยคอมมาแรกในสตริง
- ตั้งค่าสตริงย่อยเป็นเนื้อหาใหม่ในชื่อที่เกี่ยวข้องและเซลล์ของผู้เขียน
การใช้งาน
ในการนําขั้นตอนเหล่านี้ไปใช้ คุณจะใช้บริการสเปรดชีตแบบเดียวกับที่คุณเคยใช้มาก่อน แต่คุณจะต้องใช้ JavaScript เพื่อควบคุมข้อมูลสตริงด้วย โปรดทําตามขั้นตอนต่อไปนี้
- เพิ่มฟังก์ชันต่อไปนี้ในตอนท้ายของสคริปต์สคริปต์ในเครื่องมือแก้ไข Apps Script
/**
* Reformats title and author columns by splitting the title column
* at the first comma, if present.
*/
function splitAtFirstComma(){
// Get the active (currently highlighted) range.
var activeRange = SpreadsheetApp.getActiveRange();
var titleAuthorRange = activeRange.offset(
0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);
// Get the current values of the selected title column cells.
// This is a 2D array.
var titleAuthorValues = titleAuthorRange.getValues();
// Update values where commas are found. Assumes the presence
// of a comma indicates an "authors, title" pattern.
for (var row = 0; row < titleAuthorValues.length; row++){
var indexOfFirstComma =
titleAuthorValues[row][0].indexOf(", ");
if(indexOfFirstComma >= 0){
// Found a comma, so split and update the values in
// the values array.
var titlesAndAuthors = titleAuthorValues[row][0];
// Update the title value in the array.
titleAuthorValues[row][0] =
titlesAndAuthors.slice(indexOfFirstComma + 2);
// Update the author value in the array.
titleAuthorValues[row][1] =
titlesAndAuthors.slice(0, indexOfFirstComma);
}
}
// Put the updated values back into the spreadsheet.
titleAuthorRange.setValues(titleAuthorValues);
}
- บันทึกโปรเจ็กต์สคริปต์
การตรวจสอบโค้ด
มาตรวจสอบโค้ดใหม่ที่ประกอบด้วย 3 ส่วนหลักกัน
1: ดึงค่าชื่อที่ไฮไลต์
สามบรรทัดแรกจะสร้างตัวแปร 3 รายการที่อ้างถึงข้อมูลปัจจุบันในชีต
activeRangeแสดงถึงช่วงที่ผู้ใช้ไฮไลต์อยู่เมื่อเรียกใช้ฟังก์ชันsplitAtFirstComma()เพื่อทําให้การออกกําลังกายนี้ง่ายขึ้น เราจะสมมติว่าผู้ใช้ทําเช่นนี้ได้ก็ต่อเมื่อไฮไลต์เซลล์ในคอลัมน์ A เท่านั้นtitleAuthorRangeแสดงถึงช่วงใหม่ที่ครอบคลุมเซลล์เดียวกับactiveRangeแต่ยังมีคอลัมน์ทางด้านขวาอีก 1 คอลัมน์titleAuthorRangeสร้างขึ้นโดยใช้เมธอดRange.offset(rowOffset, columnOffset, numRows, numColumns)โค้ดต้องมีช่วงการขยายนี้เนื่องจากต้องมีพื้นที่สําหรับใส่ผู้เขียนที่พบในคอลัมน์ชื่อtitleAuthorValuesคืออาร์เรย์ 2 มิติของข้อมูลที่ดึงมาจากtitleAuthorRangeโดยใช้Range.getValues()
2: ตรวจสอบแต่ละชื่อและแบ่งด้วยตัวคั่นคอมมารายการแรก
ส่วนถัดไปจะตรวจสอบค่าใน titleAuthorValues เพื่อหาเครื่องหมายจุลภาค JavaScript สําหรับวนซ้ําใช้เพื่อตรวจสอบค่าทั้งหมดในคอลัมน์แรกของ titleAuthorValues เมื่อพบสตริงย่อยในคอมมา (", ") โดยใช้เมธอด JavaScript String indexOf() โค้ดจะดําเนินการต่อไปนี้
- ระบบจะคัดลอกค่าสตริงเซลล์ไปยังตัวแปร
titlesAndAuthors - ตําแหน่งเครื่องหมายจุลภาคจะกําหนดโดยใช้เมธอด JavaScript String indexOf()
- เมธอด JavaScript String\() จะเรียกใช้ 2 ครั้ง เพื่อใช้สตริงย่อยก่อนตัวคั่นคอมมาและสตริงย่อยหลังตัวคั่น
- สตริงย่อยจะคัดลอกกลับไปยังอาร์เรย์ 2D titleAuthorValues โดยเขียนทับค่าที่มีอยู่ในตําแหน่งนั้น เนื่องจากเราสมมติว่า "[authors], [title]" รูปแบบ ลําดับของสตริงย่อยทั้ง 2 สตริงจึงถูกเปลี่ยนกลับเพื่อใส่ชื่อในคอลัมน์แรกและผู้แต่งในคอลัมน์ที่ 2
หมายเหตุ: เมื่อไม่พบรหัสเครื่องหมายจุลภาค โค้ดก็จะทําให้ข้อมูลในแถวไม่เปลี่ยนแปลง
3: คัดลอกค่าใหม่ลงในชีต
เมื่อตรวจสอบค่าเซลล์ชื่อทั้งหมดแล้ว ระบบจะคัดลอกอาร์เรย์ 2D titleAuthorValues ที่อัปเดตแล้วกลับไปยังสเปรดชีตโดยใช้เมธอด Range.setValues(values)
ผลลัพธ์
ตอนนี้คุณสามารถดูผลกระทบของฟังก์ชัน splitAtFirstComma() ในสถานการณ์จริงได้แล้ว ให้ลองเรียกใช้โดยเลือกรายการในเมนูแยกชื่อ/ผู้เขียนไว้ที่เครื่องหมายจุลภาคแรกหลังจากที่เลือก...
...เซลล์เดียว:
...หรือหลายเซลล์
ขณะนี้คุณได้สร้างฟังก์ชัน Apps Script ที่ประมวลผลข้อมูลในชีตแล้ว ถัดไป คุณจะใช้ฟังก์ชันตัวแบ่ง 2
9 แบ่งข้อความบน "by" ตัวคั่น
เมื่อดูข้อมูลเดิม คุณจะเห็นปัญหาอื่น เหมือนกับชื่อหนังสือและผู้แต่งบางรูปแบบในเซลล์เดียวเป็น "[authors], [title]" ผู้แต่งและชื่อเซลล์รูปแบบอื่นๆ เป็น "[title] โดย [authors]"
การใช้งาน
คุณแก้ไขปัญหานี้ได้ด้วยเทคนิคเดียวกันจากส่วนสุดท้าย คือสร้างฟังก์ชันชื่อ splitAtLastBy() ฟังก์ชันนี้มีงานคล้ายกับ splitAtFirstComma() ความแตกต่างที่แท้จริงเพียงอย่างเดียวคือค้นหารูปแบบข้อความที่แตกต่างกันเล็กน้อย ใช้งานฟังก์ชันนี้โดยทําตามขั้นตอนต่อไปนี้
- เพิ่มฟังก์ชันต่อไปนี้ในตอนท้ายของสคริปต์สคริปต์ในเครื่องมือแก้ไข Apps Script
/**
* Reformats title and author columns by splitting the title column
* at the last instance of the string " by ", if present.
*/
function splitAtLastBy(){
// Get the active (currently highlighted) range.
var activeRange = SpreadsheetApp.getActiveRange();
var titleAuthorRange = activeRange.offset(
0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);
// Get the current values of the selected title column cells.
// This is a 2D array.
var titleAuthorValues = titleAuthorRange.getValues();
// Update values where " by " substrings are found. Assumes
// the presence of a " by " indicates a "title by authors"
// pattern.
for(var row = 0; row < titleAuthorValues.length; row++){
var indexOfLastBy =
titleAuthorValues[row][0].lastIndexOf(" by ");
if(indexOfLastBy >= 0){
// Found a " by ", so split and update the values in
// the values array.
var titlesAndAuthors = titleAuthorValues[row][0];
// Update the title value in the array.
titleAuthorValues[row][0] =
titlesAndAuthors.slice(0, indexOfLastBy);
// Update the author value in the array.
titleAuthorValues[row][1] =
titlesAndAuthors.slice(indexOfLastBy + 4);
}
}
// Put the updated values back into the spreadsheet.
titleAuthorRange.setValues(titleAuthorValues);
}
- บันทึกโปรเจ็กต์สคริปต์
การตรวจสอบโค้ด
มีความแตกต่างที่สําคัญบางประการระหว่างโค้ดนี้กับ splitAtFirstComma():
- สตริงย่อย "
by" ใช้เป็นตัวคั่นสตริง แทน "," - ระบบจะใช้เมธอด JavaScript
String.lastIndexOf(substring)แทนString.indexOf(substring)ซึ่งหมายความว่าหากมีสตริง "by" หลายสตริงในสตริงเริ่มต้น สตริงทั้งหมดยกเว้น "by" จะถือว่าเป็นส่วนหนึ่งของชื่อ - หลังจากแยกสตริงแล้ว สตริงย่อยสตริงแรกจะกลายเป็นชื่อและสตริงที่ 2 เป็นผู้เขียน (เป็นลําดับตรงกันข้ามกับ
splitAtFirstComma())
ผลลัพธ์
ตอนนี้คุณสามารถดูผลกระทบของฟังก์ชัน splitAtLastBy() ในสถานการณ์จริงได้แล้ว ลองเรียกใช้ได้โดยเลือกรายการในเมนูแยกชื่อ/ผู้เขียนเมื่อสุดท้าย "by" หลังจากเลือก...
...เซลล์เดียว:
...หรือหลายเซลล์
คุณได้กรอกข้อมูลในส่วนนี้ของ Codelab เรียบร้อยแล้ว คุณสามารถใช้ Apps Script เพื่ออ่านและแก้ไขข้อมูลสตริงในชีต และใช้เมนูที่กําหนดเองเพื่อเรียกใช้คําสั่ง Apps Script ที่แตกต่างกันได้
ในส่วนถัดไป คุณจะได้เรียนรู้วิธีปรับปรุงชุดข้อมูลนี้เพิ่มเติมด้วยการเติมข้อมูลในเซลล์ว่างด้วยข้อมูลที่ดึงมาจาก API สาธารณะ
10 ภาพรวม: รับข้อมูลจาก API สาธารณะ
จนถึงตอนนี้ คุณได้ปรับแต่งชุดข้อมูลเพื่อแก้ไขปัญหาการจัดรูปแบบและผู้เขียนบางรายการ แต่ชุดข้อมูลดังกล่าวยังไม่มีข้อมูลบางอย่างที่ไฮไลต์ไว้ในเซลล์ด้านล่าง
คุณจะไม่เห็นข้อมูลที่ขาดหายไปโดยใช้การดําเนินการสตริงกับข้อมูลที่มีอยู่ในปัจจุบัน แต่คุณจะต้องได้รับข้อมูลที่ขาดหายไปจากแหล่งที่มาอื่นแทน ซึ่งทําได้ใน Apps Script ด้วยการขอข้อมูลจาก API ภายนอกที่ให้ข้อมูลเพิ่มเติมได้
API คืออินเทอร์เฟซในการเขียนโปรแกรมแอปพลิเคชัน ถึงจะเป็นคําทั่วไป แต่โดยทั่วไปก็คือบริการที่มีโปรแกรมและสคริปต์ของคุณเรียกใช้เพื่อขอข้อมูลหรือดําเนินการบางอย่างได้ ในส่วนนี้ คุณจะเรียกใช้ API ที่เผยแพร่ต่อสาธารณะเพื่อขอข้อมูลหนังสือที่คุณแทรกลงในเซลล์ที่ว่างในชีตได้
ส่วนนี้จะสอนวิธีทําสิ่งต่อไปนี้
- ขอข้อมูลหนังสือจากแหล่งที่มาของ API ภายนอก
- แยกข้อมูลชื่อหนังสือและผู้แต่งจากข้อมูลที่ส่งกลับ และเขียนลงในสเปรดชีต
11 ดึงข้อมูลภายนอกด้วย UrlFetch
ก่อนที่จะเจาะลึกโค้ดที่ทํางานร่วมกับสเปรดชีตโดยตรง คุณสามารถดูข้อมูลเกี่ยวกับการทํางานกับ API ภายนอกใน Apps Script ด้วยการสร้างฟังก์ชันตัวช่วยสําหรับการขอข้อมูลหนังสือโดยเฉพาะจาก Open Library API สาธารณะ
ฟังก์ชันตัวช่วย fetchBookData_(ISBN) ใช้หมายเลข ISBN 13 หลักของหนังสือเป็นพารามิเตอร์และแสดงข้อมูลเกี่ยวกับหนังสือเล่มนั้น โดยจะเชื่อมต่อและเรียกข้อมูลจาก Open Library API จากนั้นแยกวิเคราะห์ออบเจ็กต์ JSON ที่แสดงผล
การใช้งาน
ใช้ฟังก์ชันตัวช่วยนี้โดยทําตามขั้นตอนต่อไปนี้
- เพิ่มโค้ดต่อไปนี้ที่ส่วนท้ายของสคริปต์ Apps Script
/**
* Helper function to retrieve book data from the Open Library
* public API.
*
* @param {number} ISBN - The ISBN number of the book to find.
* @return {object} The book's data, in JSON format.
*/
function fetchBookData_(ISBN){
// Connect to the public API.
var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
+ ISBN + "&jscmd=details&format=json";
var response = UrlFetchApp.fetch(
url, {'muteHttpExceptions': true});
// Make request to API and get response before this point.
var json = response.getContentText();
var bookData = JSON.parse(json);
// Return only the data we're interested in.
return bookData['ISBN:' + ISBN];
}
- บันทึกโปรเจ็กต์สคริปต์
การตรวจสอบโค้ด
โค้ดนี้แบ่งออกเป็น 2 ส่วนหลักดังนี้
1: คําขอ API
ใน 2 บรรทัดแรก fetchBookData_(ISBN) จะเชื่อมต่อกับ Open Library API สาธารณะโดยใช้ URL ของ API' และบริการดึงข้อมูล URL ของ Apps Script'
ตัวแปร url เป็นเพียงสตริง URL เช่น ที่อยู่เว็บ ซึ่งชี้ไปที่ตําแหน่งในเซิร์ฟเวอร์ Open Library นอกจากนี้ ยังมีพารามิเตอร์ 3 แบบ (bibkeys, jscmd และ format) ที่บอกเซิร์ฟเวอร์ Open Library ว่าคุณกําลังขอข้อมูลอะไรและวิธีจัดโครงสร้างการตอบกลับ ในกรณีนี้ คุณต้องระบุหมายเลข ISBN ของหนังสือและขอข้อมูลโดยละเอียดในรูปแบบ JSON
เมื่อคุณสร้างสตริง URL แล้ว โค้ดจะส่งคําขอไปยังตําแหน่งและได้รับการตอบกลับ วิธีนี้ใช้เมธอด UrlFetchApp.fetch(url, params) ซึ่งจะส่งคําขอข้อมูลไปยัง URL ภายนอกที่คุณระบุ และจัดเก็บการตอบกลับที่ได้ไว้ในตัวแปร response นอกจาก URL แล้ว โค้ดยังตั้งค่าพารามิเตอร์ที่ไม่บังคับ muteHttpExceptions เป็น true อีกด้วย การตั้งค่านี้หมายความว่าโค้ดจะไม่หยุดหากคําขอทําให้เกิดข้อผิดพลาด API แต่จะมีการตอบกลับข้อผิดพลาดแทน
คําขอจะแสดงออบเจ็กต์ HTTPResponse ที่จัดเก็บไว้ในตัวแปร response การตอบกลับ HTTP ประกอบด้วยโค้ดตอบกลับ ส่วนหัว HTTP และเนื้อหาการตอบกลับหลัก ข้อมูลที่สนใจที่นี่เป็นเนื้อหา JSON หลัก โค้ดจึงต้องแตกข้อมูลแล้วแยกวิเคราะห์ JSON เพื่อค้นหาและส่งคืนข้อมูลที่ต้องการ
2: แยกวิเคราะห์การตอบกลับ API แล้วแสดงผลข้อมูลที่สนใจ
ใน 3 บรรทัดสุดท้ายของเมธอด HTTPResponse.getContentText() จะแสดงผลเนื้อหาหลักของการตอบกลับเป็นสตริง สตริงนี้อยู่ในรูปแบบ JSON แต่ Open Library API จะกําหนดเนื้อหาและรูปแบบที่แน่นอน เมธอด JSON.parse(jsonString) แปลงสตริง JSON เป็นออบเจ็กต์ JavaScript เพื่อให้แยกส่วนต่างๆ ของข้อมูลได้อย่างง่ายดาย สุดท้าย ฟังก์ชันจะส่งคืนข้อมูลที่สอดคล้องกับหมายเลข ISBN ของหนังสือ
ผลลัพธ์
ตอนนี้คุณได้ใช้ fetchBookData_(ISBN) แล้ว ฟังก์ชันอื่นๆ ในโค้ดสามารถค้นหาข้อมูลหนังสือได้โดยใช้หมายเลข ISBN คุณจะใช้ฟังก์ชันนี้เพื่อช่วยเติมข้อมูลในเซลล์ในสเปรดชีตได้
12 เขียนข้อมูล API ไปยังสเปรดชีต
ตอนนี้คุณสามารถใช้ฟังก์ชัน fillInTheBlanks() ที่ทําสิ่งต่อไปนี้ได้
- ระบุชื่อหนังสือและข้อมูลที่ขาดไปภายในช่วงข้อมูลที่ใช้งานอยู่
- เรียกดูข้อมูลหนังสือบางส่วนที่ขาดหายไปโดยเรียกใช้ Open Library API โดยใช้วิธีการในตัวช่วย
fetchBookData_(ISBN) - อัปเดตค่าชื่อหนังสือหรือผู้แต่งที่ขาดหายไปในเซลล์ที่เกี่ยวข้อง
การใช้งาน
ใช้ฟังก์ชันใหม่นี้โดยทําตามขั้นตอนต่อไปนี้
- เพิ่มโค้ดต่อไปนี้ในตอนท้ายของสคริปต์สคริปต์ในเครื่องมือแก้ไข Apps Script
/**
* Fills in missing title and author data using Open Library API
* calls.
*/
function fillInTheBlanks(){
// Constants that identify the index of the title, author,
// and ISBN columns (in the 2D bookValues array below).
var TITLE_COLUMN = 0;
var AUTHOR_COLUMN = 1;
var ISBN_COLUMN = 2;
// Get the existing book information in the active sheet. The data
// is placed into a 2D array.
var dataRange = SpreadsheetApp.getActiveSpreadsheet()
.getDataRange();
var bookValues = dataRange.getValues();
// Examine each row of the data (excluding the header row).
// If an ISBN is present, and a title or author is missing,
// use the fetchBookData_(isbn) method to retrieve the
// missing data from the Open Library API. Fill in the
// missing titles or authors when they're found.
for(var row = 1; row < bookValues.length; row++){
var isbn = bookValues[row][ISBN_COLUMN];
var title = bookValues[row][TITLE_COLUMN];
var author = bookValues[row][AUTHOR_COLUMN];
if(isbn != "" && (title === "" || author === "") ){
// Only call the API if you have an ISBN number and
// either the title or author is missing.
var bookData = fetchBookData_(isbn);
// Sometimes the API doesn't return the information needed.
// In those cases, don't attempt to update the row.
if (!bookData || !bookData.details) {
continue;
}
// The API might not return a title, so only fill it in
// if the response has one and if the title is blank in
// the sheet.
if(title === "" && bookData.details.title){
bookValues[row][TITLE_COLUMN] = bookData.details.title;
}
// The API might not return an author name, so only fill it in
// if the response has one and if the author is blank in
// the sheet.
if(author === "" && bookData.details.authors
&& bookData.details.authors[0].name){
bookValues[row][AUTHOR_COLUMN] =
bookData.details.authors[0].name;
}
}
}
// Insert the updated book data values into the spreadsheet.
dataRange.setValues(bookValues);
}
- บันทึกโปรเจ็กต์สคริปต์
การตรวจสอบโค้ด
โค้ดนี้แบ่งออกเป็น 3 ส่วน ได้แก่
1: อ่านข้อมูลหนังสือที่มีอยู่
3 บรรทัดแรกของฟังก์ชันจะกําหนดค่าคงที่เพื่อช่วยให้อ่านโค้ดได้ง่ายขึ้น ใน 2 บรรทัดถัดไป ตัวแปร bookValues จะนําไปใช้เพื่อเก็บสําเนาของข้อมูลหนังสือในชีตในเครื่อง โค้ดจะอ่านข้อมูลจาก bookValues ใช้ API เพื่อกรอกข้อมูลที่ขาดหายไป และเขียนค่าเหล่านี้กลับไปที่สเปรดชีต
2: ดึงข้อมูลที่ขาดหายไปโดยใช้ฟังก์ชันตัวช่วย
โค้ดจะวนซ้ําในแต่ละแถวใน bookValues เพื่อค้นหาชื่อหนังสือหรือผู้แต่งที่ขาดหายไป ในการลดจํานวนการเรียก API ขณะปรับปรุงประสิทธิภาพ โค้ดจะเรียก API เฉพาะเมื่อเกิดเหตุการณ์ต่อไปนี้
- คอลัมน์ ISBN ของแถวมีค่า
- ชื่อเรื่องหรือเซลล์ของผู้เขียนในแถวว่าง
หากเงื่อนไขเป็นจริง โค้ดจะเรียก API โดยใช้ฟังก์ชันตัวช่วย fetchBookData_(isbn) ที่คุณใช้อยู่ก่อนหน้านี้ และเก็บผลลัพธ์ไว้ในตัวแปร bookData จากนั้นควรมีข้อมูลที่ขาดหายไปซึ่งต้องการแทรกลงในชีต
สิ่งที่คุณต้องทําเพียงอย่างเดียวคือเพิ่มข้อมูล bookData ลงในสเปรดชีต แต่ข้อควรระวังมีดังนี้ โชคไม่ดีที่ API สาธารณะอย่าง Open Library Book API ไม่มีข้อมูลที่คุณขอ หรือบางครั้งอาจเกิดปัญหาอื่นที่ทําให้ส่งข้อมูลไม่ได้ ถ้าคุณคิดว่าคําขอ API ทุกรายการจะสําเร็จ โค้ดของคุณจะไม่รัดกุมพอที่จะจัดการกับข้อผิดพลาดที่ไม่คาดคิด
โค้ดของคุณจะต้องตรวจสอบว่าการตอบกลับของ API นั้นถูกต้องก่อนที่จะพยายามใช้งาน เพื่อให้โค้ดจัดการกับข้อผิดพลาดของ API ได้ เมื่อรหัสมี bookData ก็จะทําการตรวจสอบง่ายๆ เพื่อยืนยันว่ามี bookData และ bookData.details อยู่แล้วก่อนที่จะพยายามอ่าน หากค่าใดค่าหนึ่งหายไป แสดงว่า API ไม่มีข้อมูลที่คุณต้องการ ในกรณีนี้ คําสั่ง continue จะบอกโค้ดให้ข้ามแถวนั้น คุณจะเติมเซลล์ที่ขาดหายไปไม่ได้ แต่อย่างน้อยสคริปต์ก็ไม่ขัดข้อง
3: เขียนข้อมูลที่อัปเดตกลับเข้าไปในชีต
ส่วนสุดท้ายของโค้ดจะมีการตรวจสอบที่คล้ายกันเพื่อยืนยันชื่อที่แสดงผลของ API และข้อมูลผู้เขียน โค้ดจะอัปเดตอาร์เรย์ bookValues หากชื่อเดิมหรือเซลล์ของผู้เขียนว่างเปล่าและ API แสดงผลค่าที่คุณจะวางได้
ลูปจะออกหลังจากตรวจสอบแถวทั้งหมดในชีตแล้ว ขั้นตอนสุดท้ายคือการเขียนอาร์เรย์ bookValues ที่อัปเดตใหม่กลับไปที่สเปรดชีตโดยใช้ Range.setValues(values)
ผลลัพธ์
ต่อไปนี้คุณจะล้างข้อมูลหนังสือให้เสร็จสิ้นได้แล้ว ทำสิ่งต่อไปนี้:
- หากยังไม่มี ให้ไฮไลต์ช่วง A2:A15 ในชีต แล้วเลือกรายการหนังสือและ gt; คั่นชื่อ/ผู้เขียนด้วยเครื่องหมายจุลภาคครั้งแรกเพื่อล้างปัญหาคอมมา
- หากยังไม่มี ให้ไฮไลต์ช่วง A2:A15 ในชีต แล้วเลือก Book-list > แยกชื่อ/ผู้เขียนเมื่อออก "by" ล่าสุดเพื่อล้างข้อมูล "by"
- หากต้องการเติมข้อมูลในเซลล์ที่เหลือทั้งหมด ให้เลือก Book-list > โปรดกรอกชื่อหนังสือเปล่าและเซลล์ของผู้เขียน
13 บทสรุป
ยินดีด้วยกับ Codelab นี้เสร็จสมบูรณ์แล้ว คุณได้ดูวิธีสร้างเมนูที่กําหนดเองเพื่อเปิดใช้งานส่วนต่างๆ ของโค้ด Apps Script แล้ว นอกจากนี้ยังดูวิธีนําเข้าข้อมูลไปยัง Google ชีตโดยใช้บริการ Apps Script และ API สาธารณะด้วย ซึ่งเป็นการดําเนินการทั่วไปในการประมวลผลสเปรดชีต และ Apps Script ช่วยให้คุณนําเข้าข้อมูลจากแหล่งที่มาที่หลากหลายได้ สุดท้าย คุณได้ดูวิธีใช้บริการ Apps Script และ JavaScript เพื่ออ่าน ประมวลผล และแทรกข้อมูลในสเปรดชีต
คุณคิดว่า Codelab นี้มีประโยชน์ไหม
สิ่งที่คุณได้เรียนรู้
- วิธีนําเข้าข้อมูลจากสเปรดชีตของ Google
- วิธีสร้างเมนูที่กําหนดเองในฟังก์ชัน
onOpen() - วิธีแยกวิเคราะห์และจัดการค่าข้อมูลสตริง
- วิธีเรียกใช้ API สาธารณะโดยใช้บริการเรียก URL
- วิธีแยกวิเคราะห์ข้อมูลออบเจ็กต์ JSON ที่ได้จากแหล่งที่มาของ API สาธารณะ
ขั้นต่อไปคืออะไร
Codelab ถัดไปในเพลย์ลิสต์นี้จะเจาะลึกเกี่ยวกับวิธีจัดรูปแบบข้อมูลภายในสเปรดชีต
ค้นหา Codelab ถัดไปที่การจัดรูปแบบข้อมูล