Google Sheets là ứng dụng được sử dụng thường ngày của các nhà phân tích dữ liệu, đặc biệt là trong những công ty có sử dụng hệ sinh thái Google Workspace. Bản thân Hiếu cũng sử dụng Google Sheets để làm báo cáo, dashboard và việc cập nhật dữ liệu cho Google Sheets diễn ra hằng ngày nhưng được tự động hóa bằng Python. Dưới đây là một ít kinh nghiệm của Hiếu trong việc sử dụng gspread để làm việc với Google Sheets.
Những việc cần chuẩn bị
Để sử dụng gspread hay Google Sheets API nói chung, bạn cần tạo một Service Account và lấy file JSON của nó. Việc này giúp phần ủy quyền tài khoản trở nên dễ dàng trong Python.
Sau khi có Service Account rồi, bạn hãy vào cài đặt chia sẻ Google Sheets và thêm email của Service Account với quyền chỉnh sửa. Email chính là trường client_email trong file JSON mà bạn đã tải về.
Hãy đảm bảo là bạn đã cài đặt Python trên máy, cài đặt gspread bằng lệnh sau:
pip install gspreadSử dụng gspread để lấy liệu từ Google Sheets
Lấy toàn bộ dữ liệu trong worksheet
Nếu dữ liệu trong worksheet thể hiện chuẩn dạng bảng (dòng đầu tiên là header, các dòng khác là giá trị) thì hãy dùng hàm get_all_records() để lấy dữ liệu từ Google Sheets.
import pandas as pd
import gspread
client = gspread.service_account(filename='service_account.json')
sheet = client.open_by_key(key='YourSheetsKey')
worksheet = sheet.get_worksheet_by_id(id=0) # Replace 0 with your worksheet ID
data = worksheet.get_all_records()
df = pd.DataFrame(data)Nếu dữ liệu trong worksheet không có cấu trúc như một bảng, bạn có thể lấy dữ liệu từ Google Sheets ở dạng tất cả các dòng (list of lists) bằng hàm get_all_values(). Tuy nhiên, bạn cần phải tự bóc tách, làm sạch dữ liệu của bạn.
data = worksheet.get_all_values()Mặc dù gspread có hỗ trợ các hàm khác để chọn một sheet và hay một worksheet bằng tên của chúng. Nhưng với kinh nghiệm của Hiếu, dùng key và id vẫn là an toàn nhất bởi vì nó luôn cố định, còn tên thì có thể bị đổi hoặc có nhiều sheet trùng tên sẽ gây lỗi.
Lấy dữ liệu của một range trong worksheet
Khi sử dụng hàm get_values() để lấy dữ liệu của một range trong worksheet, bạn sẽ nhận được giá trị ở dạng list of lists nhé.
data = worksheet.get_values(range_name='A1:B2')Lấy công thức thay vì giá trị
Có thể bạn chưa biết. Ngoài việc lấy dữ liệu ở dạng giá trị, gspread cũng hỗ trợ bạn lấy dữ liệu ở dạng công thức. Tính năng này rất hữu ích cho những hoàn cảnh đặc biệt, ví dụ như cập nhật toàn bộ công thức bằng cách replace string.
data = worksheet.get_all_records(value_render_option='FORMULA')Bạn có thể dùng tham số value_render_option='FORMULA' cho các hàm lấy dữ liệu khác.
Sử dụng gspread để cập nhật dữ liệu vào Google Sheets
Hầu hết trong các hàm cập nhật dữ liệu vào Google Sheets, mình thường thêm tham số value_input_option='USER_ENTERED' vì nó giúp worksheet nhận đúng định dạng như bạn điền dữ liệu bằng tay.
Thay thế toàn bộ dữ liệu cũ
Dưới đây là code cập nhật dữ liệu vào Google Sheets bằng gspread mà mình thường sử dụng.
- Kết nối worksheet.
- Xoá toàn bộ giá trị từ dòng 2 trở lên.
- Cập nhật toàn bộ dữ liệu từ data frame vào worksheet, bao gồm cả header.
import pandas as pd
import gspread
df = pd.DataFrame({'name': ['iPhone', 'iPad', 'MacBook'], 'profit': [7200, 8600, 9500]})
client = gspread.service_account(filename='service_account.json')
sheet = client.open_by_key(key='YourSheetsKey')
worksheet = sheet.get_worksheet_by_id(id=0) # Replace 0 with your worksheet ID
# Delete old data but excluded header
worksheet.delete_rows(start_index=2, end_index=worksheet.row_count)
# Update new data
worksheet.update(values=[df.columns.tolist()] + df.values.tolist(), value_input_option='USER_ENTERED')Ngoài ra bạn còn có thể khai báo thêm tham số range trong hàm update() để chọn vùng cập nhật dữ liệu.
Thêm dữ liệu vào bên dưới worksheet
Thuật ngữ quen thuộc là “append”. Khi append dữ liệu thì chúng ta không cần lấy header từ data frame nữa.
worksheet.append_rows(values=df.values.tolist(), value_input_option='USER_ENTERED')Cập nhật một cell cụ thể
Để cập nhật một cell bạn có thể dùng hàm update_acell() hoặc update_cell() nó chỉ khác cách điền tham số cho cell mà bạn cần cập nhật.
worksheet.update_acell(label='A1', value='Hello World!')
# The same as
worksheet.update_cell(row=1, col=1, value='Hello World!')Cập nhật nhiều range cùng lúc
Hàm batch_update() là lựa chọn lý tưởng khi bạn cần cập nhật nhiều cell hoặc range cùng một lúc, thay vì phải lặp lại vô số hàm update khác.
worksheet.batch_update([
{
'range': 'A1',
'values': [['Hello World!']]
},
{
'range': 'B2:C3',
'values': [['Name', 'Birthday'], ['Hieu Tran', 1996]]
},
{
'range': 'D1:D3',
'values': [['Guide'], ['From'], ['Lucid Gen']]
}
])Nâng cao: Để cập nhật nhiều range cùng một lúc mà vẫn đảm bảo tính tự động hóa, bạn cần kết hợp với các thư viện khác như pandas và openpyxl. Giả sử bạn chỉ muốn cập nhật các cell đã thay đổi trong một data frame. Cách tiếp cận tốt nhất là:
- Tạo
row_numberbằng index của data frame. - Lọc ra các dòng có thay sự thay đổi.
- Tạo danh sách các cột cần cập nhật.
- Dùng for loop để tạo các batch một cách tự động. Trong đó, sử dụng
get_column_letter()vàrow_numberđể tạorange. - Cuối cùng, dùng hàm
batch_update()để cập nhật toàn bộ cell đã thay đổi.
from openpyxl.utils import get_column_letter
# Assume df is data from worksheet
df['row_number'] = df.index + 2 # Due to header and Python index start from 0
# You had changed some values in df. Filter rows you want to update to worksheet
df_filter = df[df['status']=='changed'] # Replace with your condition
# Columns you want to update to worksheet
update_cols = ['profit', 'revenue']
batches = []
for col in update_cols:
col_letter = get_column_letter(df_filter.columns.tolist().index(col) + 1) # Due to Python index start from 0
for row_number, value in zip(df_filter.row_number, df_filter[col]):
batch = {'range': f'{col_letter}{row_number}', 'values': [[value]]}
batches.append(batch)
worksheet.batch_update(data=batches, value_input_option='USER_ENTERED')Lời kết
Bài viết đã tổng hợp những case mà người làm phân tích dữ liệu thường sử dụng gspread để làm việc với Google Sheets. Thư viện gspread vẫn còn rất nhiều hàm mà chúng ta ít khi sử dụng, nếu bạn quan tâm thì hãy tham khảo tài liệu hướng dẫn của họ. Hãy để lại bình luận bên dưới để giao lưu với mình nhé!
