Tự động hóa điện lấy tên cột excel

Trong bài đăng trên blog này, chúng ta sẽ thấy 3 cách khác nhau [1 chậm và 2 nhanh] để lấy dữ liệu từ một bảng trong tệp excel bằng Power Automate. Chúng ta sẽ bắt đầu bằng cách nhìn vào

1. quy trình Power Automate sử dụng các tác vụ đơn giản nhưng mất nhiều thời gian để chạy,
2. sau đó chúng ta sẽ xem xét việc sử dụng kết hợp các hành động đơn giản và phương pháp của Pieter tốn ít thời gian hơn để chạy và
3. cuối cùng, chúng tôi sẽ sử dụng một vài hành động HTTP cùng với phương thức của Pieter, phương thức này sẽ chỉ mất vài giây để chạy

Tín dụng

Pieter Veenstra và John Liu viết những blog tuyệt vời trên Power Automate. Nội dung trong blog của họ rất tuyệt vời và giúp ích rất nhiều trong việc tạo các luồng Power Automate nhanh chóng. Chúng tôi sẽ sử dụng các kỹ thuật khác nhau được họ đề cập trong bài đăng này

Dữ liệu sẽ được sử dụng

Mục đích cuối cùng của chúng tôi là chuyển đổi dữ liệu excel lớn thành JSON. Ví dụ: chúng tôi có tệp excel bên dưới có một bảng tên là tblWork với 1500 hàng dữ liệu [20 nghìn hàng sau đó], hiển thị 3 công việc — Painting, Gardening và Plumbing được hoàn thành ở những ngôi nhà khác nhau

Chúng tôi dự định lấy dữ liệu đó thành 3 đối tượng JSON như được hiển thị bên dưới - mỗi đối tượng đại diện cho công việc đã hoàn thành với một mảng 'Số nhà', 'Đường phố' và 'Thành phố'. Dữ liệu này sau đó có thể được sử dụng để phân tích/báo cáo thêm

Phương pháp 1 — Tránh nếu có thể

Một cách đơn giản để lấy dữ liệu là sử dụng hành động “Liệt kê các hàng có trong bảng”. Chúng tôi chuyển các chi tiết cần thiết cho hành động này tôi. e. vị trí của tệp excel và bảng [_______0] chứa dữ liệu và chúng tôi sẽ nhận được các hàng dưới dạng JSON

Hành động hàng danh sách sử dụng Microsoft Graph để lấy dữ liệu. Vì vậy, để lấy tệp một cách linh hoạt, vui lòng tham khảo bài đăng này của John Liu

Sau đó, chúng tôi lặp qua các hàng [song song nếu cần] và đối với mỗi hàng, hãy kiểm tra xem Painting có trống không và thu thập House number, Street và City liên quan đến hàng đó và nối nó vào một biến mảng. Làm tương tự cho Gardening và Plumbing

Khi vòng lặp hoàn thành thực thi, các biến mảng sẽ có các hàng cần thiết và chúng ta có thể sử dụng hành động say Compose để lấy dữ liệu cần thiết

Painting1

Thời gian thực hiện

Đây là một phương pháp đơn giản và hoạt động tốt với dữ liệu nhỏ, tuy nhiên, nếu chúng ta có dữ liệu lớn thì thời gian thực hiện sẽ rất lâu

Trong các phương pháp tiếp theo, chúng ta sẽ xem cách giảm thời gian thực hiện cho những hành động này

Phương pháp 2

Hãy xem xét việc giảm thời gian thực hiện cho hành động 'Áp dụng cho mỗi hàng'. Lý do chính khiến nó mất nhiều thời gian hơn là vì hành động đó sử dụng các biến bên trong nó. Như John Liu giải thích, Power Automate cần lấy khóa cho biến, đặt giá trị biến rồi nhả khóa khi cần thực hiện một số tác vụ trên biến. Do đó chúng ta cần một cách để tránh các biến

Để làm điều đó, chúng tôi sẽ sử dụng các hành động 'Lọc', 'Chọn' và chúng tôi sẽ nhận được dữ liệu cần thiết bằng phương pháp tuyệt vời của Pieter

dòng chảy

Quy trình được giải thích bên dưới có thể được tải xuống từ kho lưu trữ GitHub tại đây

Chúng tôi sẽ bắt đầu bằng cách sử dụng hành động “Liệt kê các hàng có trong bảng” để lấy dữ liệu từ excel. Sau đó, chúng tôi tạo một mảng đại diện cho các cột/tiêu đề trong excel

Painting2

Các tên cột/tiêu đề này có thể được lưu trữ trong danh sách trong SharePoint hoặc bất kỳ nơi nào khác nếu cần để không cần phải chỉnh sửa quy trình sau này

Sau đó, chúng tôi sẽ lặp qua từng cột/tiêu đề,

1. lọc các hàng mà cột hiện tại không trống và
2. sau đó chọn House number, Street và City liên quan đến hàng đó

Điều này sẽ cung cấp cho chúng tôi dữ liệu mảng JSON cho Vẽ tranh, Làm vườn và Hệ thống nước theo thứ tự đó và nó sẽ được sắp xếp theo thứ tự tăng dần theo hàng. Sau đó, chúng tôi sử dụng phương pháp của Pieter để lấy JSON được yêu cầu. e. sử dụng phần thân của hành động chọn bên ngoài vòng lặp

Khi hành động chọn được tham chiếu bên ngoài vòng lặp, nó sẽ nối tất cả các giá trị trong vòng lặp. Vì vòng lặp được thực hiện cho 3 cột/tiêu đề nên dữ liệu được nối sẽ có 3 giá trị. Mục đầu tiên của dữ liệu được nối sẽ là dữ liệu Painting

