株式会社iCAREのタケです。
これはiCARE Dev Advent Calendar 2023の4日目の記事です。

はじめに

AWS Lambdaを使用してMetabase（BIツール）のAPI経由で取得したデータをスプレッドシートに書き出す仕組みを構築しました。この記事では、その際に実施した作業と参考にした記事を列挙し、備忘録としてまとめています。

また、今回のようにLambdaからスプレッドシートにアクセスする場合は、認証情報としてサービスアカウントキーを用いる必要があるのですが、Workload Identity連携を用いた場合キーレスでアクセスできるらしく、そちらを試しました。

参考記事

やったこと

次の記事内の手順を参考に以下ステップで進めていきました。

サービスアカウントの作成
Google Sheets APIの有効化
スプレッドシートへのアクセス権限を付与
Workload Identify連携の設定と権限借用ファイルのダウンロード
Lambda関数の設定・コーディング

1. サービスアカウントの作成

次の記事を参考に「サービスアカウントの作成」まで行います。

サービスアカウントが作成できると以下のような状態になると思います。

2. Google Sheets APIの有効化

今回データをスプレッドシートに書き出す際に、「Google Sheets API」を使用します。
Google Cloudのナビゲーションメニューから「APIとサービス」→「有効なAPIとサービス」→「APIとサービスの有効化」と辿っていき、「Google Sheets API」を有効化します。

3. スプレッドシートへのアクセス権限を付与

アクセス権限を付与する方法として以下2つが考えられるかと思います。

対象のスプレッドシートを管理しているGoogleドライブのフォルダに対して1で作成したサービスアカウントのメールアドレスを追加する
対象のスプレッドシートの共有設定から1で作成したサービスアカウントのメールアドレスを追加する

ここは状況に応じて対応方法が異なると思います。（自分は1で対応しました）

4. Workload Identify連携の設定と権限借用ファイルのダウンロード

公式の記事を参考にWorkload Identityプール・プロバイダを作成、サービスアカウントの権限借用ファイルのダウンロードを行います。

下記記事はWorkload Identity連携 x GitHub Actionsの例ですが、Workload Identityプールの作成からサービスアカウントの権限借用の許可設定までわかりやすく説明しており、参考にさせていただきました。

そんなこんなでうまく設定できていれば、下図のように権限借用ファイルのダウンロードができるはずです。

5. Lambda関数の設定・コーディング

最後にLambda関数を作成し、環境構築からコーディング、デプロイを行います。以下に例を示します。デプロイは.zip ファイルアーカイブで行いました。
4でダウンロードした権限借用ファイルは clientLibraryConfig-aws-provider.json になります。
また、Lambdaのメモリサイズやタイムアウト値は必要に応じて調整してください。

ランタイム

Node.js 18.x

使用ライブラリ

axios 1.5.1
googleapis 128.0.0

ディレクトリ構成

script/
├── clientLibraryConfig-aws-provider.json
├── index.mjs
├── node_modules
├── package-lock.json
└── package.json

Lambdaの環境変数

環境変数としてGOOGLE_APPLICATION_CREDENTIALSとGOOGLE_CLOUD_PROJECTを追加してください。
GOOGLE_APPLICATION_CREDENTIALSには、ディレクトリ内の権限借用ファイルのパスを、GOOGLE_CLOUD_PROJECTには、GCPのプロジェクト名を指定してください。

Lambdaコーディング

MetabaseのホストやスプレッドシートIDなどの各種情報はパラメータストアに保存しています。

import { SSMClient, GetParametersCommand } from '@aws-sdk/client-ssm'
import axios from 'axios'
import { google } from 'googleapis'

const getSSMParameters = async(names) => {
  try {
    const client = new SSMClient({ region: 'ap-northeast-1' })
    const command = new GetParametersCommand({ Names: names, WithDecryption: true })
    const response = await client.send(command)

    return names.map(name => response?.Parameters?.find(parameter => parameter.Name === name)?.Value)
  } catch(error) {
    throw new Error(`SSMパラメータ取得でエラーが発生しました: ${error.message}`)
  }
}

const setupGoogleSheetsClient = () => {
  const auth = new google.auth.GoogleAuth({
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
  })

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

  return sheetsClient
}

const establishMetabaseSession = async (host, account) => {
  try {
    const session = await axios.post(`${host}/api/session`, {
      username: account['email'],
      password: account['password'],
    })

    return session.data.id
  } catch (error) {
    throw new Error(`認証に失敗しました: ${error.message}`)
  }
}

const fetchMetabaseQueryResult = async (cardId, host, sessionId) => {
  const headers = {
    'Content-Type': 'application/json',
    'X-Metabase-Session': sessionId,
  }

  try {
    const queryResult = await axios.post(`${host}/api/card/${cardId}/query/csv`, {}, { headers: headers })

    return queryResult.data
  } catch (error) {
    throw new Error(`カードID:${cardId}のクエリ結果取得でエラーが発生しました: ${error.message}`)
  }
}

const clearSpreadsheetData = async (spreadsheetId, sheetName, sheetsClient) => {
  try {
    await sheetsClient.spreadsheets.values.clear({
      spreadsheetId: spreadsheetId,
      range: sheetName,
    })
  } catch (error) {
    throw new Error(`シート名:${sheetName}のデータクリアでエラーが発生しました: ${error.message}`)
  }
}

const writeMetabaseQueryResultTo = async (spreadsheetId, sheetName, queryResult, sheetsClient) => {
  try {
    await sheetsClient.spreadsheets.values.update({
      spreadsheetId: spreadsheetId,
      range: sheetName,
      valueInputOption: 'USER_ENTERED',
      resource: { values: queryResult },
    })
  } catch (error) {
    throw new Error(`シート名:${sheetName}へのクエリ結果書き込みでエラーが発生しました: ${error.message}`)
  }
}

const parseCsv = (csvData) => {
  const rows = csvData.split('\n')
  const result = []

  for (const row of rows) {
    const cells = row.split(',')

    result.push(cells)
  }

  return result
}

export const handler = async(event, context) => {
  try {
    const [metabaseHost, metabaseAccount, nsmSpreadsheetId, mappingList] = await getSSMParameters([
      'METABASE_HOST',
      'METABASE_AUTHENTICATION_ACCOUNT',
      'NSM_SPREADSHEET_ID',
      'MAPPING_LIST',
    ])
    const parsedMetabaseAccount = JSON.parse(metabaseAccount)
    const parsedMappingList = JSON.parse(mappingList)

    const sheetsClient = setupGoogleSheetsClient()
    const metabaseSessionId = await establishMetabaseSession(metabaseHost, parsedMetabaseAccount)

    for (const mappingItem of parsedMappingList) {
      const queryResult = await fetchMetabaseQueryResult(mappingItem['metabaseCardId'], metabaseHost, metabaseSessionId)
      await clearSpreadsheetData(nsmSpreadsheetId, mappingItem['sheetName'], sheetsClient)
      await writeMetabaseQueryResultTo(nsmSpreadsheetId, mappingItem['sheetName'], parseCsv(queryResult), sheetsClient)
    }
    console.log('正常終了')
  } catch (error) {
    console.error(error.message)
  }
}

デプロイ方式

.zip ファイルアーカイブで Lambda 関数をデプロイしました。詳しくは下記を参照ください。

最後に

最後までご覧いただきありがとうございました。
実行結果は、個人情報などを含むためお見せできませんが、意図通りに動いてくれました。
どなたかの参考になれば幸いです。