Chapter 04

GASでAPIを作ってみる

2021.01.01に更新

このチャプターの目次

シート準備
家計簿のテンプレートをつくる
データを追加する onPost をつくる
データ取得する onGet をつくる
データ削除する onDelete をつくる
データ更新する onPut をつくる
リクエストを受け取れるようにする
API を叩いてみる
API が叩かれたときのログを出力する

次は API の作成です！！

本来 GAS で受け付けることのできるリクエストは GET と POST の2種類だけですが、文字列でメソッドを指定することで擬似的に GET で取得、POST で追加、PUT で更新、DELETE で削除できる REST API もどきを作成します。

シート準備

まずはじめにシートの準備をします。
Google スプレッドシートで新しいシートを作成して、「ツール」タブ→「スクリプトエディター」をクリックします。

このようなエディターが表示されます。
「無題のプロジェクト」をクリックして「家計簿API」にタイトルの変更と、コード.gs を api.gs にリネームします。

api.gs の内容を書き換えます。

api.gs

const ss = SpreadsheetApp.getActive()

function test () {
  console.log(ss.getName())
}

メニューで「test」が選択されていることを確認してから
「実行」ボタンをクリックします。

「承認が必要です」というダイアログが表示されるので、
「権限を確認」ボタンをクリックしたあと、スプレッドシートを作成したアカウントでログインして「許可」ボタンをクリックします。

画面下の実行ログ欄に作成したシートの名前が表示されればOKです。

家計簿のテンプレートをつくる

まずはじめに、家計簿のテンプレートとなるシートを作成する関数 insertTemplate を作ります。
シートのイメージを大雑把にまとめると

A1:B4 に収支確認エリア

A6:H6 にテーブルのヘッダー

J1:L1 にカテゴリ別支出のヘッダー

です。これをプログラムに落とし込みます。

api.gs

const ss = SpreadsheetApp.getActive()

function test () {
  insertTemplate('2020-06')
}

/**
 * 指定年月のテンプレートシートを作成します
 * @param {String} yearMonth
 * @returns {Sheet} sheet
 */
function insertTemplate (yearMonth) {
  const { SOLID_MEDIUM, DOUBLE } = SpreadsheetApp.BorderStyle

  const sheet = ss.insertSheet(yearMonth, 0)
  const [year, month] = yearMonth.split('-')

  // 収支確認エリア
  sheet.getRange('A1:B1')
    .merge()
    .setValue(`${year}年 ${parseInt(month)}月`)
    .setFontWeight('bold')
    .setHorizontalAlignment('center')
    .setBorder(null, null, true, null, null, null, 'black', SOLID_MEDIUM)

  sheet.getRange('A2:A4')
    .setValues([['収入：'], ['支出：'], ['収支差：']])
    .setFontWeight('bold')
    .setHorizontalAlignment('right')

  sheet.getRange('B2:B4')
    .setFormulas([['=SUM(F7:F)'], ['=SUM(G7:G)'], ['=B2-B3']])
    .setNumberFormat('#,##0')

  sheet.getRange('A4:B4')
    .setBorder(true, null, null, null, null, null, 'black', DOUBLE)

  // テーブルヘッダー
  sheet.getRange('A6:H6')
    .setValues([['id', '日付', 'タイトル', 'カテゴリ', 'タグ', '収入', '支出', 'メモ']])
    .setFontWeight('bold')
    .setBorder(null, null, true, null, null, null, 'black', SOLID_MEDIUM)

  sheet.getRange('F7:G')
    .setNumberFormat('#,##0')

  // カテゴリ別支出
  sheet.getRange('J1')
    .setFormula('=QUERY(B7:H, "select D, sum(G), sum(G) / "&B3&"  where G > 0 group by D order by sum(G) desc label D \'カテゴリ\', sum(G) \'支出\'")')

  sheet.getRange('J1:L1')
    .setFontWeight('bold')
    .setBorder(null, null, true, null, null, null, 'black', SOLID_MEDIUM)

  sheet.getRange('L1')
    .setFontColor('white')

  sheet.getRange('K2:K')
    .setNumberFormat('#,##0')

  sheet.getRange('L2:L')
    .setNumberFormat('0.0%')

  sheet.setColumnWidth(9, 21)

  return sheet
}

