Google スプレッドシートから手軽に DynamoDB テーブルの項目を追加する

2020.10.28

こんにちは、筧(カケイ)です。

Google スプレッドシートを利用して手軽に DynamoDB テーブルの項目を追加したい・・・!

このような場面に最近遭遇したのでやってみました。

やってみた

下図の構成で実装します。

以下の流れで作業します。

  1. S3 -> DyanamoDB の仕組み展開
  2. スプレッドシート -> S3 の仕組み展開
  3. 動作確認

1. S3 -> DyanamoDB の仕組み展開

下図赤枠の箇所を実装します。

以下を使って実装します。

Amazon DynamoDB への CSV 一括取り込みの実装 | AWS

まず、GitHub リポジトリ をローカルに clone します。

$ git clone git@github.com:aws-samples/csv-to-dynamodb.git

ファイル構成は以下の通り。

csv-to-dynamodb
├── CODE_OF_CONDUCT.md
├── CONTRIBUTING.md
├── CloudFormation
│   └── CSVToDynamo.template
├── LICENSE
├── README.md
└── testfile.csv

AWS CloudFormation コンソールでCSVToDynamo.templateを指定して、スタックを作成します。

今回は以下のようなパラメーターを設定してスタック作成します。他の設定はデフォルトで進めます。

  • BucketName :namecode-list-{AWS アカウント ID}
  • DynamoDBTableName:namecode-list
  • FileName:namecode-list.csv

スタックが完成したら、リソースを確認しましょう。

namecode-listという DynamoDB のテーブルができています。

namecode-list-{AWS アカウント ID}という S3 バケットができています。 こちらにnamecode-list.csvというファイルをアップロードすると、CloudFormation で作成した Lambda が DynamoDB のテーブルの項目を自動で追加してくれます。

S3 バケットのデフォルト暗号化、ブロックパブリックアクセス (バケット設定)、DynamoDB テーブルのパーティションキーに uuid 以外を使いたい時などは適宜設定してください。 独自のパーティションキーに変更する方法は、以下のブログで触れられています。

DynamoDBのテーブルにCSVファイルから一括でデータをimportしてみた

2. スプレッドシート -> S3 の仕組み展開

下図赤枠の箇所を実装します。

下図のようなスプレッドシートを作成します。

  • ファイル名:namecode-list
  • シート名:namecode-list
  • uuid(A列):一意となる id を入れます。
  • name(B列):アルファベットで代替します。

続いて、以下のブログを参考にして、スプレッドシート -> S3 の仕組みを構築していきます。

先に作成したリソースを指定しながら、以下の IAM ポリシーを作成します。 今回の方法は外部ライブラリを利用します。 当該ライブラリを使う為にはアクセスキーとシークレットアクセスキーが必要になります。 万が一アクセスキーが漏洩しても被害を最小限とするためにも、権限は最低限としておきます。

  • IAM ポリシー名:s3-put-only-policy
  • {BucketName}:namecode-list-{AWS アカウント ID}
  • {FileName}:namecode-list.csv

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::{BucketName}/{FileName}"
        }
    ]
}

プログラムによるアクセスのみ可能な IAM ユーザーを作成して、先ほど作成した IAM ポリシーを適用します。 IAM ユーザーのアクセスキーとシークレットアクセスキーは後で使うのでメモしておいてください。

作成したスプレッドシートから、スクリプトエディタを開きます。

左上をクリックしてプロジェクト名を決めます。今回はnamecode-listとしておきます。 またスクリプトを貼り付けます。

function uploadS3() {
  // use library: MB4837UymyETXyn8cv3fNXZc9ncYTrHL9
  var props = PropertiesService.getScriptProperties();
  var accessKey = props.getProperty('AWS_ACCESS_KEY_ID');
  var secretKey = props.getProperty('AWS_SECRET_ACCESS_KEY');
  var bucketName = props.getProperty('BUCKET_NAME');
  var filePath = props.getProperty('FILE_PATH');
  var spreadsheetId = props.getProperty('SPREADSHEET_ID');
  var sheetName = props.getProperty('SHEET_NAME');
  
  var sheetData = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName).getDataRange().getValues();
  var csv = sheetData.join('\n');
  csv = Utilities.newBlob( csv );
  
  var s3 = S3.getInstance(accessKey, secretKey);
  var response = s3.putObject( bucketName, filePath, csv, {logRequests:true} );
  Logger.log(response);
}

