Dữ liệu Google Sheets bảng tính là công cụ được sử dụng phải nói là nhiều vô đối trong cuộc sống và công việc của chúng ta. Sheets API sẽ giúp chúng ta đọc dữ liệu trong file Sheets rồi đem xử lý, hoặc ghi dữ liệu mới vào, tiết kiệm thời gian công sức.
Theo như doc thì đối với các file sheet public, ta có thể dùng API Key cũng được. Còn nếu file private thì nhất định phải có access token từ người dùng Google có quyền xem sửa file. Bài này mình nói về các file private nhé. Tuy nhiên đối với credentials thì thay vì OAuth 2.0 Client IDs mình thử mãi vẫn lỗi, mình dùng Service Account và JWT Client được luôn.
Đọc dữ liệu Google Sheets qua API bằng Node.js
Bước 1: Tạo Google Cloud project (không cần nộp card)
- Vào Google Cloud Console
- Tìm nút Create Project để create
Bước 2: Enable Sheets API trong project
Vẫn trong cái giao diện Cloud Console, nhìn sang tab bên trái kéo lên kéo xuống tìm API & Services
Bấm Enable APIs and Services
Tìm Google Sheets API rồi Enable nó. Enable xong đừng đóng tab!
Bước 3: Tạo Credentials
Ở cái tab lúc nãy, nếu chưa đóng mất thì bấm Create Credentials
Nếu đóng mất rồi, bạn nhấn lại vào API & Services > Credentials
Chú ý: Khi chọn loại credentials
Nếu bạn đang đọc doc thì sẽ được hướng dẫn tạo OAuth client. Lúc lấy access token thì mọi thứ diễn ra bình thường, access token có tác dụng trong 1 tiếng. Nhưng khi hết hạn và cần phải dùng refresh token thì đôi khi refresh token bị lỗi invalid. Trên mạng có mấy cách giải quyết:
- Nguyên nhân do thay đổi scope, phải xóa token đi lấy lại
- Đổi
access_typethànhofflinetrong request - Đổi
client_idthành địa chỉ email (Google đã fix lỗi này) - và một số cases khác
Mình đã thử hết mà vẫn lỗi. Sau cùng thì phải chọn Service Accounts thì giải quyết được
Bấm Create Credentials > Service Account
Bỏ qua bước Grant
Bước 4: Tạo Key
Nhấn Edit cái service account credential vừa tạo
Trong tab Key bấm Add Key
Để type mặc định JSON, sau khi bấm Create nó sẽ tải file json chứa credentials về máy của bạn
Phần 2. Kết nối tới Sheets thông qua API
Google có hướng dẫn kết nối bằng đủ loại ngôn ngữ ở đây. google api nodejs client có Github ở đây. Mình sẽ viết về cách kết nối bằng Nodejs heng. Ai muốn test nhanh có thể down code của mình về coi tạm, điền key vào trong file key.json rồi chạy file index.js rồi bật console log để xem thử.
Bước 1. Cho mail service account quyền edit file
Quay lại bước 4 lúc nãy copy cái địa chỉ mail service account, rồi chia sẻ quyền cho mail đó được edit file sheet của bạn
Bước 2. import các tài nguyên
OK bắt đầu vào việc tới code rồi
Đầu tiên trong bài này để cho nhanh mình import cả file json vừa tải được từ các bước trên để lát dùng. Còn ở project thật thì bạn cần lưu ở nơi bảo mật hơn
const key = require('./key.json');
Bạn cũng cần tải cái package googleapis để vào việc cho dễ
npm i googleapis
Cài xong ta import object google từ cái package googleapis đó vào trong file node js
const { google } = require('googleapis');
Lưu sheet ID của cái file ta cần xử lý, vào 1 biến, vd link file https://docs.google.com/spreadsheets/d/1oFYCK6Dn9lHZOaMSjfuzPEwSh5N4J3afrOufrp379JE/edit#gid=0 thì id là đoạn mã lùng nhùng giữa /d/ và /edit
const SHEET_ID = '1oFYCK6Dn9lHZOaMSjfuzPEwSh5N4J3afrOufrp379JE';
Bước 3. Khởi tạo sheets object và JWT Client
Từ cái object google ta khởi tạo 2 thứ:
Khởi tạo object sheets, đây là object chứa các hàm làm việc với bảng tính
const sheets = google.sheets('v4');
Khởi tạo JSON Web Token Client, đây là client dùng cái file key ban nãy ta tải, để authorize với Google
const authClient = new google.auth.JWT(email, keyFile, key, scopes)
Trong đó
- email: bạn điền client_email trong file key.json ban nãy
- keyFile: để null
- key: điền private_key
- scopes: phạm vi quyền, ở đây điền string
'https://www.googleapis.com/auth/spreadsheets'là quyền đọc ghi Spreadsheets
Bước 4. (Không bắt buộc) Dùng JWT client để lấy access token
Thật ra bước này không bắt buộc vì trong Nodejs driver của Google cho đã có sẵn hàm để authorize bằng JWT Client rồi, nhưng nếu trường hợp nào bạn cần dùng đến access token thì có thể lấy bằng cách gọi GET request như sau
const res = await jwtClient.request({
url: `https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}/`
});
Response trả về sẽ có chứa access token nằm trong headers bên trong object config, bạn lôi nó ra
const access_token = res.config.headers.Authorization;
Phần 3. Đọc ghi dữ liệu từ file bảng tính
Có mấy hàm chính để xài đó là
sheets.spreadsheets.values.getđể đọc 1 vùng dữ liệusheets.spreadsheets.values.batchGetđể đọc nhiều vùng dữ liệu một lúcsheets.spreadsheets.values.updateđể ghi 1 vùng dữ liệusheets.spreadsheets.values.batchUpdateđể ghi nhiều vùng dữ liệu một lúcsheets.spreadsheets.values.appendghi nhưng append vào cuối file chứ ko đè lên những ô đã có dữ liệu
Theo kinh nghiệm hạn hẹp của mình thì để tránh bị quá giới hạn request limit rồi ăn cái lỗi Client network socket disconnected before secure TLS connection was established thì có 2 cách, một là gửi request lẻ tẻ nhưng làm chầm chậm thôi. Hai là dồn dữ liệu lại rồi gửi cả mẻ (batch) một lúc. Cụ thể cách xài các hàm chắc mọi người có thể đọc ở doc viết khá rõ. Nên mình chỉ ví dụ về đọc ghi cả mẻ nhé.
Đọc – batchGet
Dùng method batchGet để gởi request với option object chứa các properties là spreadsheet ID, ranges, auth như sau
const response = await sheets.spreadsheets.values.batchGet(
{
spreadsheetId: SHEET_ID,
ranges: ['Sheet1!A2:E','Sheet2!A2:E'],
auth: jwtClient,
}
);
Trong đó:
- speadsheetId: là sheet ID của bạn
- ranges: là 1 array gồm các vùng dữ liệu cần đọc, có thể lấy ở nhiều tab của một bảng tính, tưởng tượng vùng dữ liệu là một hình chữ nhật, tọa độ được xác định theo đường chéo, ô trên cùng bên trái là A2, ô dưới cùng bên phải là E10. theo cú pháp
Tên tab ! Ô ngoài cùng bên trái : Ô dưới cùng bên phảithì được cái range làSheet1!A2:E10. Nếu mình không muốn giới hạn chiều dọc của hình chữ nhật thì mình ghiSheet1!A2:Ethôi. Tại sao lại là A2 mà không phải A1? Vì mình trừ hàng đầu tiên ra để ghi tiêu đề
- auth: là cái JWT Client tạo ở bước trước
Chú ý: Các batch method thì phải có ranges mà ở non-batch phải có range, khác tí là fail.
Request thành công sẽ trả cho bạn data dạng
{
spreadsheetId: '1oFYCK6Dn9lHZOaMSjfuzPEwSh5N4J3afrOufrp379JE',
valueRanges: [
{
range: 'Sheet1!A2:E1000',
majorDimension: 'ROWS',
values: [Array]
}
]
}
Cái array ở trên là dữ liệu các ô trong file sheet của ta. Bạn có thể moi nó ra như sau. Chú ý ở đây mình lấy của vùng dữ liệu thứ nhất nên chỗ valueRanges[0]
const values = response.data.valueRanges[0].values;
console.log(values);
Thì được trả về dữ liệu là một array chứa nhiều array con tượng trưng cho các hàng trên vùng dữ liệu.
[ [ 'Son Tung', '18', 'Singer' ], [ 'Chi Pu', '18', 'Actress' ] ]
Ghi – append
Dùng method append
const response = await sheets.spreadsheets.values.append({
spreadsheetId: SHEET_ID,
range: ['Sheet1!A2:C'],
auth: jwtClient,
valueInputOption: "RAW",
resource: {
values: [['Ton Ngo Khong', 21, 'King of Monkeys'],['Kim Jong Un', 30,'President']]
}
});
Trong đó:
- 3 dòng đầu giống chỗ Đọc
- valueInputOption: chọn là “RAW” là ghi y như nguồn chứ không phải tính toán ra kết quả gì cả
- resource: là 1 object có property là values, chứa array gồm nhiều array con tượng trưng cho nhiều hàng cần update
Chú ý: Ở các method ghi, valueInputOption: RAW là bắt buộc
Muốn biết có bn dòng đc cập nhật ta dùng response.data.updates.updatedCells của cái response object
Ghi – batchUpdate
Gửi request bằng method batchUpdate
const response = await sheets.spreadsheets.values.batchUpdate({
spreadsheetId: SHEET_ID,
auth: jwtClient,
resource: {
valueInputOption: "RAW",
data: [{
range: "Sheet1!E2:F",
values: [[1, 2], [3, 4]],
},
{
range: "Sheet1!H2:I",
values: [["haha", "hehe"], ["huhu", "hichic"]],
}
],
},
});
Trong đó:
- spreadsheetId, auth: như các phần trước
- resource: chú ý chỗ resource ở 2 method trước thì cái valueInputOption với range ở ngoài giờ nhảy vô trong resource rồi
Bài tut đến đây là kết thúc, chúc các bạn không bị chán quá vì nó quá dài hic.
kipalog