スプレッドシートは SpreadsheetApp を利用して取得します。
取得の方法は2つあります。

スプレッドシートIDを指定する openById(id)
紐付いているスプレッドシートを取得する getActive()

今回はスプレッドシートと紐付いている GAS プロジェクトを作成したので、後者で取得します。

const ss = SpreadsheetApp.getActive()

新規シートを作成するときには insertSheet メソッドを使います。
引数にシート名とインデックスを指定します。インデックスは 0 で一番左に追加されます。
返り値は新規作成したシートです。

const sheet = ss.insertSheet('シート名', インデックス)

セル操作の流れは、範囲（Range）を取得してから各操作を実行します。
シートの getRange メソッドで範囲を取得できます。
A1 形式のほうが（個人的に）見やすいので、今回のプログラムではこちらに統一します。

/** 単一のセルを取得する */
// getRange(行, 列)
sheet.getRange(1, 2) // B1
// getRange(A1形式)
sheet.getRange('B1') // B1

/** 複数のセルを取得する */
// getRange(開始行, 開始列, 何行分選択するか, 何列分選択するか)
sheet.getRange(1, 2, 3, 4) // B1:E3
// getRange(A1形式)
sheet.getRange('B1:E3')    // B1:E3

各セル操作は Range を返すので、メソッドチェーンを利用できます。
可能な操作はすべて公式リファレンスに記載されているので、こちらも確認してみてください。

// メソッドチェーン
sheet.getRange('A1')
  .func1() // どの操作も
  .func2() // A1に対して
  .func3() // 実行される

セル操作については重要な setValue, setValues メソッドを説明します。
単一セルの値をセットするときは setValue 、
複数セルの値をセットするときは setValues を使います。

setValues では必ず2次元配列を渡します。改行してみると分かりやすいです。

// A1に"A1 value"をセット
sheet.getRange('A1')
  .setValue('A1 value')

// 複数セルの値をセットするときは
// 2次元配列を渡します
sheet.getRange('A1:B2')
  .setValues([
    ['A1', 'B1'],
    ['A2', 'B2']
  ])

// 1行(1列)だけでも2次元配列を渡します
sheet.getRange('A6:H6')
  .setValues([
    ['id', '日付', 'タイトル', 'カテゴリ', 'タグ', '収入', '支出', 'メモ']
  ])

また、= から始まる数式をセットしたい場合は、
setFormula, setFormulas メソッドを使います。

sheet.getRange('A1')
  .setFormula('=PI()')

sheet.getRange('B2:B4')
  .setFormulas([
    ['=SUM(F7:F)'],
    ['=SUM(G7:G)'],
    ['=B2-B3']
  ])

この状態で test を実行してみます。
2020-06 というシートが新しく作成され、テンプレートが書き込まれることを確認してください！

データを追加する onPost をつくる

それでは API のプログラム作成に入ります！
API は成功時には何かしらの結果を返し、エラー時には { error: 'メッセージ' } を返す仕様にします。

まずはデータの追加です。onPost と、
一応入力データのバリデーションを行う isValid を作成します。

api.gs

const ss = SpreadsheetApp.getActive()

function test () {
  onPost({
    item: {
      date: '2020-07-01',
      title: '支出サンプル',
      category: '食費',
      tags: 'タグ1,タグ2',
      income: null,
      outgo: 3000,
      memo: 'メモメモ'  
    }
  })
}

/** --- API --- */

/**
 * データを追加します
 * @param {Object} params
 * @param {Object} params.item 家計簿データ
 * @returns {Object} 追加した家計簿データ
 */
function onPost ({ item }) {
  if (!isValid(item)) {
    return {
      error: '正しい形式で入力してください'
    }
  }
  const { date, title, category, tags, income, outgo, memo } = item
  
  const yearMonth = date.slice(0, 7)
  const sheet = ss.getSheetByName(yearMonth) || insertTemplate(yearMonth)

  const id = Utilities.getUuid().slice(0, 8)
  const row = ["'" + id, "'" + date, "'" + title, "'" + category, "'" + tags, income, outgo, "'" + memo]
  sheet.appendRow(row)

  return { id, date, title, category, tags, income, outgo, memo }
}

