วิธีลัดสร้างตาราง Excel ขนาดใหญ่ด้วย Power Automate

การส่งออกข้อมูลไปยังไฟล์ Excel นั้นทำได้ง่ายมากด้วย Power Automate โดยใช้คำสั่ง “Add a row into table” แต่ปัญหาคือ ช้า และ ไม่มีประสิทธิภาพ โพสต์นี้ผมจะแนะนำวิธีที่ทำให้การทำงานเร็วขึ้น แต่ยังคงได้ผลลัพธ์เหมือนเดิม
วิธีลัดสร้างตาราง Excel ขนาดใหญ่ด้วย Power Automate

บทนำ

การส่งออกข้อมูลไปยังไฟล์ Excel นั้นทำได้ง่ายมากด้วย Power Automate โดยใช้คำสั่ง “Add a row into table” แต่ปัญหาหลักคือ ช้า และ ไม่มีประสิทธิภาพ

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

ชุดข้อมูล (Dataset)

ผมจะใช้ชุดข้อมูลตัวอย่างที่ชื่อว่า ‘Data100K’ ซึ่งมี 8 คอลัมน์ ดังที่เห็นในตัวอย่างด้านล่าง

ผมจะใช้ชุดข้อมูลตัวอย่างที่ชื่อว่า ‘Data100K’ ซึ่งมี 8 คอลัมน์ ดังที่เห็นในตัวอย่างด้านล่าง

ปัญหา (วิธีที่ช้า)

มาดูกันว่าการส่งออกข้อมูลตัวอย่าง 1,000 รายการไปยัง Excel ด้วยวิธีปกติจะใช้เวลานานแค่ไหน

Flow นี้ค่อนข้างเรียบง่าย ดึงข้อมูลจาก SharePoint list และเพิ่มแต่ละแถวลงใน Excel ทีละแถวโดยใช้ “Apply to each”

Flow นี้ค่อนข้างเรียบง่าย ดึงข้อมูลจาก SharePoint list และเพิ่มแต่ละแถวลงใน Excel ทีละแถวโดยใช้ “Apply to each”

ผมได้เปิดใช้งานการควบคุมความพร้อมกัน (Concurrency control) ไว้ที่ 10 เท่าแล้ว

ผมได้เปิดใช้งานการควบคุมความพร้อมกัน (Concurrency control) ไว้ที่ 10 เท่าแล้ว

แต่ก็ยังใช้เวลาถึงประมาณ 10 นาทีในการส่งออกข้อมูลเพียง 1,000 รายการ ซึ่งช้ามาก ๆ

แต่ก็ยังใช้เวลาถึงประมาณ 10 นาทีในการส่งออกข้อมูลเพียง 1,000 รายการ ซึ่งช้ามาก ๆ

Solution (วิธีที่เร็ว)

ตอนนี้ถึงเวลาที่จะมาใช้วิธีที่เร็วกว่า โดยการใช้ Excel Office Script

  • Excel Office Script คืออะไร?

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

Office Script นี้ทำงานบนคลาวด์ ดังนั้นคุณต้องใช้งานผ่าน Excel เวอร์ชันเว็บเพื่อสร้างสคริปต์

ขั้นตอนการใช้ Excel Office Script

  • 1. สร้างไฟล์ Excel 

แค่สร้างไฟล์ Excel ว่างๆ ใน OneDrive หรือ SharePoint ของคุณ จากนั้นไปที่แท็บ ‘Automate’ แล้วคลิก ‘New Script’

แค่สร้างไฟล์ Excel ว่างๆ ใน OneDrive หรือ SharePoint ของคุณ จากนั้นไปที่แท็บ ‘Automate’ แล้วคลิก ‘New Script’
  • 2. สคริปต์

