[Node.js]利用Google Sheets API操作Google試算表

tags: `Node.js` `API` `Express.js`

本站筆記已同步更新到我的個人網站囉! 歡迎參觀與閱讀，體驗不同的視覺感受!

[Node.js]利用Google Sheets API操作Google試算表

前言

當我們想要把資料放在Google Sheets上方便閱讀、計算等操作的時候，可以透過串接Google Sheets API製作、寫入資料。這裡介紹如何應用Node.js串接Google Sheets API，廢話不多說就開始實作吧！

取得Google憑證

首先，需要有Google憑證(credentials)才可以使用這個服務。

Creat New project:
先進入Google Cloud Console，登入後先建立一個新的專案：Select a project > New Project
Enable API:
選擇側邊欄APIs and services> 接著點選 Enabled APIs and Services，再選擇畫面上方的 + ENABLE APIS AND SERVICES > 在搜尋欄搜尋 google sheets API > 點進Google Sheets API，在Product details的畫面按 Enable > 稍後片刻等他開通 > 開通後跳轉到新的頁面，按側邊欄 Credentials
OAuth consent screen:
進入到Credentials頁面，選擇畫面上方的 + CREATE CREDENTIALS > 選擇 OAuth client ID> 點選右邊的藍色按鈕 configure consent screen > 選擇User Type，此處我選External > 點選CREATE > 填入App information > Save and Continue > 接下來Scopes和Test Users都可以暫時不管他，一樣Save and Continue > 進入Summary頁面，按Back to Dashboard
Create OAuth client ID:
這時總算可以來建立 OAuth client ID了，一樣按側邊欄 Credentials 進入到Credentials頁面，選擇畫面上方的 + CREATE CREDENTIALS > 選擇 OAuth client ID > Application Type選Web Application、填寫Name > 填寫 Authorized redirect URIs（例如：http://localhost:3000/oauth2callback）> 點選CREATE
Download credentials:
建立成功後，會跳轉到OAuth client created的畫面，上面有Clinet ID和Client Secret的資訊，下方有Download JSON選項，按一下就會下載OAuth Client ID的憑證資訊，這個檔案要保存好，等一下會用到（先重新命名為credentials.json）。

專案實作

1. 前置作業(環境設置)

Node.js
npm

2. 初始化專案並安裝相關套件

mkdir sheetAPI
cd sheetAPI
npm init -y
npm i express @google-cloud/local-auth googleapis --save

專案架構如下：

sheetAPI
├─ index.js
├─ package-lock.json
└─ package.json

3. 建立Express伺服器

建立一個基本的Express伺服器：








//index.js
const express = require('express')
const app = express()
const port = 3000

app.listen(port, () => {
  console.log(`Express server is running on port ${port}`)
})

4. 進行驗證並串接Google Sheets API

Google官方文件提供了一個快速設定的範例，稍微修改就可以：
























































//index.js
...
const fs = require('fs').promises;
const path = require('path');
const process = require('process');
const {authenticate} = require('@google-cloud/local-auth');
const {google} = require('googleapis');

// If modifying these scopes, delete token.json.
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
const TOKEN_PATH = path.join(process.cwd(), 'token.json');
const CREDENTIALS_PATH = path.join(process.cwd(), 'credentials.json');

// 檢查TOKEN_PATH中是否存放refresh token
async function loadSavedCredentialsIfExist() {
  try {
    const content = await fs.readFile(TOKEN_PATH);
    const credentials = JSON.parse(content);
    return google.auth.fromJSON(credentials);
  } catch (err) {
    return null;
  }
}

// 讀取CREDENTIALS_PATH中的Credentials進行驗證，產生refresh token並放進TOKEN_PATH中
async function saveCredentials(client) {
  const content = await fs.readFile(CREDENTIALS_PATH);
  const keys = JSON.parse(content);
  const key = keys.installed || keys.web;
  const payload = JSON.stringify({
    type: 'authorized_user',
    client_id: key.client_id,
    client_secret: key.client_secret,
    refresh_token: client.credentials.refresh_token,
  });
  await fs.writeFile(TOKEN_PATH, payload);
}

// 驗證：如果有refresh token，就用refresh token驗證製作access token；如果沒有就先產生refresh token
async function authorize() {
  let client = await loadSavedCredentialsIfExist();
  if (client) {
    return client;
  }
  client = await authenticate({
    scopes: SCOPES,
    keyfilePath: CREDENTIALS_PATH,
  });
  if (client.credentials) {
    await saveCredentials(client);
  }
  return client;
}

Points：

SCOPES是根據我們所要使用的功能選擇授權的範圍，Google Sheets API提供的SCOPES可以看這裡。
CREDENTIALS_PATH是憑證資料的路徑，也就是前面設定OAuth Clinet ID下載的那個JSON檔案，剛才已經重新命名為credentials.json，這裡我直接放在根目錄。
TOKEN_PATH是用來放token.json的路徑，token.json每次進行驗證取得refresh token會自動產生；如果更改SCOPE，記得要把舊的token.json刪除。
目前專案架構如下：

sheetAPI
├─ credentials.json
├─ index.js
├─ package-lock.json
└─ package.json

5. 操作Sheet檔案

驗證完成後，就可以根據選擇的SCOPE對Sheet進行操作，這裡簡單示範幾個功能：

建立新的試算表

spreadsheets.create：官方提供的範例
在index.js中加入以下程式碼：





















// index.js
...
async function createSheet (auth, title) {
  try {
    const service = google.sheets({ version: 'v4', auth })
    const resource = {
      properties: {
        title
      }
    }

    const spreadsheet = await service.spreadsheets.create({
      resource,
      fields: 'spreadsheetId'
    })
    console.log(`Spreadsheet ID: ${spreadsheet.data.spreadsheetId}`)
    return spreadsheet.data.spreadsheetId
  } catch (error) {
    console.log(error)
  }
}

