Hàm BS_CLOUD lấy dữ liệu từ Google Sheets và Excel Online

/ Kiến thức Add-in A-Tools / Cloud / Hướng dẫn hàm BS_CLOUD lấy dữ liệu từ Google Sheets và Excel Online về Excel

Hướng dẫn hàm BS_CLOUD lấy dữ liệu từ Google Sheets và Excel Online về Excel

Dữ liệu từ Google Sheets và Excel Online (dịch vụ lưu trữ đám mây - cloud) được liên kết về Excel rất dễ dàng với hàm BS_CLOUD của Add-in A-Tools. Dữ liệu trên cloud thay đổi thì trên Excel sẽ thay đổi theo khi bạn cập nhật hàm. Dữ liệu lấy về Excel dạng mảng bạn có thể dùng VBA hay các hàm của Excel và Add-in A-Tools để tạo ra báo cáo động trên Excel.

Hàm BS_CLOUD chỉ có từ Add-in A-Tools v10.x

Cấu trúc hàm

BS_CLOUD(file_id, range_address, [cloud_type], [options])

Các tham số
Các tham số trong [ ] có thể bỏ qua.

- file_id: là chuỗi hoặc mảng ID của tập tin hoặc đường dẫn của tập tin lưu trên các Drive (OneDrive, Google Drive). Tức bạn có thể lấy dữ liệu từ một hoặc nhiều tập tin trên cloud.

- range_address: là chuỗi hoặc mảng địa chỉ vùng dữ liệu. Địa chỉ theo cấu trúc "Tên sheet!Địa chỉ vùng". Tức bạn có thể lấy dữ liệu từ nhiều sheet, nhiều vùng.

Ví dụ:

"Sheet1!A1:E100"
hoặc
"Sheet1!A1:E100,Sheet2!A1:E100,Sheet3!A1:E100"
hoặc
{"Sheet1!A1:E100","Sheet2!A1:E100","Sheet3!A1:E100"}

Nếu bỏ qua tham số này thì hàm trả về danh sách các sheet.

Cách lấy mảng giá trị trong NAME và TABLE

SheetName!A1:C7 : Nhận các giá trị vùng A1:C7 trong sheet 'SheetName'

NAME=RangeName hoặc NAME=SheetName!RangeName: Nhận mảng giá trị trong name RangeName. Nếu không chỉ ra SheetName thì NAME thuộc phạm vi quản lý của Workbook.

Trong Google Sheets, RangeName được tạo từ menu Data -> Named Ranges.
Trong Excel Online RangeName được tạo từ menu Formulas -> Name Manager.

TABLE=Table1 hoặc TABLE=SheetName!Table1: Nhận các giá trị trong bảng Table1. Mẫu này chỉ dùng cho Excel Online. Nếu không chỉ ra SheetName thì Table1 thuộc phạm vi quản lý của Workbook.

Để tạo table1, trong Excel Online vào menu Insert - > Table.

- cloud_type: là số chỉ ra kiểu dịch vụ lưu trữ. Nếu là -1 (ngầm định) hàm tự tìm kiểu cloud; 0 là Google Drive (nguồn Google Sheets); 1 - OneDive (nguồn Excel Online)

- options: là chuỗi, có thể bỏ qua, là khai báo các thuộc tính cho hàm.

Video hướng dẫn hàm BS_CLOUD "lấy và gộp dữ liệu từ Google Sheets và Excel Onlie về Excel"

(Gộp dữ liệu từ Google Sheets vào Excel; Gộp dữ liệu từ Excel Online và Excel)

Nguyên tắc sử dụng và an toàn thông tin

Đây là hàm lấy dữ liệu trên dịch vụ đám mây theo tài khoản của người dùng vì vậy nó tuân thủ nguyên tắc truy cập và quyền hạn do nền tảng lưu trữ đám mây quy định để đảm bảo an toàn dữ liệu cá nhân của người dùng. Vì vậy trước khi lấy dữ liệu từ cloud về người dùng cần thực hiện bước "Đăng nhập" để xác thực và ủy quyền cho Add-in A-Tools được lấy dữ liệu hay không. Nếu bạn không cấp phép dịch vụ nào thì dịch vụ đó sẽ không thực hiện được. Quy trình này hoàn toàn do Google và Microsoft, Dropbox (những dịch vụ cloud mà bạn sử dụng) thực hiện theo giao thức OAuth 2.0 chứ không phải A-Tools tự làm nên bạn cần hiểu A-Tools không thể biết thông tin đăng nhập cũng như không thể tự ý truy cập vào dữ liệu của bạn.

Các điều khoản thỏa thuận của Add-in A-Tools với các dịch vụ lưu trữ đám mây

- Google: Privacy Policy, Terms of Service
- Microsoft: Privacy Policy, Terms of Service

Đăng nhập và cấp phép

Trước khi sử dụng hàm BS_CLOUD cũng như các tính năng truy cập vào dữ liệu vào dịch vụ đám mây bạn phải thực hiện việc đăng nhập để xác thực và cấp phép. Các bước thực hiện:

- Bước 1: Vào menu A-Tools -> Chọn dịch vụ trong nhóm "Cloud Store service" (Dịch vụ lưu trữ đám mây).

+ "Google Drive with Google Sheets" để làm việc với Google Sheets;
+ "Open Excel file from OneDrive" để làm việc với Excel Online;
+ "Open Excel file from DropBox" để làm việc với DropBox.

Hàm BS_CLOUD không lấy dữ liệu trên DropBox)