ตั้งชื่อสคริปต์อะไรก็ได้ที่คุณต้องการ แล้ววางโค้ดนี้ใน Code Editor

				
					function main(workbook: ExcelScript.Workbook, tables: string = "[]") { 

    const data: { [key: string]: string | number | boolean }[] = JSON.parse(tables); 

    const sheet = workbook.getActiveWorksheet(); 

    if (data.length === 0) return; 

    const headers = Object.keys(data[0]); 

    const headerRange = sheet.getRangeByIndexes(0, 0, 1, headers.length); 

    headerRange.setValues([headers]); 

    const dataRangeValues = data.map(item => headers.map(header => item[header])); 

    const dataRange = sheet.getRangeByIndexes(1, 0, data.length, headers.length); 

    dataRange.setValues(dataRangeValues); 

    const lastRow = data.length + 1; 

    const lastColumn = headers.length; 

    const tableRange = sheet.getRangeByIndexes(0, 0, lastRow, lastColumn); 

    sheet.addTable(tableRange, true); 

} 
				
			
ตั้งชื่อสคริปต์อะไรก็ได้ที่คุณต้องการ แล้ววางโค้ดนี้ใน Code Editor

บันทึกสคริปต์ แล้วก็เสร็จเรียบร้อย!

  • 3. การทำงานของ Flow

มาดู Flow ที่เราต้องตั้งค่าใน Power Automate แทนที่จะใช้คำสั่ง ‘Add a row into table’ ตอนนี้เราจะใช้คำสั่ง ‘Run script’ แทน

มาดู Flow ที่เราต้องตั้งค่าใน Power Automate แทนที่จะใช้คำสั่ง ‘Add a row into table’ ตอนนี้เราจะใช้คำสั่ง ‘Run script’ แทน

เลือกตำแหน่งของไฟล์ Excel ที่ต้องการส่งออกข้อมูล (หรือจะใช้ dynamic content ก็ได้)

สคริปต์ที่บันทึกไว้ใน OneDrive โดยไม่ผูกติดกับไฟล์ ดังนั้นสามารถทำงานได้กับทุกไฟล์ Excel โดยไม่จำเป็นต้องสร้างสคริปต์ใหม่ทุกครั้ง

สคริปต์ที่บันทึกไว้ใน OneDrive โดยไม่ผูกติดกับไฟล์ ดังนั้นสามารถทำงานได้กับทุกไฟล์ Excel โดยไม่จำเป็นต้องสร้างสคริปต์ใหม่ทุกครั้ง

พารามิเตอร์สำคัญคือ ‘tables’ ที่จะปรากฏหลังจากเลือกสคริปต์นี้ คุณต้องใส่ข้อมูลในรูปแบบ Array ที่จะเขียนลงใน Excel โดยใช้ฟังก์ชัน string()

ยกตัวอย่าง Array ของผมที่ได้จาก dynamic content

				
					body('Select') 
				
			
ยกตัวอย่าง Array ของผมที่ได้จาก dynamic content

ผมจะเขียนโค้ดแบบนี้

				
					string(body('Select')) 
				
			
ผมจะเขียนโค้ดแบบนี้
  • 4. บันทึกและรัน

ส่วนที่ผมชอบที่สุดก็คือตอนบันทึกและรัน Flow เพื่อดูว่ามันทำงานได้หรือเปล่า

จากที่เห็น Flow นี้ใช้เวลาแค่ 7 วินาที เท่านั้น! เร็วมากๆ เลยใช่ไหม?

ส่วนที่ผมชอบที่สุดก็คือตอนบันทึกและรัน Flow เพื่อดูว่ามันทำงานได้หรือเปล่า

และข้อมูลทั้งหมดก็ถูกส่งออกไปยัง Excel ได้สำเร็จภายในเวลาเพียง 7 วินาที!

สรุป

  • ทดสอบด้วยข้อมูลตัวอย่าง 1,000 รายการ
  • วิธีช้าใช้เวลาประมาณ 10 นาที
  • วิธี Office Script ใช้เวลาแค่ 7 วินาที
    • เร็วกว่า 85 เท่า!
    • ไม่ต้องใช้ “Apply to each”

ด้วยวิธีนี้ คุณจะสามารถจัดการข้อมูลจำนวนมากได้อย่างรวดเร็ว ประหยัดเวลาได้เยอะเลย!

ก้าวเข้าสู่ Digital Business

ดูผลิตภัณฑ์ที่เกี่ยวข้องได้ที่นี่

แหล่งที่มา : PAWIT.PW

Table of Content