Mẹo hay Google trang tính: Tạo bảng tính cập nhật tự động

Mệt mỏi với việc tìm kiếm, sao chép và dán dữ liệu vào bảng tính? Chỉ với một vài dòng mã, bạn có thể thiết lập bảng tính tự cập nhật trong Google Trang tính để tìm nạp và lưu trữ dữ liệu cho bạn.

Việc theo dõi dữ liệu cơ bản trong bảng tính khá đơn giản – việc ghi nhớ cập nhật dữ liệu của bạn hàng ngày, hàng tuần hoặc hàng tháng có thể là một vấn đề. Nhưng tại sao phải cập nhật thủ công khi bạn có thể tận dụng một số công cụ tự động hóa đơn giản?

Dưới đây là hướng dẫn từng bước để tạo Google Trang tính tự động cập nhật, vừa loại bỏ dữ liệu từ web vừa tự động cập nhật bảng tính. Sẽ sử dụng “nhiệt độ địa phương hàng ngày vào giờ ăn trưa” trong ví dụ này, nhưng mã cũng có thể dễ dàng được điều chỉnh để tìm nạp và lưu trữ các dữ liệu khác.

1. Tạo bảng tính Google Trang tính

Truy cập Google Drive hoặc Google Trang tính và tạo một bảng tính trống mới. (Nếu chưa có tài khoản Google, bạn có thể đăng ký tại đó.)
 

Đặt tiêu đề cho trang tính bất cứ điều gì bạn muốn và bạn đã sẵn sàng để bắt đầu. (Mặc định sẽ là “Bảng tính không có tiêu đề”, nhưng bạn có thể nhấp vào đó để thay đổi nó thành thứ gì đó giúp nhận dạng bảng tính tốt hơn trong danh sách tài liệu của bạn.)

2. Tìm “XPath” cho phần trang web bạn muốn cạo

Google Trang tính cần một bộ chọn “XPath” để chỉ lấy một phần dữ liệu từ một trang web. May mắn thay, có một công cụ trỏ và nhấp dễ sử dụng giúp bạn làm điều đó: SelectorGadget. (Có một video dài chưa đầy 2 phút trên trang web minh họa cách thức hoạt động của nó.)

Cài đặt tiện ích mở rộng trình duyệt SelectorGadget Chrome, kích hoạt tiện ích này bằng cách nhấp vào biểu tượng công cụ trình duyệt, sau đó nhấp vào phần của trang bạn muốn cạo. Phần đó sẽ chuyển sang màu xanh lục. Bí quyết là đảm bảo rằng bạn chỉ chọn cái đó. Các khu vực khác của trang cũng có thể có màu xanh lục hoặc vàng. Nếu có, hãy nhấp vào những cái bạn muốn xóa khỏi lựa chọn của mình.

Trong hình ảnh bên dưới, đã truy cập trang Dịch vụ thời tiết quốc gia cho Needham, MA, vị trí của trụ sở công ty Foundry. (Foundry là công ty mẹ của Computerworld.)

Trang này rất dễ: Đã nhấp vào nhiệt độ và đó là mục duy nhất được chọn. Nếu những cái khác hiển thị màu xanh lá cây hoặc màu vàng, sẽ nhấp vào chúng để tắt chúng nhằm đảm bảo rằng XPath chỉ chọn những gì cần.
 

Ở dưới cùng bên phải, một bộ chọn xuất hiện; nhưng đó .myforecast-current-lrg là bộ chọn css. Cần XPath, đây là một tùy chọn ở dưới cùng bên phải. Nhấp vào XPath và phiên bản XPath sẽ hiển thị giống như thế này:
 

XPath thường phức tạp hơn CSS, nhưng bạn không cần lo lắng về ý nghĩa của tất cả các biểu thức. Một điểm quan trọng là bộ chọn XPath cuối cùng sẽ được bao quanh bởi dấu ngoặc kép trong công thức Google Trang tính của bạn. Thật không may, bản thân bộ chọn cũng có dấu ngoặc kép. Điều đó sẽ gây ra vấn đề, vì vậy nếu XPath của bạn bao gồm dấu ngoặc kép, hãy thay đổi tất cả chúng thành dấu ngoặc đơn theo cách thủ công.

Bộ chọn XPath sẽ sử dụng trong Google Trang tính của mình hiện là

//*[contains(concat( ‘ ‘, @class, ‘ ‘ ), concat( ‘ ‘, ‘myforecast-current-lrg’, ‘ ‘ ))]  

3. Thêm công thức của bạn

Các công thức bảng tính có thể làm được nhiều việc hơn ngoài các phép tính toán học; họ cũng có thể trích xuất dữ liệu từ các trang web. Đối với Google Sheet, chức năng đọc một phần của trang web là:

=ImportXML(“URL”, “XPath selector”)

Đối với trang National Weather Service Needham, MA và bộ chọn nhiệt độ, đó là

