google sheet python

Đọc và xuất dữ liệu Google Sheets với Python & Jupyter Notebook

Xin chào các bạn!

Google Sheets từ lâu đã là 1 công cụ bảng tính cực kỳ hữu ích, quen thuộc với nhiều người trên nền tảng web nhất là trong làm việc nhóm. Thật lòng mà nói, tôi sử dụng Google Sheets trong công việc và cuộc sống hàng ngày còn nhiều hơn bộ công cụ Microsoft Excel trên máy tính nhờ trải nghiệm xuyên suốt trên nhiều thiết bị, những hàm đặc biệt và khả năng mở rộng tính năng thông qua Google Apps Script và các Add on.

Khi sử dụng Python để phân tích dữ liệu, tôi chọn tải dữ liệu trực tiếp từ Google Sheets vào project cho tiện thay vì phải xuất file định dạng csv, xlsx rồi mới tải. Sau khi xử lý xong xuôi, tôi cũng thường xuyên lựa chọn xuất dữ liệu ra Google Sheets để lưu trữ và chia sẻ tới đội nhóm trong công ty và đối tác. Ngoài ra với các ứng dụng web scraping, trích xuất dữ liệu từ các báo thì Google Sheets cũng được chọn để lưu trữ các dữ liệu đã qua xử lý một cách tiện lợi, đính kèm link khi gửi thông tin chi tiết cho người nhận tin.

Thông qua bài hướng dẫn ngắn này, hy vọng các bạn có thể đọc và xuất dữ liệu Google Sheets với Python dễ dàng để ứng dụng vào công việc của mình nhé.

Kích hoạt tài khoản Google Cloud

Để có thể tương tác với các dịch vụ của Google thông qua API và các thư viện Python, bạn cần kích hoạt tài khoản Google Cloud (đối với lần đầu sử dụng) và tạo khóa bảo mật để trao đổi thông tin với API.

Kích hoạt tài khoản Google Cloud
Kích hoạt tài khoản Google Cloud

Tạo Project trên Google Cloud Console

Tạo project (dự án) đầu tiên của bạn trên Google Cloud bằng cách nhập tên mô tả dự án và chuyển đến bước tiếp theo.

create new project gg cloud

Tạo service account và credential

Tạo tài khoản service account và khóa bảo mật (credential) đầu tiên của bạn. Tìm mục APIs & Services tại thanh menu ở phía bên trái màn hình, sau đó chọn Credentials.

credential api

Đến đây, các bạn sẽ cần tạo một Service account - đây là tài khoản dành cho mục đích tương tác với Google Cloud thông qua bot hoặc các chương trình được lập trình sẵn.

create service account

Nhập các thông tin mô tả Service account này để các bạn dễ nhận diện và quản lý về sau khi bạn có nhiều dự án và service account khác nhau.

fill form service account

Tiếp theo, các bạn sẽ cần tạo 1 khóa bảo mật cho Service account được tạo ra. Chọn mục Manage service account ngay bên cạnh tên service account bạn mới tạo ra như hình dưới đây. Hãy copy địa chỉ Email service account sheet-api-connect@mrthinh.iam.gserviceaccount.com vào Notepad để sử dụng trong các bước tiếp theo nhé.

image 20210527211400422

Tiếp theo bạn chọn Add Key và Create new key như hình mô tả.

image 20210527211621333

Ở màn hình tiếp theo các bạn chọn định dạng khóa bảo mật là JSON sau đó click chọn Create để tải về khóa bảo mật là 1 file có đuôi .json.

image 20210527211640425

Bật dịch vụ Google Sheets API và Google Drive API

Để làm việc với Google Sheets bạn cần bật 2 dịch vụ API là Google Drive API và Google Sheets API. Hãy nhập sheet apidrive api tương ứng vào thanh tìm kiếm như hình bên dưới để tìm 2 dịch vụ trên. Chọn kết quả tìm kiếm tương ứng với Google Sheets API và Google Drive API sau đó click chọn Enable ở màn hình kế tiếp để bật dịch vụ.

image 20210527210834781
Inkedimage 20210527210726873 LI

Chia sẻ quyền chỉnh sửa file Google Sheets với service account

Như vậy là bạn đã tạo xong Service account và khóa bảo mật để tương tác với Google Sheets API rồi đấy. Để kết nối với Sheet API trong Python, chúng ta sử dụng thư viện gspread.

Để có thể đọc/ghi dữ liệu Google Sheets, bạn cần chia sẻ quyền Editor (Chỉnh sửa) của file Google Sheets cần dùng cho tài khoản service account vừa được tạo ra ở trên

