😎
DBからデータを定期的にGoogle SpreadSheetに転記する

2025/03/13に公開
Lambda
Node.js
GAS
tech
ビジネスサイドから

｢◯◯のデータをexcelでください｣と依頼されることはありませんか?

私はほぼ毎日です.
定期的なデータをいちいち

SQL叩いて→csvにして→Shift JISに変換して→Slackで共有する

ということに辟易していたときに思いついたのがこの方法でした

 やりたいことみんなが大好きなExcelで(ここでは表計算ソフトで提出されるデータ形式のことを指す)

情報を自動提供するためにGoogle SpreadSheetに

DBから引っ張ってきたデータを転記させます
GASからDBに接続するのはセキュリティリスクを除いても一癖ありそうだったので

下記のようにLambdaでサーバレスなAPIエンドポイントを作成し

それをGASから叩いて定期実行するスキーマにしました.
Lambdaを利用することでAPIを作成し,サーバーを用意してデプロイするというコストをまるまる省くことができますし,

実行時間による課金なのでお財布にもやさしく済みます.

 Lambdaの作成
 Lambdaの設定まずはAWSコンソールからLambda関数を作成します.
AWSコンソールにログインし,Lambdaサービスを選択
「関数の作成」をクリック
「一から作成」を選択
以下の設定を行います:
関数名: db-to-spreadsheet（任意の名前）
ランタイム: Node.js 20.x（最新の安定バージョン）
アーキテクチャ: x86_64またはarm64（ARM64の方がコスト効率が良い場合が多い）
実行ロール: 「基本的なLambdaアクセス権限で新しいロールを作成」

作成後,RDSへのアクセス権限を付与するために,IAMロールに適切なポリシーをアタッチします.

 レイヤーの作成Node.jsのLambda関数でデータベースに接続するために,必要なライブラリをレイヤーとして追加します.

これにより,関数本体のコードをシンプルに保つことができます.
レイヤーの作成方法は省略

 関数の作成次に,Lambda関数のコードを実装します.以下は,ユーザー情報を取得してJSON形式で返すシンプルな例です:
// PostgreSQLクライアントを使用
const postgres = require('postgres');

exports.handler = async (event) => {
  // 環境変数からDB接続文字列を取得
  const connectionString = process.env.DATABASE_URL;
  
  // PostgreSQLクライアントの初期化
  const sql = postgres(connectionString);
  
  try {
    // ユーザー情報を取得するシンプルなクエリ
    const users = await sql`
      SELECT id, name, email, created_at 
      FROM users 
      ORDER BY created_at DESC 
      LIMIT 100
    `;
    
    // レスポンス
    return {
      statusCode: 200,
      headers: {
        'Content-Type': 'application/json',
        'Access-Control-Allow-Origin': '*' // CORS対応
      },
      body: JSON.stringify({
        data: users
      })
    };
  } catch (error) {
    console.error('Error:', error);
    return {
      statusCode: 500,
      headers: {
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        error: error.message
      })
    };
  } finally {
    // 接続を終了
    await sql.end();
  }
};
Lambda関数の環境変数に以下を設定します:

DATABASE_URL: データベース接続文字列（例: postgresql://username:password@host:port/database）
セキュリティのベストプラクティス:
接続情報は必ず環境変数として設定し、コードに直接書き込まないようにしましょう
AWS Secrets Managerを使用して機密情報を管理することも検討してください

 API Gatewayの設定Lambda関数をAPIとして公開するために,API Gatewayを設定します:
Lambda関数の設定画面で「トリガーを追加」をクリック
「API Gateway」を選択
以下の設定を行います:
APIタイプ: REST API
セキュリティ: APIキー（本番環境では適切な認証を設定することをお勧めします）
APIが作成されたら,APIキーを設定:
API Gateway管理画面で「APIキー」を選択
「APIキーの作成」をクリック
名前を入力して作成
作成したAPIキーをメソッドリクエストの設定で要求するように設定

 GASの作成
 スプレッドシートの準備まず,データを格納するためのスプレッドシートを作成します:
Google Driveで新しいスプレッドシートを作成
必要に応じてヘッダー行を追加（例: 「ID」「名前」「メールアドレス」「作成日」など）

 Google Apps Scriptの作成次に,スプレッドシートに紐づくGoogle Apps Scriptを作成します:
スプレッドシートの「拡張機能」>「Apps Script」をクリック
以下のようなコードを入力:
// APIキーとエンドポイントの設定
function getApiKey() {
  const scriptProperties = PropertiesService.getScriptProperties();
  return scriptProperties.getProperty('API_KEY');
}

