Google Apps Script (GAS)でスプレッドシートをGETする

はじめに

本記事ではGASを利用してGoogleスプレッドシートの内容をGET要求することで、内容のJSONを取得するAPIを簡易的に作成することを目的とします。

最終的にはGoogle Driveの特定フォルダにアップロードしたファイルの一覧の中から最新のファイルのデータをJSONで取得するAPIを作成したいと考えており、その手始めとしてテストデータにファイル一覧を想定したものの内容をすべて取得するAPI作成します。

• 注釈

2024年12月時点での情報です。

Googleスプレッドシートを作成

共有設定で公開する

共有用のURLは後で使用します。

テストデータの入力

今回は仮に以下のように記述しました

そして、シートのページ名を[sheet1]に変更します

画像の拡張機能、[Apps Script]を選択してGASの設定を行う

APIはGASで提供されている機能として関数をある一定の名前で作成することで、GetやPostなどの要求された時にその関数を実行する機能を利用します。

テストコード

//プロジェクトのスクリプトプロパティから値を取得
const sheetId = PropertiesService.getScriptProperties().getProperty('SHEET_ID');
const apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
const sheetPageName = PropertiesService.getScriptProperties().getProperty('SHEET_PAGE_NAME');

//関数名をdoGetとすることでGet要求時に関数を実行する様になる
function doGet(){
  try{
      // リクエストオプション
      const requestOptions = {
          'method' : 'get',
      }
      const requestUrl = "https://sheets.googleapis.com/v4/spreadsheets/" + sheetId + "/values/" + sheetPageName + "?key=" + apiKey
      const response = UrlFetchApp.fetch(requestUrl, requestOptions)
      const responseCode = response.getResponseCode()
      const responseText = response.getContentText()
      //log出力
      console.log(responseCode)
      console.log(responseText)
      //取得した値を返す
      return ContentService.createTextOutput(responseText).setMimeType(ContentService.MimeType.JSON)
    }catch(e){
      console.log('エラーを検知しました。')
      console.log('エラー内容：'+e.message)
    }
}

プロジェクトのスクリプトプロパティの設定

環境変数のような形でKeyValueで値を保持してくれる機能があるため、これを利用して後述するAPI_KEYをGETするユーザから見えないようにします。

GASの設定画面の左タブの[プロジェクトの設定]を選択

API_KEYに設定する値は後述する

SHEET_IDに設定する値は、スプレッドシートのURLを参照する。

https://docs.google.com/spreadsheets/d/xxxxxSHEET_IDxxxxx/edit?usp=sharing

[xxxxxSHEET_IDxxxxx]に当たる文字列がSHEET_IDとなる。

API_KEYの取得

https://console.cloud.google.com/apis

[APIライブラリ]

[Google Sheets API]を有効にする

[認証情報]

[認証情報を作成]

[APIキーを作成する]

• 注釈

今回はAPIキーへのアクセス制限について記述しませんが、適切に制限をかける必要があります。

最後にGASのスクリプトプロパティにAPI_KEYの値を入力しておく。

GASのデプロイ

[デプロイ]ボタンから、[新しいデプロイ]、[ウェブアプリ]、次のユーザとして実行を[自分]、アクセスできるユーザを[全員]にしてデプロイします。

デプロイされたウェブアプリURLへGet要求を投げると以下のように内容を取得できます。

{
  "range": "sheet1!A1:Z1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "id",
      "name"
    ],
    [
      "1",
      "pdf01.pdf"
    ],
    [
      "2",
      "pdf02.pdf"
    ],
    [
      "3",
      "pdf03.pdf"
    ]
  ]
}

まとめ

今回、API_KEYをスクリプトプロパティに格納することで、コード自体にKEYを記述することなく作成できました。このように、無料でできる範囲でもスプレッドシートのデータをJSONで取得することができました。

今後は、responseに返すデータを絞り込むことや、Google Driveの内容にアクセスすることを目標にしていきたいと思います。

参考サイト

Google Sheets API

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