/** --- common --- */

/**
 * 指定年月のテンプレートシートを作成します
 * @param {String} yearMonth
 * @returns {Sheet} sheet
 */
function insertTemplate (yearMonth) {
  /** ～ 省略 ～ */
}

/**
 * データが正しい形式か検証します
 * @param {Object} item
 * @returns {Boolean} isValid
 */
function isValid (item = {}) {
  const strKeys = ['date', 'title', 'category', 'tags', 'memo']
  const keys = [...strKeys, 'income', 'outgo']

  // すべてのキーが存在するか
  for (const key of keys) {
    if (item[key] === undefined) return false
  }

  // 収支以外が文字列であるか
  for (const key of strKeys) {
    if (typeof item[key] !== 'string') return false
  }

  // 日付が正しい形式であるか
  const dateReg = /^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$/
  if (!dateReg.test(item.date)) return false

  // 収支のどちらかが入力されているか
  const { income: i, outgo: o } = item
  if ((i === null && o === null) || (i !== null && o !== null)) return false

  // 入力された収支が数字であるか
  if (i !== null && typeof i !== 'number') return false
  if (o !== null && typeof o !== 'number') return false

  return true
}

シートの取得は getSheetByName でシート名を指定して取得します。
シートがなかった場合は null が返ってくるので、insertTemplate が実行されます。

// 指定年月シートを取得する、なかったらテンプレートシートを作成する
const sheet = ss.getSheetByName(yearMonth) || insertTemplate(yearMonth)

また、シートには appendRow というシンプルで便利なメソッドが用意されているので、
引数に配列を渡すだけで簡単にデータの追加をできます。

収支以外は文字列として扱ってほしいので、値の前にシングルクォートを付与してからシートに追加します。
値をセットするとき、文字列を渡しても数字や日付などは自動で変換されるので注意が必要です。

const a1 = sheet.getRange('A1').setValue("100").getValue()
const b1 = sheet.getRange('B1').setValue("'100").getValue()

console.log(a1)        // -> 100
console.log(typeof a1) // -> "number"
console.log(b1)        // -> "100"
console.log(typeof b1) // -> "string"

ID は Utilities の getUuid を利用して UUID の先頭8文字だけ切り取るという謎のプログラムで生成しています。
公式リファレンスで使える便利メソッドが記載されているので、ぜひ確認してみてください。

const id = Utilities.getUuid().slice(0, 8)

この状態で test を実行してみます。
シートが新しく作成され、データの追加を確認してください！

データ取得する onGet をつくる

追加ができたら、次は取得してみたいですね。onGet を作ります。

api.gs

const ss = SpreadsheetApp.getActive()

function test () {
  const result = onGet({ yearMonth: '2020-07' })
  console.log(result)
}

/** --- API --- */

/**
 * 指定年月のデータ一覧を取得します
 * @param {Object} params
 * @param {String} params.yearMonth 年月
 * @returns {Object[]} 家計簿データ
 */
function onGet ({ yearMonth }) {
  const ymReg = /^[0-9]{4}-(0[1-9]|1[0-2])$/
  
  if (!ymReg.test(yearMonth)) {
    return {
      error: '正しい形式で入力してください'
    }
  }
  
  const sheet = ss.getSheetByName(yearMonth)
  const lastRow = sheet ? sheet.getLastRow() : 0

  if (lastRow < 7) {
    return []
  }

  const list = sheet.getRange('A7:H' + lastRow).getValues().map(row => {
    const [id, date, title, category, tags, income, outgo, memo] = row
    return {
      id,
      date,
      title,
      category,
      tags,
      income: (income === '') ? null : income,
      outgo: (outgo === '') ? null : outgo,
      memo
    }
  })

  return list
}

/** ～ 省略 ～ */

テーブルのヘッダーが A6:H6 にあるので、A7:H{最終行} のデータを取得します。

シートの最終行は getLastRow で取得できます。
指定年月のシートが存在しない場合も考慮して、最終行が7未満の場合は空の配列を返します。

const sheet = ss.getSheetByName(yearMonth)
const lastRow = sheet ? sheet.getLastRow() : 0

if (lastRow < 7) {
  return []
}

データを返すときはオブジェクトにして返したいので、
getValues で受け取った2次元配列を map でオブジェクトに加工します。