外部ライブラリをインポートします。スクリプトエディタのライブラリを開きます。

ポップアップが表示されたら、以下の手順を実行します。

  1. MB4837UymyETXyn8cv3fNXZc9ncYTrHL9と入力する
  2. 追加をクリックする
  3. バージョンを選択する
  4. 保存をクリックする

スクリプトのプロパティを編集します。以下の情報を使います。

  • AWS_ACCESS_KEY_ID
    • 取得したアクセスキー
  • AWS_SECRET_ACCESS_KEY
    • 取得したシークレットキー
  • BUCKET_NAME
    • CFn で作成したバケット名
    • namecode-list-{AWS アカウント ID}
  • FILE_PATH
    • CFn で指定したファイル名
    • namecode-list.csv
  • SPREADSHEET_ID
    • データを管理しているスプレッドシートのID(対象スプレッドシートの URL から参照)
  • SHEET_NAME
    • データを保持しているシート名
    • namecode-list

スクリプトエディタからプロジェクトのプロパティを開きます

スクリプトのプロパティタブを選択し、自分の環境に合わせて各パラメータを設定して保存します。

3. 動作確認

スクリプトを動かしてみましょう。関数にuploadS3を選択して、実行ボタンを押します。

次のような確認が最初だけ表示されます。許可しましょう。

指定の S3 バケットにnamecode-list.csvが追加され、DynamoDB のnamecode-listテーブルの項目にスプレッドシートの内容が追加できました!

スクリプトの実行ログは、下図の箇所を選択することで確認できますので、うまく実行できない場合はこちらを確認してください。

うまく実行できたら、スプレッドシートからより手軽に項目追加できるように設定してみます。スプレッドシートからマクロのインポートを設定します。

uploadS3関数を追加します。

スプレッドシートにデータを追加します。

スプレッドシートのマクロからuploadS3をクリックすると、手軽にマクロを実行して DynamoDB のテーブルに項目追加できます。

DynamoDB のテーブルを確認してみましょう。

スプレッドシートに追加したデータもちゃんと反映されていますね!

おまけ:データ削除について

今回紹介したのはデータ追加を手軽に行う方法です。スプレッドシートからデータを削除しても、DynamoDB のテーブルからは項目削除されません。 項目削除する場合は以下の方法が挙げられます。

  1. DynamoDB のマネジメントコンソールで対象項目を選択して削除
  2. CloudFormation を変更して新しいテーブルを作成

削除項目が少なければ1で良さそうですが、削除項目が多ければ2の方が良さそうです。 今回は2の方法をやってみましょう。

CloudFormation の対象スタックを選択して、更新をクリックします。

現在のテンプレートを使用のまま次へを選択します。

DynamoDBTableNamenamecode-list-newに変更します。他の設定はそのままでスタックを更新します。 スタックを更新すると、前のテーブル(namecode-list)は削除され、新しいテーブル(namecode-list-new)が作成されます。

スプレッドシートのデータを編集します。下図の通り、最初の行だけ残します。

スプレッドシートのマクロからuploadS3をクリックして、DynamoDB のテーブルを確認してみましょう。

新しいテーブル(namecode-list-new)に適切なデータが反映されていますね!

終わりに

この仕組みを使えばサーバレス開発が捗りそうなので実装してみました。お役に立てていると嬉しいです。 CloudFormation で展開したリソースの使用は参照した以下のドキュメントに記載があるので、何かあれば確認してみてくださいね。

Amazon DynamoDB への CSV 一括取り込みの実装 | AWS

以上、 筧(カケイ) がお送りしました。それでは皆さん良い一日を!