Đọc ghi dữ liệu Google Sheets qua API bằng Node.js
White

Mòe viết ngày 30/08/2021

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.
alt text

Phần 1. Tạo app, lấy key

Bước 1: Tạo Google Cloud project (không cần nộp card)

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
alt text

Bấm Enable APIs and Services
alt text
Tìm Google Sheets API rồi Enable nó. Enable xong đừng đóng tab!
alt text

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
alt text
Nếu đóng mất rồi, bạn nhấn lại vào API & Services > Credentials
alt text

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_type thành offline trong request
  • Đổi client_id thà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
alt text
alt text

Bỏ qua bước Grant

Bước 4: Tạo Key

Nhấn Edit cái service account credential vừa tạo
alt text

Trong tab Key bấm Add Key

alt text

Để 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
alt text

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//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ệu
  • sheets.spreadsheets.values.batchGet để đọc nhiều vùng dữ liệu một lúc
  • sheets.spreadsheets.values.update để ghi 1 vùng dữ liệu
  • sheets.spreadsheets.values.batchUpdate để ghi nhiều vùng dữ liệu một lúc
  • sheets.spreadsheets.values.append ghi 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ải thì đượ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 ghi Sheet1!A2:E thô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 đề

alt text

  • 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.

Bình luận


White
{{ comment.user.name }}
Bỏ hay Hay
{{comment.like_count}}
Male avatar
{{ comment_error }}
Hủy
   

Hiển thị thử

Chỉnh sửa

White

Mòe

8 bài viết.
360 người follow
Kipalog
{{userFollowed ? 'Following' : 'Follow'}}
Cùng một tác giả
White
132 45
Google cách học tốt Tiếng Anh thì ra hàng đống kết quả nhưng bạn mãi không dùng được cái nào. Vậy thay vì tìm cách học tốt hãy đọc thử những cách h...
Mòe viết hơn 3 năm trước
132 45
White
44 10
(Ảnh) Dùng CSS để "sửa" nội dung HTML Ngoài nguyên nhân là muốn chơi nổi thì lí do phải dùng code CSS để tác động vào HTML là khi bạn không được...
Mòe viết gần 3 năm trước
44 10
White
36 16
Các khái niệm riêng của Wordpress dễ gây nhầm lẫn Ai muốn làm theme động luôn và không muốn hiểu lý thuyết có thể bỏ qua phần này. Ngay ở documen...
Mòe viết hơn 2 năm trước
36 16
{{like_count}}

kipalog

{{ comment_count }}

bình luận

{{liked ? "Đã kipalog" : "Kipalog"}}


White
{{userFollowed ? 'Following' : 'Follow'}}
8 bài viết.
360 người follow

 Đầu mục bài viết

Vẫn còn nữa! x

Kipalog vẫn còn rất nhiều bài viết hay và chủ đề thú vị chờ bạn khám phá!