空白セルは空文字（''）として取得されるので、収支だけ注意が必要です。

const values = [
  ['xxx', '2020-07-01', 'sample1'],
  ['yyy', '2020-07-02', 'sample2']
]

const list = values.map(row => {
  return {
    id: row[0],
    date: row[1],
    title: row[2]
  }
})

console.log(list)
// -> [
//      { id: "xxx", date: "2020-07-01", title: "sample1" },
//      { id: "yyy", date: "2020-07-02", title: "sample2" }
//    ]

この状態で test を実行してみます。
追加したデータがオブジェクトの配列で返ってくることを確認してください！

データ削除する onDelete をつくる

機能はあと2つです！ onDelete を作ります。

api.gs

const ss = SpreadsheetApp.getActive()

function test () {
  const result = onDelete({ yearMonth: '2020-07', id: 'xxxxxxxx' })
  console.log(result)
}

/** --- API --- */

function onGet ({ yearMonth }) {
  /** ～ 省略 ～ */
}

function onPost ({ item }) {
  /** ～ 省略 ～ */
}

/**
 * 指定年月&idのデータを削除します
 * @param {Object} params
 * @param {String} params.yearMonth 年月
 * @param {String} params.id id
 * @returns {Object} メッセージ
 */
function onDelete ({ yearMonth, id }) {
  const ymReg = /^[0-9]{4}-(0[1-9]|1[0-2])$/
  const sheet = ss.getSheetByName(yearMonth)

  if (!ymReg.test(yearMonth) || sheet === null) {
    return {
      error: '指定のシートは存在しません'
    }
  }

  const lastRow = sheet.getLastRow()
  const index = sheet.getRange('A7:A' + lastRow).getValues().flat().findIndex(v => v === id)

  if (index === -1) {
    return {
      error: '指定のデータは存在しません'
    }
  }

  sheet.deleteRow(index + 7)
  return {
    message: '削除完了しました'
  }
}

/** ～ 省略 ～ */

内容はシンプルです。指定年月&id のデータが存在したら deleteRow で行を削除するだけです。
A7:A{最終行} で範囲の値を取得すると、2次元配列になっているのでフラットにしてから id を探します。

const values = [['xxx'], ['yyy'], ['zzz']]
const flatted = values.flat()
console.log(flatted) // -> ['xxx', 'yyy', 'zzz']
console.log(flatted.findIndex(v => v === 'yyy')) // -> 1

インデックスが見つかれば、インデックスに7行分足した行を削除するだけです。

sheet.deleteRow(index + 7)

この状態で test の指定年月&id を書き換えて実行してみます。
指定のデータが削除され、「削除完了しました」というメッセージをログで確認してください！

データ更新する onPut をつくる

最後の機能です！ onPut を作ります。

api.gs

const ss = SpreadsheetApp.getActive()

function test () {
  onPut({
    beforeYM: '2020-07',
    item: {
      id: 'xxxxxxxx',
      date: '2020-07-31',
      title: '更新サンプル',
      category: '食費',
      tags: 'タグ1,タグ2',
      income: null,
      outgo: 5000,
      memo: '更新したよ'  
    }
  })
}

/** --- API --- */

function onGet ({ yearMonth }) {
  /** ～ 省略 ～ */
}

function onPost ({ item }) {
  /** ～ 省略 ～ */
}

function onDelete ({ yearMonth, id }) {
  /** ～ 省略 ～ */
}

/**
 * 指定データを更新します
 * @param {Object} params
 * @param {String} params.beforeYM 更新前の年月
 * @param {Object} params.item 家計簿データ
 * @returns {Object} 更新後の家計簿データ
 */
