スプレッドシートとAWSでコストかからない業務システムを作る設計TIPS

この記事は COUNTERWORKS Advent Calendar 2023 の 17 日目の記事です。

はじめまして @shimma です。本業はD2C企業のCTOとして働く傍ら、業務支援として複数社、インフラを中心に直接手を動かして、社内で横展開できるような設計・コードベースをご提供しています。

• 枯れた技術で
• コード行数少なく
• 運用コストかからず
• 8-9割くらいのことを解決できる

こちらが私の設計がポリシーです。

世の中9割はスプレッドシートで解決できる

私達の想像以上に、世の中の困りごとの大半はスプレッドシートやエクセルで解決ができます。エンジニアに依頼しなくても直接ロジック変更できるなど、組織リソースの有効化としてもメリットあります。

一方、複雑な数式やマクロにすべてを寄せ切り、ロジックを育てていくと、メンテナンスが困難を極めていきます。この記事を読んで頂いている技術者の方々であれば

• 複雑な箇所はコードによせて
• 変更しやすい所はスプレッドシート／Google App Script

という境界線を持つシステムを作り、運用もメンテナンスもしやすい仕組みを構築可能です。あえてエンジニアしかさわれないロジック領域を作ることで、コードの保護も出来ます。

今回は設計TIPSとして、具体的なユースケースを一部ご紹介します。

ユースケース1 内部データベースの情報を何らかスプレッドシートで処理したい

内部データベースとの接続方法ですが、BIツールと IMPORTDATA 関数の連携が楽です。

Redash Query API + IMPORTDATA

Redashをつなぐことで、Query API という方式を使って、特定のSQLの結果のみ有効な形式で外部公開ができます。このデータのURLを直接スプレッドシートの IMPORTDATA を使って、取り込めます。

=IMPORTDATA("https://${REDASH_HOST}/api/queries/${QUERY_ID}/results.csv?api_key={QUERY_API_KEY}")

一度スプレッドシートに取り込めれば、あとは表計算、Google App Script で処理OKです。

その他、Redash の Query API に関しては、Cloudflare Workers と組み合わせれば、独自ドメイン配下で簡単なデータフィード構築を構築するなど、利用の幅は広いです。

ユースケース2 スプレッドシートからIP制限がかかった外部リソースへのアクセス

結論、 GAS + AWS Lambda と組み合わせると簡単に可能です。

シート上から外部アクセスを実現する場合、Google App Script 経由となりますが、2023年現在、

• GAS は Outbound IP を固定できない
• fetch で Squid 等のフォワードプロキシー利用不可

という制約があります。そこで AWS Lambda の Function URLs を利用して、処理を実行する簡易のリバースプロキシー作ってしまうことで回避出来ます。

Lambda は実は EIP をアサインできる

あまり知られていませんが、Lambda Function は Public VPC に配置し、直接 Elastic IP をアタッチ出来ます。 これだけで任意の Lambda 関数の Outbound アクセスは固定IPになります。

何らかプライベートネットワークの制約がある場合、Private VPC + NAT Gateway 必要ですが、固定IPに振り切る場合であれば、コストヘビーな NAT Gateway が不要で、これが最もコストかかりません。

Lambda Function URLs で簡単公開

2022年のAWS新機能として、Lambda Function URL という機能がリリースされ、Function 単位で簡単に外部公開できるようになりました。

// API Gateway不要で、簡単にhttpsなPublic URL発行
https://xxxxxxxxxxxxxx.lambda-url.ap-northeast-1.on.aws

EIP つきの Public VPC 配備の Lambda と、Functoin URLs を組み合わせると、固定IP化を実現する専用のリバースプロキシーを限りなくコストゼロで構築できます。

実際に Terraform で Lambda デプロイする

具体的なコードに進みます。

今回 Lambda 関数の簡単なデプロイとして、殆の会社で導入されている Terraform を利用します。

./aws-lambda/examplecom-api-gateway/index.js
./main.tf

アーカイブモジュールを利用して、コードを準備

main.tf

locals {
  resource_prefix = "counterworks-stg"
}

data "archive_file" "examplecom" {
  type        = "zip"
  source_dir  = "${path.module}/aws-lambda/examplecom-api-gateway"
  output_path = "${path.module}/aws-lambda/.cache/examplecom-api-gateway.zip"
}

resource "aws_lambda_function" "examplecom" {
  filename         = data.archive_file.examplecom.output_path
  function_name    = "${local.resource_prefix}-examplecom-api-gateway"
  runtime          = "nodejs18.x"
  handler          = "index.handler"
  source_code_hash = data.archive_file.examplecom.output_base64sha256

  // TODO: 以下調整して下さい
  vpc_config {
    subnet_ids         = module.examplecom_proxy_vpc.public_subnets
    security_group_ids = [aws_security_group.examplecom_proxy.id]
  }
  role = aws_iam_role.iam_for_lambda.arn
}

resource "aws_lambda_function_url" "examplecom" {
  function_name      = aws_lambda_function.examplecom.function_name
  authorization_type = "NONE"
}

index.js では Lambda Function URL からの入力を受け付けて、そのまま外にプロキシー実現します。

examplecom-api-gateway/index.js

const https = require("https");

exports.handler = async (event) => {
  const { method, path } = event.requestContext.http;
  const targetUrl = new URL(`https://example.com${path}`);
  const headers = { ...event.headers, host: targetUrl.hostname };

  const options = {
    hostname: targetUrl.hostname,
    path: targetUrl.pathname,
    method,
    headers,
  };

  const response = await new Promise((resolve, reject) => {
    const req = https.request(options, (res) => {
      let data = "";
      res.on("data", (chunk) => { data += chunk; });
      res.on("end", () => { resolve(data); });
    }).on("error", reject);

    if (method === "POST") {
      const decodedBody = Buffer.from(event.body, 'base64').toString('utf8');
      req.write(decodedBody);
    }
    req.end();
  });

  return response;
};

これらを組み合わせてデプロイすると、リバースプロキシーをしてくれるエンドポイントが完成します

https://xxxxxxxxxxxxxx.lambda-url.ap-northeast-1.on.aws

上記は簡易例ですが Lambda Fuction は package.json からモジュールも読み込むことも当然できますので、より高度な処理も寄せることができます。

ユースケース1と2を組み合わせれば、簡単に処理をつなぐことができます。

最後に

以上、具体的なTIPS一部ご紹介です。エンジニアと社内人材の状況をみて組織のアウトプットが最大限出力されるシステム境界線をうまく探して、システムを設計していきましょう。