Painting7

Tương tự, đối với Làm vườn và Hệ thống nước, chúng ta cần lấy mục thứ hai và thứ ba của dữ liệu được nối i. e

Painting8

Painting9

Bằng cách sử dụng phương pháp của Pieter, chúng tôi đã giảm thời gian thực hiện từ 20 phút xuống còn 1 giây

Thông tin bổ sung — Trong vòng lặp “Áp dụng cho từng cột”, chúng ta có thể sử dụng thao tác Chọn trước và Lọc dữ liệu nếu cần. Thay vào đó, chúng ta sẽ cần viết một biểu thức trong Chọn cho điều đó

Gardening0

Phương pháp 3

Trong phương pháp cuối cùng, chúng ta sẽ xem cách giảm thời gian thực hiện cho hành động “Liệt kê các hàng có trong bảng”. Chúng ta có thể sử dụng phương pháp này khi dữ liệu chúng ta đang xử lý lớn và/hoặc thời gian thực hiện cần nhỏ hơn. Ngoài ra, phương pháp này sử dụng hành động HTTP cao cấp với thông tin đăng nhập của người dùng

Hành động “Liệt kê các hàng có trong một bảng” lấy các hàng có trong một bảng trang tính excel được chỉ định. Chúng tôi có thể trực tiếp sử dụng điểm cuối Danh sách hàng của Microsoft Graph API để lấy dữ liệu đó. [Hoặc tùy chọn khác là sử dụng dữ liệu phạm vi như Pieter đề cập tại đây]

Vì điểm cuối này yêu cầu quyền Gardening1 được ủy quyền nên chúng tôi sẽ cần mã thông báo truy cập được ủy quyền. Để có được điều đó, chúng tôi sẽ cần phải đăng ký một ứng dụng trong Azure AD và cung cấp quyền được ủy quyền của Gardening1 cho đăng ký ứng dụng đó

Chúng tôi sẽ sử dụng tệp excel trong OneDrive làm nguồn. Vì vậy, chúng tôi sẽ cần một tài khoản với OneDrive và thông tin đăng nhập của tài khoản đó. [Lưu ý — Tôi đã thử sử dụng SharePoint làm nguồn cho API này, tuy nhiên tôi không thể lấy dữ liệu từ tệp đó bằng phương pháp bên dưới — hoan nghênh các nhận xét liên quan đến điều này]

dòng chảy

Quy trình được giải thích bên dưới có thể được tải xuống từ kho lưu trữ GitHub tại đây

Bước đầu tiên trong quy trình là gọi hành động HTTP với các cài đặt sau

Bước này sẽ trả về mã thông báo truy cập được ủy quyền mà một điều chúng ta có thể làm là truy cập dữ liệu có trong các tệp trong OneDrive

Sau đó, chúng tôi sử dụng một hành động HTTP khác để gọi điểm cuối Microsoft Graph List Rows và với mã thông báo truy cập này trong tiêu đề

Điều này sẽ trả về dữ liệu dưới dạng JSON — một ví dụ mẫu được hiển thị bên dưới và chúng tôi quan tâm đến việc lấy dữ liệu “giá trị”

Trong dữ liệu giá trị, chúng tôi biết rằng chúng tôi có dữ liệu theo thứ tự - số nhà, đường phố, thành phố, sơn, làm vườn và hệ thống ống nước. Nghĩa

1. giá trị của “Tranh” được thể hiện bằng dữ liệu tại chỉ mục thứ 3
2. giá trị của “Làm vườn” được thể hiện bằng dữ liệu ở chỉ mục thứ 4
3. giá trị của “Hệ thống ống nước” được thể hiện bằng dữ liệu ở chỉ số thứ 5

Vì vậy chúng ta cần kiểm tra điều kiện rỗng tại các chỉ số này. Để làm được điều đó, chúng tôi tạo một mảng với các chỉ số này - Gardening3, lặp qua các chỉ số đó và kiểm tra giá trị hàng trống cho chỉ mục đó. Khi chúng tôi đã lọc các giá trị, tất cả những gì chúng tôi cần là sử dụng chọn để ánh xạ dữ liệu

Trong hành động chọn, House number sẽ được lấy từ Gardening5, Street sẽ được lấy từ Gardening7 và City sẽ được lấy từ Gardening5

Sau đó, chúng tôi sử dụng phương thức của Pieter như chúng tôi đã làm trước đó bên ngoài vòng lặp để lấy JSON được yêu cầu

Thời gian thực hiện

Với những hành động này, thời gian giờ đây được giảm xuống còn 1 giây để đọc các hàng của bảng. Tức là chỉ cần 1 giây để đọc khoảng 1500 hàng dữ liệu

Nếu chúng tôi tăng dữ liệu trong tệp excel lên 20 nghìn hàng thì bằng cách sử dụng phương pháp này, chúng tôi sẽ nhận được dữ liệu cần thiết trong khoảng 10 giây

Phần kết luận

Chúng ta có thể thử và tránh sử dụng vòng lặp for each với các biến bên trong chúng, bất cứ khi nào có thể. Thay vào đó, chúng tôi có thể sử dụng bộ lọc và chọn các hành động nhanh và giúp chúng tôi lấy dữ liệu cần thiết sau vài giây. Kết hợp những thứ đó với phương pháp của Pieter giúp chúng tôi thực hiện các quy trình của mình rất nhanh