function onPut ({ beforeYM, item }) {
  const ymReg = /^[0-9]{4}-(0[1-9]|1[0-2])$/
  if (!ymReg.test(beforeYM) || !isValid(item)) {
    return {
      error: '正しい形式で入力してください'
    }
  }

  // 更新前と後で年月が違う場合、データ削除と追加を実行
  const yearMonth = item.date.slice(0, 7)
  if (beforeYM !== yearMonth) {
    onDelete({ yearMonth: beforeYM, id: item.id })
    return onPost({ item })
  }

  const sheet = ss.getSheetByName(yearMonth)
  if (sheet === null) {
    return {
      error: '指定のシートは存在しません'
    }
  }

  const id = item.id
  const lastRow = sheet.getLastRow()
  const index = sheet.getRange('A7:A' + lastRow).getValues().flat().findIndex(v => v === id)

  if (index === -1) {
    return {
      error: '指定のデータは存在しません'
    }
  }

  const row = index + 7
  const { date, title, category, tags, income, outgo, memo } = item

  const values = [["'" + date, "'" + title, "'" + category, "'" + tags, income, outgo, "'" + memo]]
  sheet.getRange(`B${row}:H${row}`).setValues(values)

  return { id, date, title, category, tags, income, outgo, memo }
}

/** ～ 省略 ～ */

編集だけ「更新前と後で年月が違う場合」を考慮しないといけません。
削除と追加の処理は onDelete と onPost に任せます。

// 更新前と後で年月が違う場合、データ削除と追加を実行
const yearMonth = item.date.slice(0, 7)
if (beforeYM !== yearMonth) {
  onDelete({ yearMonth: beforeYM, id: item.id })
  return onPost({ item })
}

同じシートで完結できる場合は id 列以外の B?:H? を setValues で更新します。
編集する行はデータ削除の時と同じように探します。

const values = [["'" + date, "'" + title, "'" + category, "'" + tags, income, outgo, "'" + memo]]
sheet.getRange(`B${row}:H${row}`).setValues(values)

この状態で test の編集前年月と item の id を書き換えて実行してみます。
id 列以外のデータが更新されることを確認してください！

リクエストを受け取れるようにする

機能がすべて揃ったので、GAS 側でリクエストを受け取れるようにします。
GAS では doGet, doPost という関数を作ると、GET, POST を受け取ることができます。

この画像2回目の登場になりますが、doPost で受け取り、
onGet, onPost, onPut, onDelete に振り分ける処理を追加します。

api.gs

const ss = SpreadsheetApp.getActive()
const authToken = PropertiesService.getScriptProperties().getProperty('authToken') || ''

/**
 * レスポンスを作成して返します
 * @param {*} content
 * @returns {TextOutput}
 */
function response (content) {
  const res = ContentService.createTextOutput()
  res.setMimeType(ContentService.MimeType.JSON)
  res.setContent(JSON.stringify(content))
  return res
}

/**
 * アプリにPOSTリクエストが送信されたとき実行されます
 * @param {Event} e
 * @returns {TextOutput}
 */
function doPost (e) {
  let contents
  try {
    contents = JSON.parse(e.postData.contents)
  } catch (e) {
    return response({ error: 'JSONの形式が正しくありません' })
  }

  if (contents.authToken !== authToken) {
    return response({ error: '認証に失敗しました' })
  }

  const { method = '', params = {} } = contents

  let result
  try {
    switch (method) {
      case 'POST':
        result = onPost(params)
        break
      case 'GET':
        result = onGet(params)
        break
      case 'PUT':
        result = onPut(params)
        break
      case 'DELETE':
        result = onDelete(params)
        break
      default:
        result = { error: 'methodを指定してください' }
    }
  } catch (e) {
    result = { error: e }
  }

  return response(result)
}

/** --- API --- */

/** ～ 省略 ～ */

GAS でレスポンスを返すときは ContentService を利用します。
作成した API では JSON しか返さないので mime type には MimeType.JSON を指定します。

function response (content) {
  const res = ContentService.createTextOutput()
  // レスポンスの Content-Type ヘッダーに "application/json" を設定する
  res.setMimeType(ContentService.MimeType.JSON)
  // オブジェクトを文字列にしてからレスポンスに詰め込む
  res.setContent(JSON.stringify(content))
  return res
}

次に doPost の中をみていきます。
送られたリクエストは e.postData.contents で取得できます。
文字列なので JSON にパースします。一応 try catch で囲んでおきます。

let contents
try {
  contents = JSON.parse(e.postData.contents)
} catch (e) {
  return response({ error: 'JSONの形式が正しくありません' })
}

受け取るリクエストの内容はこのような形式としてます。