const API_ENDPOINT = 'https://xxxxxxxx.execute-api.region.amazonaws.com/stage/db-to-spreadsheet';

/**
 * Lambda APIからデータを取得してスプレッドシートに書き込む
 */
function updateSheet() {
  // APIリクエストを構築
  const options = {
    method: 'get',
    headers: {
      'x-api-key': getApiKey()
    },
    muteHttpExceptions: true
  };
  
  // APIリクエスト実行
  const response = UrlFetchApp.fetch(API_ENDPOINT, options);
  const data = JSON.parse(response.getContentText());
  
  // データをシートに挿入
  if (data.data && data.data.length > 0) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    
    // データを2次元配列に変換
    const values = data.data.map(row => Object.values(row));
    
    // 新しいデータを書き込み
    sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
  }
}

/**
 * APIキーを設定する関数
 */
function setApiKey() {
  const ui = SpreadsheetApp.getUi();
  const result = ui.prompt(
    'APIキーの設定',
    'APIキーを入力してください:',
    ui.ButtonSet.OK_CANCEL
  );
  
  if (result.getSelectedButton() == ui.Button.OK) {
    const scriptProperties = PropertiesService.getScriptProperties();
    scriptProperties.setProperty('API_KEY', result.getResponseText());
    ui.alert('APIキーが保存されました');
  }
}

 スプレッドシートにボタンを追加する方法スプレッドシート内に直接ボタンを配置して、クリックするだけでデータを更新できるようにする方法もあります。
スプレッドシートで「挿入」>「図形描画」を選択
好みの形のボタンを描画（例：角丸長方形）
ボタンにテキスト「データを更新」などを追加
図形を選択した状態で右クリックし、「スクリプトを割り当て」を選択
実行する関数名として updateSheet を入力して「OK」をクリック
これで、スプレッドシート上のボタンをクリックするだけでデータを更新できるようになります。

 スケジュール設定定期的にデータを更新するためのトリガーを設定します:
Apps Scriptエディタで「トリガー」アイコンをクリック（時計のアイコン）
「トリガーを追加」をクリック
以下の設定を行います:
実行する関数: updateSheet
イベントのソース: 時間主導型
時間ベースのトリガーのタイプ: 日タイマー（または必要な頻度）
時刻: 適切な時間を選択（例: 毎日午前9時）
「保存」をクリックしてトリガーを有効化

 セキュリティ上の注意点以下のセキュリティ上の注意点に留意してください:
API Gatewayの保護:
APIキー認証を必ず設定する
可能であればIAM認証やCognito認証も検討する
Lambda関数の権限制限:
データベースへの接続は読み取り専用アカウントを使用する
必要最小限の権限のみを付与する
データの取り扱い:
個人情報や機密情報を含むデータは適切に匿名化・マスキングする
スプレッドシートの共有設定を厳格に管理する
APIキーの管理:
APIキーはGASコード内にハードコーディングせず,PropertiesServiceを使用して保存する
定期的にAPIキーをローテーションする
https://developers.google.com/apps-script/guides/properties?hl=ja

データベース接続情報:
接続情報は環境変数として設定し、コードに直接書き込まない


 まとめこの記事では,AWSのLambdaとAPI Gateway,そしてGoogle Apps Scriptを組み合わせて,

データベースの情報を定期的にGoogle Spreadsheetに転記する方法を紹介しました.
この方法のメリットは以下のとおりです:
サーバーレスアーキテクチャによる低コスト運用
必要なときだけ実行されるため,リソース効率が良い
ビジネスサイドが使い慣れたスプレッドシートでデータを閲覧・加工できる
手動作業を自動化することによる工数削減
スプレッドシート上のボタンで簡単に最新データを取得可能
ちなみにAWS Lambda以外にもサーバーレスサービスの選択肢はあります

Cloudflare Workers:

Vercel Functions/Edge Functions:
こういう系のサービスはだいたい課金が実行時間に依存するので

より実行速度の早い言語を選択することでより費用削減が期待できます

https://tmokmss.hatenablog.com/entry/llrt_new_javascript_runtime
今回はAWSのエコシステムを活用するためLambdaを選択しましたが、要件に応じて最適なサービスを選ぶことをお勧めします。
GitHubで編集を提案
やりたいこと

Lambdaの作成

Lambdaの設定

レイヤーの作成

関数の作成

API Gatewayの設定

GASの作成

スプレッドシートの準備

Google Apps Scriptの作成

スプレッドシートにボタンを追加する方法

スケジュール設定

セキュリティ上の注意点

まとめ

Discussion
