Google/Sheets API - api_programming @ ウィキ

下位ページ

• Java

Content

https://developers.google.com/sheets/?hl=ja

URI

https://sheets.googleapis.com

batchGet : シートのデータを取得する

• URL/通信方式
  • GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGet
• URL
  • spreadsheetId : string
    • The ID of the spreadsheet to retrieve data from.
• リクエストパラメータ
  • ranges : string
    • The A1 notation of the values to retrieve.
  • majorDimension : enum(Dimension)
    • The major dimension that results should use.
    • For example, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then requesting range=A1:B2,majorDimension=ROWS will return 1,2],[3,4, whereas requesting range=A1:B2,majorDimension=COLUMNS will return 1,3],[2,4.
  • valueRenderOption : enum(ValueRenderOption)
    • How values should be represented in the output.
• dateTimeRenderOption : enum(DateTimeRenderOption)
  • • How dates, times, and durations should be represented in the output. This is ignored if valueRenderOption is FORMATTED_VALUE.

batchGet

GET : シートのデータを取得する

HTTP request

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}

• spreadsheetId : string
  • The ID of the spreadsheet to retrieve data from.
  • スプレッドシートを開いたときの、https://docs.google.com/spreadsheets/d/{ここ}/edit#gid=0
• range : string
  • A1形式で記述。A1だけなら "A1" A1,A2,B1,B2なら"A1:B2"

Query parameters

Parameters

majorDimension	enum(Dimension), DIMENSION_UNSPECIFIED,ROWS,COLUMNS	レスポンスに含まれる値が、行→列で値を返すか、列→行で値を返すか。A1=1,B1=2,A2=3,B2=4 で A1:B2 の値を要求したとき、ROWS なら [[1,2],[3,4]], COLUMNS なら [[1,3],[2,4]] が返る。
valueRenderOption	enum(ValueRenderOption), FORMATTED_VALUE,UNFORMATTED_VALUE,FORMULA	返す値をどのように表現して返すか
dateTimeRenderOption	enum(DateTimeRenderOption),SERIAL_NUMBER,FORMATTED_STRING	日時をどのように表現して返すか

GET
https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetID}/values/A1?dateTimeRenderOption=FORMATTED_STRING&majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE&key={YOUR_API_KEY}

Request body

The request body must be empty.

Response body

If successful, the response body contains an instance of ValueRange.

{
 "range": "'シート1'!A1",
 "majorDimension": "ROWS",
 "values": [
  [
   "September 18, 2016 at 05:16PM"
  ]
 ]
}

認証（scope）

• https://www.googleapis.com/auth/drive
• https://www.googleapis.com/auth/drive.readonly
• https://www.googleapis.com/auth/spreadsheets
• https://www.googleapis.com/auth/spreadsheets.readonly

https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/get?hl=ja

APPEND

既存のデータ範囲を考えて、最後の行にデータを追加する。
※

• HTTP
  • POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append
• パスに設定するパラメータ
  • spreadsheetId : string
    • The ID of the spreadsheet to update.
  • range : string
    • The A1 notation of a range to search for a logical table of data.
    • Values will be appended after the last row of the table.
• 送信データに設定するパラメータ
  • valueInputOption : enum(ValueInputOption)
    • How the input data should be interpreted.
  • insertDataOption : enum(InsertDataOption)
    • How the input data should be inserted.

• Request body
  • The request body contains an instance of ValueRange.

D14を起点として、開いている行を探して、2×2のデータを挿入したい。データは

test11	test12
test21	test22

https://sheets.googleapis.com/v4/spreadsheets/aaaaa/values/D14:append?valueInputOption=USER_ENTERED&key={YOUR_API_KEY}

{
 "values": [
  [
   "test11",
   "test12"
  ],
  [
   "test21",
   "test22"
  ]
 ]
}

レスポンス例

--ヘッダ--
cache-control:  private
content-encoding:  gzip
content-length:  178
content-type:  application/json; charset=UTF-8
date:  Sun, 09 Oct 2016 09:02:52 GMT
server:  ESF
vary:  Origin, X-Origin, Referer

--ボディ--
{
 "spreadsheetId": "aaaaa",
 "tableRange": "'シート1'!D14:E15",
 "updates": {
  "spreadsheetId": "aaaaa",
  "updatedRange": "'シート1'!D16:E17",
  "updatedRows": 2,
  "updatedColumns": 2,
  "updatedCells": 4
 }
}

https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/append?hl=ja