{
  method: 'GET or POST or PUT or DELETE',
  authToken: '認証情報',
  params: {
    // 任意の処理の引数となるデータ
  }
}

誰でもアクセス可能な URL を発行するので、認証情報 authToken を持っている人しかアクセスできないようにします。
認証情報はソースコードに書きたくないので、PropertiesService を利用してスクリプトのプロパティから取得します。

スクリプトのプロパティは以下のプログラムを一度実行して設定します。
トークンには UUID などを設定することをオススメします。

function test () {
  PropertiesService.getScriptProperties().setProperties({
    authToken: 'xxxxx'
  })
}

// プロパティの取得方法例
const authToken = PropertiesService.getScriptProperties().getProperty('authToken') || ''

処理はシンプルに case 文で分けます。
実行中にエラー起きても大丈夫なように、一応 try catch で囲んでおきます。

let result
try {
  switch (method) {
    case 'POST':
      result = onPost(params)
      break
    case 'GET':
      result = onGet(params)
      break
    case 'PUT':
      result = onPut(params)
      break
    case 'DELETE':
      result = onDelete(params)
      break
    default:
      result = { error: 'methodを指定してください' }
  }
} catch (e) {
  result = { error: e }
}

最後に実行結果をレスポンスとして返します。

return response(result)

ついに API 完成です！！ ✨ ✨

API を叩いてみる

API URL を発行します。

画面右上の「デプロイ」ボタンから「新しいデプロイ」をクリックします。

「種類の選択」から「ウェブアプリ」をクリックします

「次のユーザーとして実行」は「自分」、
「アクセスできるユーザー」は「全員」に設定してから
「デプロイ」ボタンをクリックします。

「デプロイを更新しました」という内容が表示されれば、準備完了です。
ウェブアプリの URL をコピーしておきます。
※実際の URL はもっと長いです。

curl などを使ってこの API を叩いてみます。
authToken や yearMonth の値は置き換えてください。

> curl -L -d "{\"method\":\"GET\",\"authToken\":\"\",\"params\":{\"yearMonth\":\"2020-07\"}}" https://script.google.com/macros/s/xxxxx/exec

[{"id":"5e30de41","date":"2020-07-31","title":"サンプル","category":"食費","tags":"タグ1,タグ2","income":null,"outgo":5000,"memo":"メモメモ"}]

データが正常に返ってくればOKです！

「GASでAPIを作ってみる」は以上になります。
お疲れ様でした！ 🎉 🍻

現時点のソースコード一覧はこちらから確認できます！

API が叩かれたときのログを出力する

こちらを進めるのは任意になりますが、
API の実行ログを確認できるようにする方法を紹介します。

クリックで展開

画面左にあるアイコン「実行数」から過去のログを確認することもできます。

以下の test を1度実行してからダッシュボードで確認すると、

function test () {
  console.log('log')
  console.info('info')
  console.warn('warn')
  console.error('error')
}

このようにログが表示されます。

しかし doPost を匿名で実行した場合は詳細が表示できないようです。
🔽 マークが表示されず、バージョンやステータスしか確認できません。

なので、今回はシートにログを記録したいと思います。

log シートを作り、A1:C1 に「日付」「レベル」「メッセージ」を記入します。

api.gs に log 関数を追加します。

一応ログは最大100件まで保存するようにしました。
logMaxRow を書き換えれば最大保存件数を変更できます。

api.gs

/** ～ 省略 ～ */

const logMaxRow = 101
const logSheet = ss.getSheetByName('log')

/**
 * ログをシートに記録します
 * @param {String} level 
 * @param {String} message 
 */
function log (level, message) {
  logSheet.appendRow([new Date(), level.toUpperCase(), message])

  if (logMaxRow < logSheet.getLastRow()) {
    logSheet.deleteRow(2)
  }
}

この状態で test の内容を書き換えて実行してみます。

function test () {
  log('info', 'info メッセージ')
  log('warn', 'warn メッセージ')
  log('error', 'error メッセージ')
}

log シートがこのように書き換わります。

あとは、API の好きな部分で log を実行するだけです！

シートに条件付き書式などを設定して、見やすくすると良さそうです。
スプレッドシートの「フィルター表示」機能を使うとフィルタリングもできます！

ログを記録するサンプルコードはこちらから確認できます！