Cách dùng hàm ImportXML của Google Sheets để thu thập dữ liệu khắp nơi

4,8 ★ 4 👨
91

Bạn có thể nhập dữ liệu từ trang web bất kỳ bằng hàm ImportXML của Google Sheets. Dưới đây là cách sử dụng hàm ImportXML trên Google Sheets.

Khi làm chủ hàm ImportXML của Google Sheet, bạn sẽ thấy như đã sở hữu một Sheets Wizard được chứng nhận. ImportXML lấy thông tin từ trường XML bất kỳ. Nhờ đó, bạn có thể tải dữ liệu và metadata được tạo ra trên đó ở bất kỳ nơi đây.

Khái niệm cơ bản về XML và HTML

Ngôn ngữ đánh dấu XML chỉ định các bộ dữ liệu trong một trang web. Về bản chất, bất kỳ bộ <something></something> - các khối xây dựng của mã nguồn web hay một tập hợp dữ liệu nhất định sẽ nằm bên trong chúng. Mã nguồn của web sẽ có một số text trong thẻ <p>aragraph - đoạn văn, đôi khi chứa <b>old - chữ in đậm và có thể cả <a>a link - liên kết (được theo sau bởi </a></b>.</p></body> để đóng toàn bộ thẻ).

Hàm ImportXML của Google Sheets có thể tìm một bộ dữ liệu XML nào đó và sao chép dữ liệu bên ngoài nó. Ở ví dụ trên, nếu muốn lấy toàn bộ liên kết trên trang, chúng ta cần yêu cầu hàm ImportXML nhập toàn bộ thông tin trong tag <a></a>. Nếu muốn toàn bộ text của một web, bạn có thể bắt đầu bằng cách lấy mọi thứ trong <body></body> hoặc mỗi phiên bản của <p></p>, rồi xóa dữ liệu ở các giai đoạn sau.

Cách trích xuất một danh sách mã bưu điện và quận trong thành phố

Bảng biểu trong Wikipedia là bài luyện tập ImportXML tuyệt vời. Bài viết sẽ lấy ví dụ tải toàn bộ mã bưu điện ở Edmonton, Alberta. Tìm danh sách mã bưu điện của Canada bắt đầu bằng chữ T. Mở trang đó trong cửa sổ trình duyệt mới để bắt đầu.

Xem nguồn trang

Chọn một mã bưu điện, click chuột phải vào nó và chọn Inspect để mở công cụ trình duyệt xem mã nguồn trang. Bạn sẽ thấy mỗi mã nguồn trang nằm trong một tag (xác định một ô trong bảng). Sau đó, bài viết sẽ nhập toàn bộ tag TD chứa từ Edmonton trong chúng.

Tạo một bảng tính Google Sheet trống mới. Bài viết sẽ lấy toàn bộ nội dung tag TD, bao gồm <span> và liên kết bằng cách xác định dữ liệu muốn dùng cú pháp Xpath. ImportXML lấy URL và tag bạn đang tìm làm đối số để nhập vào Google Sheets.

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td")

Bạn sẽ nhận được kết quả như sau:

Nhập kết quả vào bảng dữ liệu

Quay lại nguồn trang, chúng ta sẽ thấy mã bưu chính được in đậm trong thẻ <b></b>, tên thành phố liên kết tới các bài báo Wikipedia nằm trong <a></a>. Giờ hãy thử chỉ lấy liên kết trong mỗi ô thành phố lớn và loại bỏ các liên kết khác (khu phố). Chỉnh sửa chúng thành hai lệnh trọng cột A và B:

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td/span/a[1]")

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td/b[1]")

Bạn cần tinh chỉnh kết quả một chút:

Tinh chỉnh kết quả nhận được

Hành động này giúp bạn hiểu cách cú pháp truy vấn XPath hoạt động: một thẻ chỉ cung cấp phiên bản đầu tiên của <tag> trong <parent tag>. Vì thế, td/span/a[1] cho bạn liên kết đầu tiên trong <span> ở mỗi <td>. Tương tự như vậy, td/b[1] cho bạn text in đậm trước tiên trong mỗi <td> hoặc chỉ mã bưu điện ở trường hợp này.

