多分保存版？S3とSpreadSheet間でのCSVとJSONのデータ読み書きを簡単なサンプルで網羅してみた

はじめに

CSVやJSONのデータをGASを通してSpreadSheetとS3間でやり取りする可能性がでてきたものの、いい塩梅でのサンプル等もなかなか見つからなかったため、少し拵えてみることにしました。

事前準備

全般的に以下のエントリーを参考してみてください。

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

AWS

必要に応じて作成します。

• S3バケット
• IAMポリシー
• IAMユーザ

GAS

外部ライブラリを利用します。「MB4837UymyETXyn8cv3fNXZc9ncYTrHL9」を指定するだけです。

• Amazon S3 API Binding for Google Apps Script

SpreadSheetとS3間のデータ移植

まとめて一つにしようと思いましたが、まるごと必要なケースも早々無いだろうと思い、ユースケースで分けました。

CSVのSpreadSheet→S3

一応セル内のデータをダブルクォーテーションにて囲っていますが、厳密にしたい場合はより調整が必要かもしれません。

function csv_ss_to_s3() {
  var awsAccessKeyId = <ACCESS-KEY-ID>
  var awsSecretKey = <SECRET-KEY>
  var ss = SpreadsheetApp.openById(<SPREADSHEET-ID>);
  var sheet = ss.getSheetByName(<SHEET-NAME>);
  var data = sheet.getRange('A:G').getValues();
  var csvString = '';
  for ( var i = 0; i < data[0].length; i++ ) {
    if (csvString != '') {
      csvString += ',';
    }
    if (data[0][i] != '') {
      csvString += '"' + data[0][i] + '"';
    }
  }
  csv = Utilities.newBlob(csvString, "text/csv", "UTF-8");
  var s3 = S3.getInstance(awsAccessKeyId, awsSecretKey);
  s3.putObject( <BUCKET-NAME>, 'test.csv', csv, {logRequests:true} );
}

CSVのS3→SpreadSheet

値のチェックは入れていません。必要に応じてセルの書式を変える必要があります。

"test1","test2","test3","test4","test5","test6","test7"

function csv_s3_to_ss() {
  var awsAccessKeyId = <ACCESS-KEY-ID>
  var awsSecretKey = <SECRET-KEY>
  var s3 = S3.getInstance(awsAccessKeyId, awsSecretKey);
  var data = s3.getObject(<BUCKET-NAME>, "test.csv");
  var ss = SpreadsheetApp.openById(<SPREADSHEET-ID>);
  var sheet = ss.getSheetByName(<SHEET-NAME>);
  for(var i = 1; i < data.length + 1; i++){
    sheet.getRange(1, i).setValue(data[i - 1]);
  }
}

JSONのSpreadSheet→S3

特定範囲の値をまるごと取ってそのまま保存しているため、厳密にしたい場合は精査が必要になります。

function json_ss_to_g3() {
  var awsAccessKeyId = <ACCESS-KEY-ID>
  var awsSecretKey = <SECRET-KEY>
  var ss = SpreadsheetApp.openById(<SPREADSHEET-ID>);
  var sheet = ss.getSheetByName(<SHEET-NAME>);
  var data = sheet.getRange('A:G').getValues();
  json = Utilities.newBlob(JSON.stringify(data[0]), "text/json", "UTF-8");
  Logger.log(JSON.stringify(data[0]));
  var s3 = S3.getInstance(awsAccessKeyId, awsSecretKey);
  s3.putObject( <BUCKET-NAME>, 'test-2.json', json, {logRequests:true} );
}

JSONのS3→SpreadSheet

値のチェックは入れていません。必要に応じてセルの書式を変える必要があります。

{
  "Columns":
    [
      {
        "name": "test1"
      },
      {
        "name": "test2"
      },
      {
        "name": "test3"
      },
      {
        "name": "test4"
      },
      {
        "name": "test5"
      },
      {
        "name": "test6"
      },
      {
        "name": "test7"
      }
    ]
}

function json_s3_to_ss() {
  var awsAccessKeyId = <ACCESS-KEY-ID>
  var awsSecretKey = <SECRET-KEY>
  var s3 = S3.getInstance(awsAccessKeyId, awsSecretKey);
  var data = s3.getObject(<BUCKET-NAME>, "test.json");
  var ss = SpreadsheetApp.openById(<SPREADSHEET-ID>);
  var sheet = ss.getSheetByName(<SHEET-NAME>);
  for(var i = 1; i < data.Columns.length + 1; i++){
    sheet.getRange(1, i).setValue(data.Columns[i - 1].name);
  }
}

まとめ

YAML等でも検証してみようと思いましたが、SpreadSheet側でYAML用に整形するのが面倒になり見送りました。

データチェック等含めると処理はもっと膨大になりますが、そこまで厳密にはしない移植であれば割とお手軽だと思います。また、今回のサンプルは手間を省くために直埋めにしていますが、AccessKeyIdとSecretKeyの取扱には十分に注意してください。