Tôi một người đam mê các bảng tính và tôi thậm chí còn là thành viên của một nhóm quan tâm đến các bảng tính. Số lượng những người đam mê mà tôi đã gặp ở đó khiến tôi nghĩ rằng chúng ta đã từng sử dụng bảng tính tại một thời điểm nào đó trong sự nghiệp của chúng ta.
Ngay cả trong lĩnh vực này, Google Sheets là một siêu anh hùng thực sự. Bảng tính của Google Sheets có thể tự động thu thập thông tin cho bạn trong khi bạn ngủ, và thu thập bất kỳ thông tin nào bạn muốn (giá cổ phiếu, phân tích trang web, và nhiều hơn thế nữa) từ bất kỳ đâu.
Nhưng làm thế nào nếu bạn muốn thu thập dữ liệu từ web nói chung, có lẽ để sao chép thông tin từ một bảng trên một trang web? Có thể có một danh sách sự kiện, một lưới sự thật hoặc các địa chỉ email phân tán trên một trang web. Việc sao chép và dán tất cả điều đó sẽ mất cả một cả để hoàn thành – nhưng Google Sheets cung cấp một tùy chọn tốt hơn.
Bạn có thể nhập dữ liệu từ bất kỳ trang web nào bằng cách sử dụng một chức năng nhỏ gọi là ImportXML, và một khi bạn làm chủ nó, bạn sẽ cảm thấy mình như là một nhà phép thuật thực sự của bảng tính. ImportXML trích xuất thông tin từ bất kỳ trường XML nào, tức là bất kỳ trường giữa các cặp thẻ và . Do đó, bạn có thể thu thập dữ liệu từ bất kỳ trang web nào và các siêu dữ liệu được tạo bởi bất kỳ trang web nào, ở bất kỳ đâu. Tất nhiên, bạn có thể sao chép và dán và dành hàng giờ để chỉnh sửa tất cả mọi thứ thủ công, nhưng tại sao không tự động hóa những tác vụ buồn tẻ?
Hãy làm chính xác điều đó.
Cơ bản về XML và HTML
Bạn sẽ cần biết những cơ bản rất đơn giản về HTML, hoặc chính xác hơn là thẻ XML, để hiểu các chức năng thông thường ở đây. Về cơ bản, mọi thẻ và – các yếu tố cơ bản của mã nguồn trang web – có nghĩa là một tập hợp dữ liệu nằm bên trong (có thể là ). Nội dung của một trang web đôi khi có một đoạn văn bản
, có thể có một văn bản in đậm và có thể có một liên kết(theo sau là.
Chức năng ImportXML của Google Sheets có thể tìm kiếm một tập hợp dữ liệu XML cụ thể và sao chép dữ liệu phát sinh từ đó.
Vì vậy, trong ví dụ trên, nếu chúng ta muốn thu thập tất cả các liên kết trên một trang, chúng ta sẽ chỉ định cho chức năng ImportXML của chúng ta để nhập tất cả thông tin nằm giữa các thẻ . Nếu chúng ta muốn thu thập tất cả văn bản trên một trang web vì chúng ta đang làm công việc phân tích văn bản nâng cao, chúng ta có thể bắt đầu bằng cách thu thập tất cả những gì nằm giữa các thẻ p hoặc tất cả những gì nằm giữa mỗi thể hiện của , sau đó chúng ta sẽ làm sạch dữ liệu theo từng bước sau đó.
Nếu chúng ta nói với ImportXML để thu thập các liên kết từ ví dụ trên, chúng ta sẽ nhận được văn bản “liên kết“. Điều này có thể không quá hữu ích, nhưng ít nhất bạn đã hiểu ý tưởng.
Cách trích xuất danh sách mã bưu điện và khu phố của một thành phố
Dự án hiện tại của tôi là khớp danh sách khách hàng của mình với mã bưu điện của họ trong một quận thành phố của tôi. Đây là một dự án khá nhỏ, vì tôi chỉ sử dụng một số quận trung tâm thành phố, nhưng khá khó khăn, vì không có bộ dữ liệu nào cho mã bưu điện của chúng ta ở Canada. Đúng, nghiêm trọng – Canada Post đã kiện ai đó một lần vì đã xuất bản một danh sách tất cả các mã bưu điện.
May mắn thay, một người thông minh đã tạo ra một phiên bản thay thế trên Wikipedia: một bảng mã bưu điện tiếp sau bởi các đô thị và khu phố chứa chúng.
Các bảng Wikipedia là một cách tuyệt vời để thực hành ImportXML. Hãy thử lấy tất cả các mã bưu điện của Edmonton, Alberta. Chúng ta sẽ nhìn vào phần “AB” của hệ thống bưu chính, các mã bắt đầu bằng T. Hãy mở trang này trong một cửa sổ trình duyệt mới để làm bài tập này.
Hãy nhìn vào mã nguồn trang. Chọn một mã bưu điện, nhấp chuột phải vào nó và chọn “Inspect” để mở công cụ trình duyệt của bạn để xem mã nguồn trang.
Có vẻ như mỗi mã bưu điện được chứa trong một thẻ
(mô tả một ô trong bảng). Vì vậy, chúng ta sẽ nhập tất cả các thẻ TD chứa từ “Edmonton”.Cho bài học đầu tiên của bạn, hãy tạo một bảng tính Google Sheets trống mới. Chúng ta sẽ lấy tất cả nội dung của thẻ TD, bao gồm và các liên kết, bằng cách chỉ định những gì chúng ta muốn bằng cú pháp XPath. ImportXML lấy URL và các thẻ bạn muốn tìm kiếm làm đối số, vì vậy hãy nhập điều này 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:
Trở lại mã nguồn của chúng ta, chúng ta thấy rằng mã bưu điện được in đậm, hoặc , và các tên thành phố trỏ đến các bài viết Wikipedia, dĩ nhiên, trong . Hãy thử chỉ thu thập liên kết đầu tiên trong mỗi ô, đó là thành phố chính, và bỏ qua các liên kết khác, đó là khu phố. Hãy chỉnh sửa điều này thành hai câu lệnh, trong 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]")
Và bạn sẽ rà đút kết quả của bạn một chút nữa:
Điều này sẽ cung cấp cho bạn một ý tưởng về cách cú pháp truy vấn XPath hoạt động: một thẻ với [1] có nghĩa là “chỉ cung cấp cho tôi mục đầu tiên trong ở bên trong “. Vì vậy, td/span/a[1] sẽ cung cấp cho bạn liên kết đầu tiên trong bên trong mỗi
td/b[1] sẽ cung cấp cho bạn văn bản in đậm đầu tiên trong mỗi
, hoặc chỉ mã bưu điện trong trường hợp của chúng ta.
Một điều thú vị bạn có thể làm là thực hiện hai truy vấn bằng một hàm duy nhất. Vì vậy, chúng ta có thể kết hợp hai truy vấn này bằng một ký hiệu | (pipe) ở 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 kết quả giống như trước đây: nó sẽ xen kẽ tất cả các truy vấn phù hợp vào một danh sách dài mà không có phân chia thành hai cột. Có nhiều ứng dụng của điều này, nhưng điều này không phải là điều chúng ta đang tìm kiếm ở đây.
Hơn nữa, chúng ta không muốn tất cả các dòng này; chúng ta chỉ muốn những dòng khớp với “Edmonton” trong trường td/span/a[1]. Bạn nhớ rằng chúng ta muốn trả lại mã bưu điện, vì vậy chúng ta muốn yếu tố b[1] của mỗi
chứa “Edmonton” trong span/a[1]. Vẫn cùng tôi chứ?Để chỉ chọn mã bưu điện trong các ô nơi các liên kết đầu tiên là “Edmonton”, chúng tôi sẽ 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]")
Chúng ta đặt phần “tìm kiếm” – văn bản phân loại kết quả của chúng ta – giữa [dấu ngoặc vuông], mà không làm ảnh hưởng đến đường dẫn cung cấp thực sự kết quả. Đó là điều đó!
Bây giờ chúng ta muốn tên các khu phố, hãy viết một hàm ImportXML tương ứng khác ở cột tiếp theo, đang lấy văn bản ngay sau từ “Edmonton”.
Giải pháp của tôi lấy toàn bộ nội dung span[1] và sử dụng dấu ngoặc đơn và dấu gạch chéo để chia nội dung, cắt “Edmonton” vào cột đầu tiên và mỗi tên khu phố vào các cột tiếp theo. Từ quá trình hai bước này, chúng ta có thể khớp mã bưu điện và tên khu phố:
=importxml("https://en.wikipedia.org/wiki/List_of_T_postal_codes_of_Canada", "//td[span/a='Edmonton']/span[1]")
Sau đó, vài cột xa hơn, sử dụng các hàm SPLIT và CONCATENATE để tách và kết hợp dữ liệu mà bạn đang làm việc:
=SPLIT(concatenate(B2:J2),"(/)")
Điều này sẽ cho chúng ta bảng cuối cùng, đã làm sạch, chỉ chứa mã bưu điện, tên thành phố và thông tin về các khu phố mà chúng ta cần:
Nếu bạn bắt đầu hiểu, bạn có thể cải thiện phương pháp này. Hãy tìm cách chỉ thu thập nội dung sau a[1], hoặc chỉ văn bản nằm trong ngoặc đơn, hoặc tất cả trừ chuỗi “Edmonton”, hoặc tất cả sau xuống dòng
.
Cách sao chép tự động địa chỉ email từ một trang web
Nó thật dễ dàng: bạn có thể trích xuất tất cả các địa chỉ email của nhân viên Zapier từ trang Về chúng tôi?
Một cái nhìn vào mã nguồn sẽ cho bạn biết ngay: mỗi địa chỉ email của từng thành viên trong nhóm Zapier được đặt trong một trường với lớp “email”. Dễ dàng! Khi bạn muốn chỉ định một thuộc tính của một thẻ (ví dụ: “href” trong một , hoặc “id” hoặc “class” trong một
=importxml("https://zapier.com/about//", "//span[@class='email']")
Lấy một địa chỉ email mà không cần biểu tượng như những cái này có thể được thực hiện. Chúng tôi sử dụng nó bằng cách nắm bắt hình thức cần thiết (tênngười dùng@miền.tiền tố, ví dụ [email protected]). Điều này phức tạp hơn, nhưng nó có nhiều tiềm năng hơn rất nhiều.
Một biểu thức chính quy là những gì chúng ta sử dụng để chắt lọc các thông tin trong đúng định dạng. Hãy nói rằng chúng ta muốn biết tất cả các nhiệt độ được liệt kê trên một trang web thời tiết. Chúng ta sẽ chỉ định “đưa cho chúng tôi tất cả các số đi trước các biểu tượng ° hoặc ℃ hoặc ℉” – vâng, đó là tất cả các ký tự unicode khác nhau.
Nếu chúng ta muốn thu thập một danh sách các địa chỉ email, chúng ta sẽ nói “đưa cho tôi tất cả những chuỗi khớp với định dạng tênngười dùng@miền.tiền tố”. Hoặc, với biểu thức chính quy:
[a-zA-Z0-9_-.+]+@[a-zA-Z0-9-.]+.[a-zA-Z0-9-]{2,15}
Hãy thở dài, và chúng ta sẽ đi qua từng bước một. Bạn có thể thấy ký hiệu @, và bạn có thể thấy rằng không gian “tênngười dùng” trước @ (hoặc là [a-zA-Z0-9_.+-]+) khá giống với không gian “miền” sau @ (hoặc là [a-zA-Z0-9-.]+).
Và phần “tiền tố” trông giống như, nhưng không hoàn toàn. Điều đó là vì các ký tự được chấp nhận trong một địa chỉ email và trong một tên miền, như được xác định bởi các vị thần của Internet, là giới hạn. Bạn có thể nhớ từng đăng ký một địa chỉ email và nhận được thông báo lỗi khi bạn cố gắng nhập “~f41ry~”. Tôi cũng biết cảm giác này. Đó là vì địa chỉ email chấp nhận chữ thường (a-z), chữ in hoa (A-Z), chữ số (0-9), gạch dưới (_), gạch ngang (-) và dấu chấm (.) – và đôi khi cộng thêm dấu cộng (+).
Vậy còn các dấu “/” và “+” trong biểu thức này? Dấu gạch ngang và dấu chấm đã chỉ định những thứ đặc biệt trong biểu thức chính quy, vì vậy để chỉ định “dấu gạch ngang ký tự và không phải dấu gạch ngang biểu thức chính quy”, chúng ta phải “hủy” nó, một thuật ngữ phức tạp để nói “bỏ qua điều bạn sẽ làm thông thường trong tình huống này”. Hủy được thực hiện bằng cách đặt một dấu gạch chéo () trước.
Dấu cộng bên ngoài dấu ngoặc đơn có nghĩa là “được phép một ký tự phù hợp với điều này, một hoặc nhiều lần”. Vì vậy, tênngười dùng của bạn có thể có bất kỳ số ký tự nào, miễn là có ít nhất một ký tự.
Tiếp theo, chúng tôi làm điều này một lần nữa cho tên miền: một hoặc nhiều ký tự chữ thường, chữ in hoa, số, gạch dưới, gạch ngang và dấu chấm – bởi vì một số địa chỉ email là “@mail.tênmiền.tiền tố”.
Phần cuối cùng, tiền tố, hạn chế hơn: ([a-zA-Z0-9-]{2,15})
Chúng ta chỉ có thể có các ký tự đơn giản, và chúng ta chỉ có thể có từ 2 đến 15 ký tự (để bao gồm tất cả các tên miền mới thời trang như .coffee và .gripe và, dường như là dài nhất cho đến nay, .cancerresearch). Vì vậy, thay vì + có nghĩa “bất kỳ độ dài”, chúng ta đặt một độ dài tối thiểu và tối đa với {2,15}. (Bạn có thể đặt một cái như “chính xác năm” với chỉ {5}.)
Tóm lại, khi chúng ta muốn một ký tự đơn (như @), chúng tôi viết đơn giản nó. Khi chúng tôi muốn một ký tự phù hợp với một hoặc nhiều loại ký tự, chúng tôi nhóm các ký tự được chấp nhận cùng nhau trong dấu ngoặc vuông. Khi chúng tôi muốn nhân bản nó một số lần, chúng tôi thêm ngoặc nhọn chỉ ra số lần tối thiểu và tối đa các ký tự phù hợp với mô tả, hoặc sử dụng các chỉ số để nói “một hoặc nhiều” hoặc “một hoặc không” chứ không phải là mô tả. Khi chúng tôi thực hiện một nhân bản như vậy, chúng tôi đặt nó trong dấu ngoặc đơn. Một số ký tự yêu cầu “hủy” bằng một dấu gạch chéo.
Đây là, bạn đã học một kỹ năng mạnh mẽ mới ngày hôm nay! Tất cả chỉ để lấy các địa chỉ email. Ouf.
Cách sử dụng biểu thức chính quy để nhập địa chỉ email từ một trang web vào Google Sheets
Hãy lấy những địa chỉ Zapier này bằng sức mạnh của regex của chúng tôi. Chúng ta nhập cùng các thẻ , nhưng thay vì tìm một lớp khớp với “email”, chúng ta tìm nội dung khớp với biểu thức chính quy. Một lần nữa, chúng ta làm hai bước: chúng ta rút all thông tin từ trang Zapier vào cột đầu tiên, sau đó lọc nó cho các địa chỉ 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}")
Và đây là bảng cuối cùng của chúng tôi, hỗ trợ bởi sức mạnh của Regex:
Bạn có thể kết hợp hai chức năng này không? Hãy nhớ, ImportXML sẽ tự động hoàn chỉnh cột và hàng cho mình, dựa trên những gì nó tìm thấy (gọi là một công thức ma trận), và truy vấn regex phải được hoàn thành cho mỗi ô bạn muốn một kết quả (nghĩa là không phải là một công thức ma trận). Để ghép chúng lại, bạn chỉ cần yêu cầu Regexextract là công thức ma trận chỉ một lần (và thêm một hàm IFERROR cho sự tinh khiết, để để lại các ô trống nơi không tìm thấy địa chỉ email) :
=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à danh sách cuối cùng của địa chỉ email từ trang Về chúng tôi của Zapier, được cung cấp bởi sức mạnh của Regex:
Trở thành một chuyên gia về Google Sheets với Zapier
Để đi xa hơn, chúng tôi đã viết các bài viết khác về web scraping trong eBook miễn phí của chúng tôi về CRM của Google Sheets. Bạn cũng có thể đọc về các chức năng bạn gặp khi ImportXML:
- ImportHTML – một chức năng yếu hơn thu thập một bảng hoặc danh sách đầy đủ từ một trang web cụ thể mà không có các điều khiển khác
- ImportRange – để lấy dữ liệu từ các bảng tính khác trong bảng tính
- ImportData – để nhập dữ liệu từ một tệp CSV hoặc TSV liên kết
- ImportFeed – hoạt động giống như ImportXML, nhưng để nhập RSS hoặc Atom feed, điều đó có thể rất hữu ích nếu bạn gặp vấn đề khi nhập XML từ một trang web cụ thể (hem Twitter).
Ngoài ra, bạn sẽ học các kiến thức cơ bản về bảng tính nếu bạn cần xem lại, cũng như một số gợi ý về việc xây dựng một ứng dụng đầy đủ trong bảng tính của bạn, sử dụng Google Apps Script để tự động hóa bảng tính của bạn, và hướng dẫn sử dụng ứng dụng đồng hành của Google Sheets, Google Forms.
Hoặc, bạn có thể sử dụng các tích hợp Google Sheets của Zapier để dễ dàng nhập dữ liệu vào bảng tính của mình. Bạn có thể lưu các tweet vào bảng tính, sao lưu danh sách liên hệ MailChimp của bạn, hoặc lưu dữ liệu từ các biểu mẫu và sự kiện của bạn vào bảng tính.
Zapier cũng có thể tận dụng dữ liệu của bạn. Giả sử bạn sử dụng importXML để trích xuất danh sách địa chỉ email vào một bảng tính. Sau đó, Zapier có thể sao chép chúng từ bảng tính của bạn và gửi email cho họ hoặc thêm họ vào danh sách phát. Nó có thể thêm danh sách ngày vào lịch Google của bạn để giúp tạo danh sách nghỉ lễ hoặc sự kiện. Hoặc nó có thể thêm mỗi mục nhập mới như một nhiệm vụ mới trong ứng dụng quản lý dự án của bạn – và còn nhiều hơn thế nữa.
Related posts
Comment référencer une autre feuille dans une formule Google Sheets?
Tháng ba 29, 2024
