Google スプレッドシートの表をS3へJSONファイルとして保存する

Guten Tag、伊藤です。

弊社で管理しているいくつかの AWS アカウントについて、それぞれの利用費を取得して、対象の通貨ごとに計算したいと思いました。

ということで、AWSアカウント一覧表をスプレッドシートで管理し、それをJSONファイルとしてS3に格納、Lambdaで処理してSlackのスラッシュコマンドで計算結果を出力することにしました。

このブログでは、スプレッドシートの表をJSONファイルに格納するまでの方法をご紹介します。

なお、GAS スクリプトエディタが変わったので、新しい仕様に則っています。以前のエディタを使用する場合は過去の同様のブログをご参考ください。

GoogleスプレッドシートのデータをS3へCSVとして保存する

Google Apps Scriptを利用してGoogleスプレッドシートのデータをS3へJSONとして保存する

スプレッドシートの表の準備

お好みの表をご用意ください。表のシート名（今回であれば「Account List」）は後ほど使います。

今回、スプレッドシート上にカスタムメニューを追加し、このオプション「Upload the list to S3」を選択することで、表の内容を出力したJSONファイルがS3に置かれるようにします。

ユーザは、表を更新した場合にこのオプションを実行することでS3内のJSONファイルを更新することになります。

メニューにはユースケースに合わせて表を並び替えたり、プロパティを確認したりするオプションも加えたので、末尾のおまけで紹介します。

ポイント

• 列名の編集防止

列名はJSONのキーとして出力される（最終的にLambdaの処理にも使う）ので、誤って値を変えられないようにしておくと良いです。

「データ」→「保護されたシートと範囲」を選択し、セル範囲を選択して「+シート/範囲を追加」し、権限を設定します。

これで、対象セル範囲を編集しようとすると、編集前に以下のような警告を出すか、許可したユーザを除き編集を不可とすることができます。

ただし、表でフィルターを使用している場合、フィルタを操作・適用する際にも編集とみなされてしまいます。

今回のケースではフィルタとソートの想定されるパターンが決まっているため、それらはカスタムメニューとして追加しました。（カスタムメニューの場合は、スクリプトがフィルタやソートを実行し、ユーザが直接編集するわけではないので、権限として引っかからない）

• 列ヘッダにセル名を定義

デフォルトでは A1、B2 などセルの場所を示す番号が表示されますが、この名前ボックスをクリックして任意の値に編集することができます。

スクリプト内で使用する列のヘッダには、このようにセル名を定義しています。こうすることで、スクリプト内で直接セル番号を指定する代わりに、セル名から位置を取得することができ、もし一覧表で列の順序を変えたとしてもスクリプト側を修正する必要がなくなります。

セットアップ

AWS: S3バケットの置き場所決め

S3内でJSONファイルを置く場所およびファイル名を決めます。

今回は新しいバケットを作成し、以下のようなパスでファイルを置いてみます。

s3://[バケット名]/test/account-list.json

AWS: IAMユーザ作成とアクセスキーの取得

アクセスキーを直接GASに持たせる必要があるので、権限のなるべく制限されたIAMユーザを用意します。

今回は新規IAMユーザを作成し、以下のようなポリシーをアタッチします。先ほど決めた置き場所にファイルを置ける権限です。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::[バケット名]/test/account-list.json"
        }
    ]
}

ユーザ作成後、アクセスキーを発行します。これは、この後使います。

GAS: ライブラリの追加

「ツール」-「スクリプト エディタ」をクリックし、スクリプトエディタ（GAS）を開きます。

ここでは、外部ライブラリ Amazon S3 API Binding for Google Apps Script を追加します。

左メニューの「ライブラリ」の＋ボタンをクリックし、この外部ライブラリのスクリプトIDとして以下を入力・検索し、追加します。

1Qx-smYQLJ2B6ae7Pncbf_8QdFaNm0f-br4pbDg0DXsJ9mZJPdFcIEkw_

※以前のエディタの場合、プロジェクトID（MB...）を指定しましたが、新しいエディタではこのスクリプトIDを指定する必要があります。

GAS: プロパティの設定

スクリプトに使うアクセスキーやバケット名などの情報は、スクリプト内にベタ書きしなくて良いよう、スプレッドシートのプロパティに持たせます。

以前のエディタではGUIから設定するセクションがあったのですが、こちらも仕様が変わってしまったようなので、setPropertiesやgetPropertiesを使ってスクリプトでプロパティを設定、確認していきます。

なお、一時的に旧エディタに切り替えることでGUI設定も引き続き利用可能です。その場合は冒頭の過去ブログを参照ください。

まずスクリプトファイル（デフォルトでは「コード.gs」）に下記を貼り付けます。

先ほど取得したIAMユーザのアクセスキーの情報に置き換え、バケット名やファイルパス、シート名も適宜修正してください。そして保存します。

