SpreadSheet に書き込むscript

.env

# Ref: https://zenn.dev/catnose99/scraps/2d0abd8f32f91e#comment-6936e4d21939b4

SHEET_ID='foo' # スプレッドシートのURLに含まれる文字列
GOOGLE_SERVICE_ACCOUNT_EMAIL='[email protected]' # サービスアカウントのアドレス
GOOGLE_PRIVATE_KEY='-----BEGIN PRIVATE KEY-----\nabcdefg...' # サービスアカウントのkeyのJSONに含まれる`"private_key"`の値をそのままコピペする

index.ts

import { GoogleSpreadsheet } from "google-spreadsheet"
import dotenv from "dotenv"

dotenv.config()

// Ref: https://zenn.dev/catnose99/scraps/2d0abd8f32f91e
const sheetId = process.env.SHEET_ID
const clientEmail = process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL as string
const privateKey = process.env.GOOGLE_PRIVATE_KEY as string


;(async () => {
  const doc = new GoogleSpreadsheet(sheetId)
  await doc.useServiceAccountAuth({
    client_email: clientEmail,
    private_key: privateKey.replace(/\\n/g, "\n"),
  })
  await doc.loadInfo()
  console.log(doc.spreadsheetId)

  const sheet = doc.sheetsByIndex[0]
  await sheet.addRows([
    {a: 1, b: 2},
    {a: 3, b: 4}
  ])

  console.log("Done.")
})()

package.json

{
  "name": "test",
  "version": "1.0.0",
  "description": "",
  "type": "commonjs",
  "main": "index.js",
  "scripts": {
    "post": "ts-node lib/index.ts"
  },
  "keywords": [],
  "author": "makotot",
  "license": "ISC",
  "devDependencies": {
    "@types/google-spreadsheet": "3.3.1",
    "@types/node": "18.15.11",
    "dotenv": "16.0.3",
    "google-spreadsheet": "3.3.0",
    "ts-node": "10.9.1",
    "typescript": "5.0.3"
  }
}

https://github.com/theoephraim/node-google-spreadsheet