=IMPORTXML(“https://forecast.weather.gov/MapClick.php?lat=42.2803&lon=-71.2345”, “//*[contains(concat( ‘ ‘, @class, ‘ ‘ ), concat( ‘ ‘, ‘myforecast-current-lrg’, ‘ ‘ ))]”)

Trong cột đầu tiên của bảng tính, sẽ liệt kê vị trí đang theo dõi. Điều này sẽ giúp dễ dàng thêm các địa điểm bổ sung vào trang tính trong tương lai. Sẽ đặt công thức trong cột thứ hai.
 

Và muốn ngày và giờ trong cột C. Sẽ giải quyết vấn đề đó trong giây lát.

4. Viết hàm lấy và lưu trữ dữ liệu

Cách thiết lập bảng tính hiện tại, nhiệt độ sẽ không được lưu; nó sẽ thay đổi mỗi khi bạn mở trang tính.

Để giữ dữ liệu lịch sử như thiết kế hiện tại của trang tính, cần sao chép và dán các giá trị theo cách thủ công vào một ô khác mỗi khi mở nó. Đó không phải là rất khả năng mở rộng! Thay vào đó, hãy tạo một hàm mới để 1) Tìm hàng trống đầu tiên trong trang tính và 2) sao chép giá trị từ ô B2 vào một ô trống khác để lưu trữ.

Để lưu trữ dữ liệu, chúng ta cần tạo một hàm bảng tính. Đi tới Tiện ích mở rộng > Tập lệnh ứng dụng để tạo chức năng cho bảng tính.
 

Bạn sẽ thấy một chức năng mặc định bật lên có tên là myFunction.
 

Thay đổi tên hàm đó thành storeTemperature() (hoặc bất kỳ tên nào bạn muốn gọi) và sử dụng mã sau:
 

Dòng mã đầu tiên tạo một biến cho bất kỳ bảng tính nào đang hoạt động, theo sau là một biến chứa số hàng trống đầu tiên của trang tính đó.

Để đọc hoặc ghi dữ liệu trong một ô của Trang tính, ô đó cần phải là một đối tượng phạm vi . May mắn thay, thật dễ dàng để biến vị trí ô của bảng tính thành một đối tượng phạm vi ô với yourSheetObject.getRange(). Trong ví dụ này, đã gọi đối tượng trang tính của mình sheet(bạn có thể gọi nó là gì cũng được), vì vậy mã là sheet.getRange(). getRange có một số định dạng để xác định vị trí ô, bao gồm getRange(3,5)cho hàng 3, cột 5 hoặc getRange(“B ” + 2)cho cú pháp ô bảng tính thông thường như B2.

Với ý nghĩ đó, có thể tạo một biến giữ phạm vi ô cho ô B2, là hàng 2 và cột 2, nơi nhiệt độ hiện tại của mình, sử dụng sheet.getRange(2, 2. Sau đó đọc giá trị đó get.Value() như bạn có thể thấy trong đoạn mã trên.

Tiếp theo trong khối mã đó, tìm thấy hàng trống đầu tiên trong cột B, nơi muốn thêm nhiệt độ mới và lưu ô đó làm đối tượng phạm vi. Sau đó, vấn đề đơn giản là đặt giá trị của ô đó với nhiệt độ vừa lưu trữ.

Các hàng cuối cùng làm điều gì đó tương tự để lưu trữ ngày và giờ hiện tại cũng như vị trí được mã hóa cứng.

Có nhiều cách để làm cho mã này nhỏ gọn hơn, nhưng đang chia sẻ một phiên bản dài dòng để giúp dễ dàng xem những gì đang diễn ra. Ở dòng cuối cùng, đã làm chặt chẽ hơn một chút bằng cách kết hợp getRange và setValue trong một dòng mã, thay vì tạo một biến riêng chứa phạm vi ô. Sử dụng bất kỳ cú pháp nào bạn thích.
 

Lưu cái này bằng cách nhấp vào biểu tượng đĩa mềm phía trên mã chức năng. Có thể bạn sẽ muốn thay đổi tên dự án mặc định thành tên khác. Đặt tên cho mình là “Nhiệt độ giờ ăn trưa”.

Bạn có thể kiểm tra tập lệnh của mình bằng cách nhấp vào nút Chạy để xem điều gì xảy ra trong bảng tính. Nếu mọi việc suôn sẻ, bạn sẽ có một hàng dữ liệu mới với nhiệt độ hiện tại.

5. Lên lịch để chức năng của bạn tự động chạy

Phần cuối cùng: Lên lịch để chức năng của bạn chạy tự động. Để thực hiện việc này, hãy nhấp vào biểu tượng đồng hồ ở bên trái để mở bảng điều khiển tập lệnh hiển thị trình kích hoạt dự án hiện tại của bạn. (Sẽ chưa có.) Trước tiên, bạn có thể được yêu cầu ủy quyền điều này trong tài khoản Google của mình.
 

Nhấp vào tạo trình kích hoạt mới và một menu bật lên.
 

Nếu bạn muốn bảng tính của mình cập nhật theo một lịch trình cụ thể, hãy thay đổi nguồn sự kiện từ “Từ bảng tính” thành “Theo thời gian” rồi chọn xem bạn muốn bảng tính chạy hàng giờ, hàng ngày, hàng tuần, hàng tháng hay các tùy chọn khác.

Sau đó, thì đấy! Một bảng tính tự cập nhật thu thập và lưu trữ dữ liệu tự động.