Google Apps Script を使って、Google Spreadsheet にあるデータを検索するWeb APIが作れないか気になりました。

そこで、試してみたときのメモを残します。

環境
Google Apps Script で、Google Spreadsheet のデータを検索する方法
- TextFinderを使って検索
- QUERY関数を使ってデータを検索
Google Apps Scriptにて、JSONを返すAPIを作る
動作確認
ソースコード

環境

Google Apps Script

　
また、今回は量がそこそこあるデータから検索してみるため、郵便番号データを使うことにします。
読み仮名データの促音・拗音を小書きで表記するもの - zip圧縮形式日本郵便

その中の KEN_ALL.csv ファイルを Google Spreadsheet として保存し、Google Apps Script で検索することを考えてみます。

Google Apps Script で、Google Spreadsheet のデータを検索する方法

Google Apps Script で、Google Spreadsheet のデータを検索する方法を調べてみたところ、以下の2つがありました。

TextFinderを使って検索
QUERY関数を使ってデータを検索

　
それぞれどんな感じになるか、試してみます。

TextFinderを使って検索

TextFinderを使う場合、

createTextFinder() でTextFinderを作成
- createTextFinder | Class Sheet | Apps Script | Google Developers
findAll() で検索を実行
- findAll | Class TextFinder | Apps Script | Google Developers)

という処理の関数を作れば良さそうでした。

function findByTextFinder(keyword) {
  // TextFinderを使う
  // https://developers.google.com/apps-script/reference/spreadsheet/sheet#createtextfinderfindtext

  const sheet = SpreadsheetApp.getActive().getSheetByName("KEN_ALL")

  const textFinder = sheet.createTextFinder(keyword)
  const items = textFinder.findAll()

  return items.map(item => {
    return {
      'zip_code': sheet.getRange(item.getRow(), 3).getValue(),
      'banchi': sheet.getRange(item.getRow(), 9).getValue()
    }
  })
}

QUERY関数を使ってデータを検索

Google Spreadsheet には QUERY 関数があります。
QUERY - Google ドキュメントエディタヘルプ

これを使うことで、SQLライクな文法のGoogle Visualization API のクエリ言語でデータを検索できます。
Query Language リファレンス（バージョン 0.7） | Charts | Google Developers

　
QUERY関数をGoogle Apps Script で使う方法がないかを調べたところ、セルの setValue() を使うと関数が使えそうでした。

setValue(value) 範囲の値を設定します。値は数値、文字列、ブール値、日付にできます。'=' で始まる場合、数式として解釈されます。

https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluevalue

　
そこで、

検索結果を貼り付けるシート(結果シート)を用意
結果シートの1つのセルに、 setValue() でQUERY関数の結果を貼り付け
貼り付けたQUERY関数の結果を読み込み、レスポンスデータを作成

を行う関数を用意すれば良さそうでした。

function responseByQueryFunction(keyword) {
  // QUERY関数を使う
  // setValueに関数を入れられるのでそれを利用する
  // https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluevalue

  // `RESULT` シートに書き込み
  // WHEREの左辺で列を指定して検索できる
  const sheet = SpreadsheetApp.getActive().getSheetByName("RESULT")
  sheet.getRange(1, 1).setValue(`=QUERY(KEN_ALL!A:I,"WHERE I LIKE '%${keyword}%'")`)

  const results = []

  const lastRow = sheet.getLastRow()
  for (let i=2; i <= lastRow; i++) {
    const zip = sheet.getRange(i, 3).getValue()
    const banchi = sheet.getRange(i, 9).getValue()

    results.push({ zip, banchi })
  }

  return results
}

　
なお、実行後のRESULTシートはこんな感じになります。

Google Apps Scriptにて、JSONを返すAPIを作る

Google Apps ScriptでWeb APIを作る方法については、以前試したことがあったため、その時の実装を流用すれば良さそうでした。
SendGridのEvent Webhookでメールを識別するため、X-SMTPAPIヘッダのUnique Argumentsを使ってみた - メモ的な思考的な

　
また、クエリパラメータ finder の値により、TextFinderとQUERY関数のどちらを使うかを決められるようにしました。