grant permission service account

Nếu trên máy tính của bạn chưa cài đặt thư viện gspread thì hãy thực thi câu lệnh pip install gspread trong Terminal/Command Prompt hoặc 1 cell của Jupyter Notebook đang sử dụng.

Tạo 1 cell trong Jupyter Notebook để nhập các thư viện cần sử dụng vào project và khai báo xác thực oauth2client.

 
# importing the required libraries
import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
 
ss_cred_path = r'D:datascience_lab\config\gg_cred\mrthinh_support-3898307a9270.json' # Your path to the json credential file
from oauth2client.service_account import ServiceAccountCredentials # import oath2client
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] # define the scope
creds = ServiceAccountCredentials.from_json_keyfile_name(ss_cred_path, scope) # add credentials to the account
gc = gspread.authorize(creds) # authorize the clientsheet 
 

Đến đây bạn chỉ còn làm 1 bước cuối cùng nữa là copy Sheet ID và Sheet Name của sheet bạn muốn đọc dữ liệu. Trong hình minh họa dưới đây, bạn có thể tìm thấy Sheet ID trong đường link trên thanh địa chỉ của trình duyệt là 1tpyXydttojObdSW9MusCj-7dG10BkrrDyUDeVQQSnN4 còn Sheet Name tương ứng là export_social_network_ads_sample

image 20210527214615832

Đọc dữ liệu từ Google Sheets

Giờ đây bạn có thể bắt đầu đọc toàn bộ dữ liệu từ Sheet trên vào dataframe trong Python.

# Get existing trend data from Sheet
spreadsheet_id = "1tpyXydttojObdSW9MusCj-7dG10BkrrDyUDeVQQSnN4"
wks = gc.open_by_key(spreadsheet_id)
worksheet = wks.worksheet('export_social_network_ads_sample')
df = pd.DataFrame(worksheet.get_all_records())

Thành quả đạt được trong Jupyter Notebook như sau:

 

image 20210527225116511

Xuất dữ liệu qua Google Sheets

Để xuất dữ liệu qua Google Sheets ở 1 sheet mới, trước tiên bạn cần tạo sheet đó với số dòng và cột tương ứng. Ví dụ tôi muốn tạo 1 sheet mới có tên là test_sheet để lưu trữ dữ liệu muốn xuất ra, sheet này có số dòng là 1000 và số cột là 26.

new_sheet = wks.add_worksheet('test_sheet', rows=1000, cols=26)
 
Bạn cũng có thể tạo sheet mới sử dụng chính thông số dòng, cột từ dataframe:

new_sheet = wks.add_worksheet(title=”test_sheet_2", rows=len(df.index), cols=len(df.columns))
 
Bây giờ chúng ta có thể sử dụng 1 câu lệnh đơn giản sau để xuất dữ liệu ra sheet mới được tạo.
 
# upload dataframe to google sheets
new_sheet.update([df.columns.values.tolist()] + df.values.tolist())
 
trong đó df là dataframe tôi muốn xuất dữ liệu ra Google Sheets. Sau các bước thực hiện như trên, giờ đây Jupyter Notebook của bạn sẽ có dạng hoàn chỉnh như này:
Screenshot 2021 05 27 233435
Sau 1 hồi làm theo hướng dẫn hẳn các bạn cảm thấy khá rắc rối với nhiều bước phải không nào. Thực tế đây là lần đầu tiên thực hiện nên bạn mất nhiều thời gian để hoàn thành các bước thiết lập. Tin tôi đi, những lần tới việc đọc và xuất dữ liệu qua Google Sheets của bạn sẽ cực kỳ đơn giản. Chỉ cần lấy Sheet ID, Sheet Name và phân quyền chỉnh sửa file cho service account là bạn đã có thể tương tác với Gooogle Sheet rồi đó.
Để hiểu thêm về các tùy chọn và toàn bộ tính năng thú vị của thư viện gspread, bạn có thể tham khảo tài liệu chính thức Tại đây
 
Chúc các bạn thành công!

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

Hướng dẫn sử dụng Google Colab - Google Colab Tutorial 101
Hướng dẫn sử dụng Google Colab đầy đủ – Python Tutorial
July 29, 2021
Tôi đã đến với Python như thế nào? Câu chuyện về trải nghiệm các “Tool” phân tích dữ liệu
March 28, 2021
automated facebook ads report to google sheets 2
Cập nhật báo cáo Facebook Ads tự động với Google Sheets và Apps Script
October 16, 2021

Leave a Reply

Your email address will not be published. Required fields are marked *