Google Spread Sheet API v4を使ってToDoのCRUD操作を行ったメモ

概要

前回では、Spread Sheetを操作できる環境を作った。
次は、CRUD操作を試してみる。

基本的にはapi v4 リファレンスとsamplesの通り。
SQLでいうWHEREをうまくやる方法が見つからなかったので、行番号を出力して使用するようにした。
絞り込みはフィルターを作成して、取得すればできそうな気もしますが面倒そうで、スプレッドシート側でやることに。
簡単に条件を付ける方法ご存じの方いらっしゃれば教えてください。

ソース
スプレッドシート

スプレッドシート

基本の情報シート

A1セルに=ARRAYFORMULA(if(B:B="","",row(B:B)-1))を入れて、行番号が出力されるようにしている。

絞り込みシート

A1セルに=query(Todo!A:D,"select * where D <> TRUE", 1)を入れて、絞り込んだ結果を表示するようにしている。
プログラムからは、このシートを参照するだけ。

プログラム

認証

const private_key = process.env.SERVICE_ACCOUNT_PRIVATE_KEY.replace('\\\\','\\',)
async function getAuth() {
  const auth = await google.auth.getClient({
    credentials: {
      client_email: process.env.SERVICE_ACCOUNT_CLIENT_EMAIL,
      private_key,
    },
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
  })
  return auth
}

CREATE

  create: async (todo) => {
    const auth = await getAuth()
    const req = {
      auth,
      spreadsheetId: spreadsheetId,
      range: 'A1',
      valueInputOption: 'USER_ENTERED',
      insertDataOption: 'INSERT_ROWS',
      resource: {
        values: [['', ...values(todo)]],
      },
    }
    await sheets.spreadsheets.values.append(req)
  },

READ

• 全件取得

async function fetchAll(spreadsheetId: string, range: string) {
  const baseSheetName = 'Todo'
  const auth = await getAuth()
  const apiOptions = {
    auth,
    spreadsheetId,
    range: `${baseSheetName}!${range}`,
  }
  const res = await sheets.spreadsheets.values.get(apiOptions)
  if (!res.data || !res.data.values) return []
  return res.data.values
}

• 絞り込み（そんなに行が多くなる想定がないのでとりあえず100件で十分としている）

async function fetchCompleted(isCompleted) {
  const completedSheetName = 'TodoCompleted'
  const uncompletedSheetName = 'TodoUncompleted'
  const sheetName = isCompleted ? completedSheetName : uncompletedSheetName

  const auth = await getAuth()
  const apiOptions = {
    auth,
    spreadsheetId,
    range: `${sheetName}!A2D100`,
  }
  const res = await sheets.spreadsheets.values.get(apiOptions)
  if (!res.data.values) return []
  // console.log('fetchres', res.data)
  return res.data.values
}

• 指定行を1行取得

async function fetch(rowNumber): Promise<null | string[]> {
  const auth = await getAuth()
  const request = {
    spreadsheetId,
    resource: {
      dataFilters: [
        {
          gridRange: {
            sheetId: baseSheetId,
            startRowIndex: rowNumber,
            endRowIndex: rowNumber + 1,
          },
        },
      ],
    },
    auth,
  }
  try {
    const response = (await sheets.spreadsheets.values.batchGetByDataFilter(request)).data
    if (!response.valueRanges || response.valueRanges.length === 0) return null
    return response.valueRanges[0].valueRange.values[0]
  } catch (err) {
    console.error(err)
    return null
  }
}

UPDATE

  update: async (rowNumber, update) => {
    const auth = await getAuth()
    const arr = await fetch(rowNumber)
    if (!arr) return null
    const todo = convertSheetArrayToToDo(arr)
    const updatedTodo = { ...todo, ...update }
    const [_, ...updateValues] = values(updatedTodo)

    const request = {
      spreadsheetId,
      resource: {
        valueInputOption: 'USER_ENTERED',
        data: [
          {
            dataFilter: {
              gridRange: {
                sheetId: 0,
                startRowIndex: rowNumber,
                endRowIndex: rowNumber + 1,
                startColumnIndex: 1,
                endColumnIndex: 4,
              },
            },
            values: [updateValues],
            majorDimension: 'ROWS',
          },
        ],
      },
      auth,
    }
    try {
      const response = (await sheets.spreadsheets.values.batchUpdateByDataFilter(request)).data
      if (response.totalUpdatedRows !== 1) return null
    } catch (err) {
      console.error(err)
    }
    return updatedTodo
  }

DELETE

• 指定行を削除

async function remove(rowNumber: number) {
  const auth = await getAuth()
  const req = {
    auth,
    spreadsheetId: spreadsheetId,
    resource: {
      requests: [
        {
          deleteDimension: {
            range: {
              sheetId: baseSheetId,
              dimension: 'ROWS',
              startIndex: rowNumber,
              endIndex: rowNumber + 1,
            },
          },
        },
      ],
    },
  }
  await sheets.spreadsheets.batchUpdate(req)
  return start
}

型定義

export type ID = string
export interface Todo {
  rowNumber: number
  id: ID
  title: string
  completed: boolean
}

export type OptionalKeys<T> = { [K in keyof T]?: T[K] | null }
export interface DataStorage<T extends { rowNumber: number }> {
  fetchAll: () => Promise<T[]>
  fetchByCompleted: (completed: boolean) => Promise<T[]>
  create: (todo: Omit<T, 'rowNumber'>) => Promise<void>
  update: (rowNumber: number, update: OptionalKeys<T>) => Promise<T | null>
  remove: (rowNumber: number) => Promise<number | null>
}

参考

Googleスプレッドシートをプログラムから操作
NOWでデプロイできるGoogleフォームみたいに追記できるAPIを作ったので、GitHubに公開してみた