Hàm BS_CLOUD lấy dữ liệu từ Google Sheets

- Đăng nhập

Sau khi chọn menu "Google Drive with Google Sheets" sẽ xuất hiện màn hình "Đăng nhập" nếu bạn chưa đăng nhập.

Bạn hãy nhập tài khoản Google Drive mà ở đó bạn muốn lấy dữ liệu. Như đã nói ở trên. A-Tools không biết thông tin về tài khoản của bạn. Đây là nguyên tắc bảo mật của Google theo chính sách quyền riêng tư. Sau khi nhập user và password chọn "Next". Xuất hiện bước tiếp theo.

Xác thực và cấp phép - Cấp quyền

Ở màn hình này bạn hãy tick chọn hoặc chọn hết (tick chọn "Select all") các dịch vụ mà Add-in A-Tools xin cấp. Nếu bạn không tick chọn dịch vụ nào thì A-Tools không thể thực hiện dịch vụ đó.

Cuối cùng chọn nút "Continue" - Tiếp tục. Bạn sẽ nhận được yêu cầu xác thực trên điện thoại di động nếu bạn có cài đặt "Bảo mật 2 lớp" trong tài khoản của bạn.

Sau khi cấp phép và xác thực thành công bạn có thể thực hiện truy xuất dữ liệu của bạn trên dịch vụ đám mây mà bạn chọn, trong đó có việc bạn chạy hàm BS_CLOUD.

(*) Lưu ý: Công việc đăng nhập để xác thực và cấp phép bạn chỉ phải làm một lần sau này bạn không phải thực hiện lại trừ khi bạn thực hiện "Đăng xuất" hoặc hết thời hạn hoạt động của token.

Ví dụ

Cần lấy vùng dữ liệu trong Sheet1 của tập tin "Bảng tính Google Sheets.xlsx" trên Google Sheets (lưu trên Google Drive).

Bạn cần biết giá trị ID của nó. Có hai cách để biết:
+ Cách 1: Khi bạn mở file này từ Googel Drive bạn sẽ thấy ID của nó sau nhánh "/d/" của URL. Ví dụ

https://docs.google.com/spreadsheets/d/1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA/edit?gid=988554170#gid=988554170

ID của nó chính là "1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA"

Bạn hoàn toàn dùng cả đường dẫn URL chưa file_id để làm tham số cho hàm vẫn chạy.

+ Cách 2: Copy ID từ màn hình "Open file in cloud service"

Đến đây bạn đã có đủ điều kiện và thông tin để dùng hàm BS_CLOUD

Giả sử bạn điền các giá trị tham số trên bảng tính Excel dưới đây

Địa chỉ ô B3 chứa file_id: 1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA
Địa chỉ ô B4 chứa địachỉ vùng dữ liệu: Sheet1!A7:E10 (Tên sheet!Địa_chỉ_vùng)

+ Công thức lấy dữ liệu từ Google Sheets về Excel là:

=BS_CLOUD(B3,B4,0)

(0 ở tham số thứ 3 nghĩa là kiểu cloud là Google Drive. Nếu là 1- là OneDrive)

+ Kết quả:

Hàm BS_CLOUD lấy dữ liệu từ Excel Online - OneDrive

Quy trình và cách thức giống hết với Google Sheets.

Công thức là:

=BS_CLOUD(B2,B3,1)

Các hình ảnh thể hiện quy trình dưới đây:

Nếu hàm trả về lỗi không có quyền truy cập, như là "The caller does not have permission". Có nghĩa tài khoản cloud đang hoạt động không có quyền truy cập file_id. Trường hợp này bạn phải đăng nhập tài khoản khác có quyền truy cập. Bấm vào "Google Drive" hoặc "OneDrive" phía dưới màn hình gợi ý hàm để đăng nhập hoặc vào menu "A-Tools", mở màn hình quản trị dữ liệu trên cloud, bấm vào biểu tượng user đăng nhập phía phải, chọn "Switch Account" - Chuyển đổi tài khoản.

Từ phiên bản Add-in A-Tools v10 cập nhật ngày 11-12-2024 thì hàm BS_CLOUD có thêm tính năng cho phép lấy dữ liệu trong Table và Name. Cụ thể là:

+ Lấy dữ liệu trong Table. Chỉ áp dụng với Excel Online.

Trong hàm BS_CLOUD, tham số ADDRESS nhập theo cú pháp:

TABLE=Table1 hoặc TABLE=SheetName!Table1 : có nghĩa là hàm sẽ nhận mảng giá trị table có tên "Table1".

Nếu không khai báo tên sheet thì table thuộc quản lý của Workbook.

Ví dụ:
=BS_CLOUD("FileID", "TABLE=Table", 1)

+ Lấy dữ liệu trong NAME. Cáp dụng cho cả Google Sheets và Excel Online

Trong hàm BS_CLOUD, tham số ADDRESS nhập theo cú pháp:

NAME=RangeName hoặc NAME=SheetName!RangeName : có nghĩa là hàm sẽ nhận mảng giá trị trong NAME có tên "RangeName".

Nếu không khai báo tên sheet thì NAME thuộc quản lý của Workbook.
Ví dụ lấy mảng giá trị trong NAME có tên là "DATA.

=BS_CLOUD("FileID", "NAME=DATA")

Nếu NAME "DATA" có phạm vi Worksheet và nằm trong Sheet2 thì công thức là:

=BS_CLOUD("FileID", "NAME=Sheet2!DATA")

Download Add-in A-Tools để thực hành
Tác giả Nguyễn Duy Tuân