Points：

title是sheet的標題，也可以加入其他內容，參考文件：https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#SpreadsheetProperties
spreadsheetId是每一個sheet獨有的ID，觀察sheet的網址，粗體字的部分就是sheetId:
https://docs.google.com/spreadsheets/d/<spreadsheet Id>/edit?pli=1#gid=0

在createSheet函式下方再加入一個函式，測試看看：










// index.js
...
async function run () {
  try {
    const auth = await authorize()
    const sheetId = await createSheet(auth, 'test')
  } catch (err) { console.error(err) }
}

run()

接著讓伺服器運作，成功的話，會在終端機上面看到：
Spreadsheet ID: <Your spreadsheet Id>
同時根目錄也新增了一個token.json檔案，也可以到web介面看看，網址:https://docs.google.com/spreadsheets/d/<Your spreadsheet Id>/edit?pli=1#gid=0
應該可以看到一個標題是test的試算表建立成功，也可以進入個人的Google Sheets頁面，查看是否新增了一個新的試算表test：
需用取得Google憑證時的Google account去看，目前其他人還沒有權限

在試算表新增資料

spreadsheets.values.update：官方提供的範例
在index.js中加入以下程式碼：
























// index.js
...
async function updateValues (auth, spreadsheetId) {
  const service = google.sheets({ version: 'v4', auth })
  const values = [
    ['Item', 'Cost', 'Stocked', 'Ship Date'],
    ['Wheel', '$20.50', '4', '3/1/2016'],
    ['Door', '$15', '2', '3/15/2016'],
    ['Engine', '$100', '1', '3/20/2016'],
    ['Totals', '=SUM(B2:B4)', '=SUM(C2:C4)', '=MAX(D2:D4)']
  ]
  const resource = {
    values
  }

  const result = await service.spreadsheets.values.update({
    spreadsheetId,
    range: 'Sheet1!A1:D',
    valueInputOption: 'USER_ENTERED',
    resource
  })
  console.log('%d cells updated.', result.data.updatedCells)
  return result
}

Points：

values:試算表範圍內的資料，也就是要操作的資料，values是必填的參數，型態是陣列(array)。其他參數參考文件。
valueInputOption是指系統如何解讀輸入資料。例如這裡使用USER_ENTERED，則系統會解析Total那列的'=SUM(B2:B4)'，加總B2:B4的值，就和在Excel資料表裡面操作一樣的方法。其他選項參考文件。
range是指資料的範圍，可以指定多個儲存格 (例如 A1:D5) 或單一儲存格 (例如 A1)。詳細用法參考文件。或是範例中也有提到:

在run()中加入：










// index.js
...
async function run () {
  try {
    ...
    await updateValues(auth, sheetId)
  } catch (err) { console.error(err) }
}

run()

同樣讓伺服器運作，成功的話，會在終端機上面看到：

Spreadsheet ID: <Your spreadsheet Id>
20 cells updated.

到web介面看看，這時可以看到test試算表中有資料了!

讀取試算表的資料

spreadsheets.values.get：官方提供的範例
一樣根據範例稍微改寫，在index.js中加入以下程式碼：
















// index.js
...
async function getValues (auth, spreadsheetId) {
  try {
    const service = google.sheets({ version: 'v4', auth })
    const result = await service.spreadsheets.values.get({
      spreadsheetId,
      range: 'Sheet1!A1:D2'
    })
    const numRows = result.data.values ? result.data.values.length : 0
    console.log(`${numRows} rows retrieved.`)
    return result.data.values
  } catch (error) {
    console.log(error)
  }
}

在run()中加入：











// index.js
...
async function run () {
  try {
    ...
    const result = await getValues(auth, sheetId)
    return console.log(result)
  } catch (err) { console.error(err) }
}

run()

同樣讓伺服器運作，成功的話，會在終端機上面看到：

Spreadsheet ID: <Your spreadsheet Id>
20 cells updated.
2 rows retrieved.
[
  [ 'Item', 'Cost', 'Stocked', 'Ship Date' ],
  [ 'Wheel', '$20.50', '4', '3/1/2016' ]
]

成功讀取了A1:D2這個範圍的資料，也就是Web介面上：

小結

以上簡單介紹了Google Sheets API的一些用法，還有很多其他的功能都可以參考API文件。前面介紹了使用OAuth Client ID進行驗證的方法，另外也可以使用Service Account進行驗證，驗證方式可以參考這篇文章；使用Service Account驗證須注意權限，如要用自己的gmail帳號登入需要開啟權限，除了前面這篇文章中使用的方式（先用Gmail帳號開新的Sheet再分享權限給Service Account）之外，另外也可以使用Google Drive API給予權限，詳情可以參考這篇文章，以及Google Drive API文件permissions.create或permissions.update，當然直接使用Google Drive API建立Sheet也是可以的。

參考資料

　本站內容僅為個人學習記錄，如有錯誤歡迎留言告知、交流討論！

[Node.js]利用Google Sheets API操作Google試算表

tags: Node.js API Express.js

前言

取得Google憑證

專案實作

1. 前置作業(環境設置)

2. 初始化專案並安裝相關套件

3. 建立Express伺服器

4. 進行驗證並串接Google Sheets API

5. 操作Sheet檔案

建立新的試算表

在試算表新增資料

讀取試算表的資料

小結

參考資料

Read more

利用指令建立Express專案的步驟 - 筆記

Event Loop Note

應用Redis在Node.js中實作快取(Cache) - 筆記

連線資料庫及建立Model的步驟(MongoDB) - 筆記

tags: `Node.js` `API` `Express.js`