スプレッドシートをAPIで操作してみる

システムからスプレッドシートを操作する機会があったので、今回はスプレッドシートを操作するAPI

「Google Sheets API」

を紹介したいと思います。サンプルコードはTypeScriptです。

ドキュメント

Googleから日本語ドキュメントが公開されています。
https://developers.google.com/sheets/api/guides/concepts?hl=ja

認証／認可

APIを利用するために認証まわりの準備が必要です。
こちらもGoogleから日本語ドキュメントが公開されています。

https://developers.google.com/workspace/guides/get-started?hl=ja

OAuthを利用したクライアントを準備するサンプルコード

const authClient = new google.auth.OAuth2({
  clientId: クライアントID,
  clientSecret: クライアントシークレット,
});
authClient.setCredentials({
  access_token: アクセストークン,
  refresh_token: リフレッシュトークン,
  token_type: 'Bearer',
  scope: 'https://www.googleapis.com/auth/spreadsheets',
});

const sheetsClient = google.sheets({
  version: 'v4',
  auth: authClient,
});

書き込んでみる

シートの1行目A列に「あいうえお」と書き込んでみます。書き込みは
「spreadsheets.values.update」メソッドを使用します。

await sheetsClient.spreadsheets.values.update({
  spreadsheetId: スプレッドシートID,
  range: 'シート1',
  valueInputOption: "USER_ENTERED",
  requestBody: {
    values: [
      ['あいうえお']
    ],
  },
});

1行目A列に書き込まれました。引数をそれぞれ解説します。

spreadsheetId

書き込むスプレッドシートのIDを指定します。IDはスプレッドシートのURLにあります。以下のURLのXでマスクしている部分がIDとなります。

https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit?hl=ja#gid=0

range

書き込みを開始する位置を「A1表記」という記法でシートとセルを指定します。今回はシート名のみを指定したのでシートの１行目A列に書き込まれました。 'シート1!A1'と指定しても同様の結果となります。'シート1!B1'と指定するとB列から書き込みます。
セル名の後の数字は行番号ですので、'シート1!A2'とすることでA列2行目から書き込むといったことも可能です。

valueInputOption

書き込むデータをスプレッドシート側が加工するかどうかを指定します。加工する場合は「USER_ENTERED」、加工しない場合は「RAW」と指定します。
USER_ENTEREDとした場合、データが「=1+1」のような式として解釈できる文字列のとき、「式」としてシートに反映され、画面上でセル値は「2」となります。
RAWとした場合はそのままセル値は「=1+1」という文字列となります。

requestBody

セルに書き込むデータをvaluesプロパティで指定します。valuesは2次元配列です。

1行目A列とB列に書き込まれる

  requestBody: {
    values: [
      ['あいうえお', 'かきくけこ']
    ],
  },

1行目A列と2行目A列に書き込まれる

  requestBody: {
    values: [
      ['あいうえお'],
      ['かきくけこ'],
    ],
  },

追記してみる

「spreadsheets.values.append」メソッドを使用することでシートの最終行から書き込むことができます。
追記ですのでrangeの指定はシート名のみとなります。セルを指定することはできません。

await sheetsClient.spreadsheets.values.append({
  spreadsheetId: スプレッドシートID,
  range: 'シート1',
  valueInputOption: "USER_ENTERED",
  requestBody: {
    values: [
      ['かきくけこ']
    ],
  },
});

データが以下のようにB列から存在しているシートに追記をした場合、B列から追記されます。絶妙に賢いですね。

追記するとB列から書き込まれる。

取得してみる

最後にシートのデータを取得してみます。以下はシート1のすべてのデータを取得するコードです。
results.data.valuesに2次元配列でデータが入っています。

const results = await sheetsClient.spreadsheets.values.get({
  spreadsheetId: スプレッドシートID,
  range: 'シート1'
});
console.dir(results.data.values);

rangeのA1表記で取得する範囲を絞ることが可能です。

2行目以降のデータを取得する

シート1!A2:Z
※列の終了を指定する必要があります。列がZよりも長い場合はZZのように変更してください

A列のみ全行取得する

シート1!A:A

A列とB列の1行目から3行目を取得する

シート1!A1:B3
※矩形選択をイメージするとわかりやすいと思います

以上です！

シナジーマーケティング株式会社では一緒に働く仲間を募集しています。