// Set new properties (This resets the current properties)
function setNewProperties() {
  var scriptProperties = PropertiesService.getScriptProperties();
  var newProperties = {
    'AWS_ACCESS_KEY_ID': 'AK*******************',
    'AWS_SECRET_ACCESS_KEY': '****************************************',
    'BUCKET_NAME': '[バケット名]',
    'FILE_PATH': 'test/account-list.json',
    'SHEET_NAME': 'Account List'
  };
  scriptProperties.setProperties(newProperties, true);
};

// Get the current properties
function getPropertiesInConsole() {
  var scriptProperties = PropertiesService.getScriptProperties().getProperties();
  Logger.log('=== Script Properties ===');
  for (var i in scriptProperties) {
    Logger.log('%s: %s', i, scriptProperties[i]);
  }
  Logger.log('=========================');
};

プルダウンで「setNewProperties」を選択して、[実行]をクリックします。

実行ログで実行開始&実行終了と表示されるはずです。そしたら、プルダウンで今度は「getPropertiesInConsole」を選択して、再び[実行]をクリックします。

すると、設定されたプロパティの内容が実行ログに表示されます。きちんと確認できればOKです。

スクリプトファイルにアクセスキーを書いたままにしておくのはあまりよろしくないので、５、６行目のアクセスキー・シークレットキーの値を消して、保存しておきましょう。プロパティを二度と変えるつもりがないのなら（基本的には変えないはず）、スクリプトファイル自体を削除してしまっても大丈夫です。

GAS: スクリプトの作成

それでは、新しいスクリプトファイルを追加して、スクリプトを書きます。

出力するJSONファイルは、アカウントIDをキーとした形式で以下のようにしたいと思います。
もしそういう小細工が不要なら、過去ブログのスクリプトを参照してください。

{
  "123456789012": {
    "Inactive": "x",
    "Payer": "Payer-B",
    "Account ID": "123456789012",
    "Org code": "-",
    "Account Name": "AWS Team Bob",
    "Monthly Charge Currency": "-",
    "10% Premium Plan": "-",
    "Discount": "-",
    "Type": "internal",
    "Sales/Engineer": "-",
    "Note": "POC"
  },
  "234567890123": {
    "Inactive": "",
    "Payer": "Payer-A",
    "Account ID": "234567890123",
    "Org code": "company_eu",
    "Account Name": "EU dev",
    "Monthly Charge Currency": "GBP",
    "10% Premium Plan": "No",
    "Discount": "1%",
    "Type": "customer",
    "Sales/Engineer": "Higashi",
    "Note": ""
  },
  ...
}

記述するスクリプトの内容は次の通り。

getProperties 関数内で、先ほど設定したプロパティの値を取得しています。

キーとするアカウントIDの列番号 idCol は、冒頭の通り定義したセル名からセル位置を取得していますが、ややこしければベタ書きにしても大丈夫です。

function getProperties() {
  var props = PropertiesService.getScriptProperties();
  var properties = {
    'accessKey': props.getProperty('AWS_ACCESS_KEY_ID'),
    'secretKey': props.getProperty('AWS_SECRET_ACCESS_KEY'),
    'bucketName': props.getProperty('BUCKET_NAME'),
    'filePath': props.getProperty('FILE_PATH'),
    'sheetName': props.getProperty('SHEET_NAME')
  };
  return properties;
}

function uploadSheetData() {
  // get properties and sheet
  properties = getProperties();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(properties['sheetName']);

  // get range
  var startRow = 1;
  var startCol = 2; // 今回1列目は除外するため
  var numRows = sheet.getLastRow() - startRow + 1;
  var numColumns = sheet.getLastColumn() - startCol + 1; // 対象はB-L列
  var sheetData = sheet.getRange(startRow, startCol, numRows, numColumns).getValues();

  // generate JSON data from values of sheetData
  var idCol = ss.getRangeByName('account_id').getColumn() - startCol;  // アカウントIDの列
  var header = sheetData[0];
  var body = sheetData.slice(1);
  var data = toJson(header, body, idCol);

  // execute s3 upload
  var s3 = S3.getInstance(properties['accessKey'], properties['secretKey']);
  var blob = Utilities.newBlob(data, 'application/json');
  var response  = s3.putObject(properties['bucketName'], properties['filePath'], blob, { logRequests: true });
  Logger.log(response);
};

/**
 * bodyのデータを、対応するheaderの値をキーとしたJSONデータに加工し、そのJSON文字列を返す。
 */
function toJson(header, body, id) {
  var json = {};
  for (var i = 0; i < body.length; i++) {
    var line = body[i];
    if (body[i][id]) {
      var dict = body[i][id];
      json[dict] = getListbyId(header, line);
    }
  }
  return JSON.stringify(json);
}

function getListbyId(header, line) {
  var list = {};
  for (var j = 0; j < header.length; j++) {
    if (header[j]) {
      list[header[j]] = line[j];
    }
  };
  return list;
};