function doGet(e) {
  const params = e.parameter
  const keyword = params.searchKey
  const finder = params.finder

  const results = finder === 'query' ? findByQueryFunction(keyword) : findByTextFinder(keyword)

  const response = ContentService.createTextOutput()
  response.setMimeType(ContentService.MimeType.JSON)
  response.setContent(JSON.stringify({data: results}))

  return response
}

動作確認

今回はブラウザで動作確認を行います。

そのため、まずはブラウザでアクセスできるよう、Google Apps Scriptをデプロイします。

次に、デプロイした後に表示されるURLにリクエストパラメータを加えて、ブラウザでアクセスします。

例えば、 極楽 という文字列が含まれるものをQUERY関数版で検索する場合のURLはこんな感じです。
https://script.google.com/macros/s/***-***-***/exec?searchKey=極楽&finder=query

　
ブラウザでアクセスして動作確認したところ、TextFinderとQUERY関数のいずれも、以下のような結果が得られました。

{
    "data": [
        {
            "zip_code": 230074,
            "banchi": "水沢極楽"
        },
        {
            "zip_code": 2830835,
            "banchi": "極楽寺"
        },
        {
            "zip_code": 2480023,
            "banchi": "極楽寺"
        },
        {
            "zip_code": 9301453,
            "banchi": "原（極楽坂）"
        },
        {
            "zip_code": 9300451,
            "banchi": "極楽寺"
        },
        {
            "zip_code": 4093811,
            "banchi": "極楽寺"
        },
        {
            "zip_code": 5013763,
            "banchi": "極楽寺"
        },
        {
            "zip_code": 5010605,
            "banchi": "極楽寺"
        },
        {
            "zip_code": 4650053,
            "banchi": "極楽"
        },
        {
            "zip_code": 4910144,
            "banchi": "浅井町極楽寺"
        },
        {
            "zip_code": 5220231,
            "banchi": "極楽寺町"
        },
        {
            "zip_code": 6120886,
            "banchi": "深草極楽寺町"
        },
        {
            "zip_code": 6120813,
            "banchi": "深草極楽寺山町"
        },
        {
            "zip_code": 6120027,
            "banchi": "深草極楽町"
        },
        {
            "zip_code": 6148222,
            "banchi": "内里極楽橋"
        },
        {
            "zip_code": 5960832,
            "banchi": "極楽寺町"
        },
        {
            "zip_code": 6392337,
            "banchi": "極楽寺"
        },
        {
            "zip_code": 8290323,
            "banchi": "極楽寺"
        }
    ]
}

ソースコード

ソースコード全体は以下の通りです。

function doGet(e) {
  const params = e.parameter
  const keyword = params.searchKey
  const finder = params.finder

  const results = finder === 'query' ? findByQueryFunction(keyword) : findByTextFinder(keyword)

  const response = ContentService.createTextOutput()
  response.setMimeType(ContentService.MimeType.JSON)
  response.setContent(JSON.stringify({data: results}))

  return response
}

function findByQueryFunction(keyword) {
  // QUERY関数を使う
  // setValueに関数を入れられるのでそれを利用する
  // https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluevalue

  // `RESULT` シートに書き込み
  // WHEREの左辺で列を指定して検索できる
  const sheet = SpreadsheetApp.getActive().getSheetByName("RESULT")
  sheet.getRange(1, 1).setValue(`=QUERY(KEN_ALL!A:I,"WHERE I LIKE '%${keyword}%'")`)

  const results = []

  const lastRow = sheet.getLastRow()
  for (let i=2; i <= lastRow; i++) {
    const zip = sheet.getRange(i, 3).getValue()
    const banchi = sheet.getRange(i, 9).getValue()

    results.push({ zip, banchi })
  }

  return results
}

function findByTextFinder(keyword) {
  // TextFinderを使う
  // https://developers.google.com/apps-script/reference/spreadsheet/sheet#createtextfinderfindtext

  const sheet = SpreadsheetApp.getActive().getSheetByName("KEN_ALL")

  const textFinder = sheet.createTextFinder(keyword)
  const items = textFinder.findAll()

  return items.map(item => {
    return {
      'zip_code': sheet.getRange(item.getRow(), 3).getValue(),
      'banchi': sheet.getRange(item.getRow(), 9).getValue()
    }
  })
}