Điều tuyệt vời là bạn có thể thực hiện hai truy vấn trong một hàm. Vì thế, bài viết kết hợp hai yêu cầu bằng một biểu tượng | ở giữa:

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td/span/a[1] | //td/b[1]")

Tuy nhiên, bạn sẽ không nhận được cùng kết quả trước đó. Nó sẽ xen kẽ toàn bộ yêu cầu kết hợp vào một danh sách dài, thay vì hai cột. Nó có nhiều lợi ích nhưng không cần thiết ở bài viết này.

Cột dữ liệu tên thành phố chính

Để chọn mã bưu chính trong các box chứa liên kết ‘Edmonton’. Chúng ta sẽ dùng mã này:

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td[span/a='Edmonton']/b[1]")

Đặt phần “search” - text đủ điều kiện thu hẹp kết quả trong dấu ngoặc vuông mà không làm ảnh hưởng tới cách thức mang đến kết quả.

Toàn bộ mã code kết hợp Edmonton tương ứng

Giờ tới các tên các khu vực lân cận. Viết hàm importXML phù hợp vào cột tiếp theo, lấy text sau từ “Edmonton.”

Bài viết lấy toàn bộ nội dung của span[1] và dùng dấu ngoặc đơn và chéo để phân chia nội dung, đưa “Edmonton” vào cột đầu tiên và tên khu vực lân cận vào cột sau. Sau đó, chúng ta có thể kết hợp mã bưu chính với tên tương ứng:

=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td[span/a='Edmonton']/span[1]")

Tiếp theo, dùng hàm Split và nối một số cột sau đó để chia tách & nhóm dữ liệu đang xử lý:

=SPLIT(concatenate(B2:J2),"(/)")

Cuối cùng, đây là bảng kết quả với thông tin cần thiết:

Bảng dữ liệu chứa các thông tin cần thiết

Cách tự động sao chép địa chỉ email từ web

Cách lấy email bằng hàm ImportXML trong Google Sheets

Bài viết sẽ hướng dẫn bạn cách lấy toàn bộ email nhân viên trên trang About | Zapier. Nhìn vào mã nguồn, bạn sẽ thấy mọi địa chỉ email của từng thành viên đều nằm trong trường class=”email”. Khi muốn chỉ định một thuộc tính tag, dùng hàm ImportXML của Google Sheets như sau:

=importxml("https://zapier.com/about//", "//span[@class='email']")

Cách dùng Regex để nhập địa chỉ Email từ web trong Google Sheets.

Để lấy các địa chỉ Zapier bằng cách dùng “sức mạnh” của Regex, chúng ta sẽ nhập lệnh <span> thay vì tìm class. Giờ chúng ta sẽ thực hiện nhiệm vụ này trong hai bước: Gọi thông tin từ trang Zapier vào cột đầu tiên, sau đó, phân loại email vào cột thứ hai:

=importxml("https://zapier.com/about//", "//span")

=regexextract(A1, "[a-zA-Z0-9_\.\+-]+@[a-zA-Z0-9-\.]+\.[a-zA-Z0-9-]{2,15}")

Cuối cùng, chúng ta sẽ có bảng này:

Trích xuất địa chỉ email từ web

Nhớ rằng, ImportXML sẽ tự điền vào tất cả các cột và hàng tùy thuộc vào dữ liệu nó tìm thấy. Truy vấn regex phải được điền vào từng ô bạn muốn có kết quả. Để kết hợp tất cả lại với nhau, bạn chỉ cần dùng lệnh Regexextract là một công thức hằng số mảng (array):

=ArrayFormula(IFERROR(REGEXEXTRACT(IMPORTXML("https://zapier.com/about//", "//span"), "[a-zA-Z0-9_\.\+-]+@[a-zA-Z0-9-\.]+\.[a-zA-Z0-9-]{2,15}")))

Và đây là kết quả:

Trích xuất email bằng hàm Regex

Hi vọng bài viết hữu ích với các bạn!

Cập nhật: 18/02/2020
4,8 ★ 4 👨
91