Sử dụng gspread để lấy và cập nhật dữ liệu Google Sheets

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 gspreadCopy

Sử 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)Copy

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()Copy

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')Copy

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')Copy

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.

1. Kết nối worksheet.
2. Xoá toàn bộ giá trị từ dòng 2 trở lên.
3. 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')Copy

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')Copy

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!')Copy

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']]
    }
])Copy

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à:

1. Tạo row_number bằng index của data frame.
2. Lọc ra các dòng có thay sự thay đổi.
3. Tạo danh sách các cột cần cập nhật.
4. 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ạo range.
5. 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')Copy

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é!