プルダウンで「uploadSheetData」を選択して、[実行]をクリックします。

無事に実行完了が確認できたら成功です！S3 バケットを覗いてみて、意図した通りの JSON ファイルが格納されていることを確認します。

GAS: メニューの追加

今度は、ユーザがスプレッドシートの画面のメニューからアップロードを実行できるよう、カスタムメニューを追加します。

スクリプトの冒頭に下記を追記し、保存します。

このように onOpen() 関数を記述すると、関数内の処理がスプレッドシートを開いたタイミングで実行されます。今回では、カスタムメニューをスプレッドシート上に表示させる処理です。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Upload the list to S3', 'uploadSheetData')
      .addToUi();
}

一度スクリプトエディタ・スプレッドシートの画面ともに閉じ、スプレッドシートを開き直します。すると、上部メニューの一番右（「ヘルプ」の隣）に「Custom Menu」というのが増えているはずです。（読み込みに数秒かかります。）

メニューの「Upload the list to S3」を選択して、スクリプトがエラーなく実行されるのを確認しましょう。初回は、実行前に承認が求められます。
改めて表データがJSONファイルとしてS3にアップロードされるので、無事に実行されればファイルの更新時間が更新されているはずです。

GAS: テーブルのソート・フィルタ（おまけ）

冒頭の通り、表の表示を整えるオプションもメニューに加えようと思います。今回の場合、以下の3つを用意しました。

• sortDefault：フィルタをすべて解除し、(1列目の)番号順に並べる
• removeInactive：Inactiveにチェックのある行を非表示にする
• sortList：Inactive、Org code、Account ID の順にそれぞれ昇順で並び替え、Inactiveにチェックのある行を非表示にする

また、checkProperties でスクリプトエディタを使わずにS3の置き場所などのプロパティが確認できるようにします。

それでは、再びスクリプトエディタを開き、スクリプトファイルに下記を追記します。

function sortDefault() {
  properties = getProperties();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(properties['sheetName']);

  // Reset the filter (remove and recreate a filter)
  var filter = sheet.getFilter();
  if(filter != null){
    filter.remove();
    var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
    range.createFilter();
  }

  // Sort by the number
  var numCol = ss.getRangeByName('num').getColumn();
  sheet.getFilter().sort(numCol, true);
};

function removeInactive() {
  properties = getProperties();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(properties['sheetName']);

  var inactiveCol = ss.getRangeByName('inactive').getColumn();
  var criteria = SpreadsheetApp.newFilterCriteria()
    .setHiddenValues(['x'])
    .build();
  sheet.getFilter().setColumnFilterCriteria(inactiveCol, criteria);
}

function sortList() {
  properties = getProperties();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(properties['sheetName']);

  // Columns
  var sortCol1 = ss.getRangeByName('inactive').getColumn();
  var sortCol2 = ss.getRangeByName('org_code').getColumn();
  var sortCol3 = ss.getRangeByName('account_id').getColumn();

  // Sort the list by columns
  var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
  range.sort([{column: sortCol1, ascending: true}, {column: sortCol2, ascending: true}, {column: sortCol3, ascending: true}]);

  removeInactive();
};

function checkProperties() {
    properties = getProperties();
    var msg = `Here are the current properties:\n
      AWS access key: ${properties['accessKey']}
      AWS secret key: ********
      S3 file path: s3://${properties['bucketName']}/${properties['filePath']}
      Sheet name: ${properties['sheetName']}`;
    SpreadsheetApp.getUi().alert(msg);
}

上記をメニューに反映させるため、onOpen()を下記のように修正します。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addSubMenu(ui.createMenu('Sort the list')
        .addItem('Default', 'sortDefault')
        .addItem('Remove inactive accounts', 'removeInactive')
        .addItem('Sort as CMJ Invoice', 'sortList'))
      .addSeparator()
      .addItem('Upload the list to S3', 'uploadSheetData')
      .addSeparator()
      .addItem('Check properties', 'checkProperties')
      .addToUi();
}

参考：メニューに関するドキュメント

これで保存し、再度ウィンドウを閉じて、スプレッドシートを開き直します。「Custom Menu」をクリックすると、今度は冒頭のキャプチャと同じようにメニューが増えているはず。

「Sort the list」のメニューを選択すると、定義した通り表が並べ替えられます。
※スプレッドシートの表で行や列がフィルタ等で非表示になっていても、エクスポートされるJSONファイルのデータには含まれます。

「Check properties」を選択すると、スクリプトエディタを開かなくても次のようにシート上の画面からプロパティ内容を確認することができます。

簡単・便利ですね！

弊社は主には Office 365 で Excel を使っているので、Office スクリプトでも同じようなことができるようになると嬉しいなぁと思います。

以上、どなたかの役に立てば幸いです。