Last active
December 20, 2022 02:04
-
-
Save WorldDownTown/8030777267c4b0e0d19820631359f22c to your computer and use it in GitHub Desktop.
Google Sheets の最終行に追記するスクリプト
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
# Google Sheets の最終行に追記するスクリプト | |
# 訳あって最終行の位置を特定するために行数を取得している | |
# OAuth周りは下記がわかりやすい | |
# https://qiita.com/shin1ogawa/items/49a076f62e5f17f18fe5 | |
CLIENT_ID="Google API ClientID" | |
CLIENT_SECRET="Google API's client secret" | |
REFRESH_TOKEN="Google API's refresh token" | |
SHEET_ID="Google Sheets SheetID" | |
# Access Tokenの有効期限は1時間なので毎回リフレッシュする | |
# https://developers.google.com/identity/protocols/OAuth2 | |
ACCESS_TOKEN=`curl --data "refresh_token=$REFRESH_TOKEN" --data "client_id=$CLIENT_ID" --data "client_secret=$CLIENT_SECRET" --data "grant_type=refresh_token" \ | |
https://www.googleapis.com/oauth2/v4/token \ | |
| jq -r .access_token \ | |
| tr -d "\n"` | |
# 現在の行数を取得 | |
# スプレッドシートは200万セルが上限らしいので "A1:A2000000" を指定 | |
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get?hl=ja | |
ROWS_COUNT=`curl -H "Content-Type:application/json" -H "Authorization: Bearer $ACCESS_TOKEN" \ | |
https://sheets.googleapis.com/v4/spreadsheets/$SHEET_ID/values/A1:A2000000 \ | |
| jq -r '.values|length' \ | |
| tr -d "\n"` | |
# 書き込み対象のセルのRange | |
NEXT_RANGE=A`expr $ROWS_COUNT + 1` | |
# 書き込みリクエスト | |
# 最新行の先頭2つのセルに "value1", "value2" を追記する | |
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append?hl=ja | |
# Query parameters に "insertDataOption=INSERT_ROWS" を付けると書き込み対象セルを厳密に与えなくても最終行に追記する仕様なのだが、先頭のカラムを非表示にすると最終行ではなく先頭に書き込まれてしまった | |
# そのためわざわざ行数を取得してRangeを組み立てている | |
curl -H "Content-Type:application/json" -H "Authorization: Bearer $ACCESS_TOKEN" -d \ | |
' | |
{ | |
"values": [ | |
["value1", "value2"] | |
] | |
} | |
' \ | |
https://sheets.googleapis.com/v4/spreadsheets/$SHEET_ID/values/$NEXT_RANGE:append?valueInputOption=USER_ENTERED \ | |
> /dev/null |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment