GASでスプレッドシートのデータをテキストファイルにしてGoogleDriveに保存する

この記事は、ZOZOテクノロジーズ #2 Advent Calendar 2019 5日目の記事になります。
昨日は、@tsucasa さんの「データ集計やりにくい感の原因 ～OLAP～」でした。

GAS(Google Apps Script)でスプレッドシートのデータをテキストファイルに変換して
GoogleDrive内に保存したときのメモです。

概要

スプレッドシートの内容は以下の通りです。

• シートは2つ
  • 文字列とIDの変換テーブルを記載したシート(Sheet1)
  • Textに変換するデータを記載したシート(Sheet2)

Sheet1

	A列	B列
1行目	201	アウター
2行目	202	トップス
3行目	301	パンツ
4行目	302	ワンピース
5行目	303	オールインワン

Sheet2

	A列	B列	C列
1行目	1	ワンピース	4980
2行目	2	トップス	1980
3行目	3	アウター	9800
4行目	4	アウター	7980
5行目	5	パンツ	2980

やりたいことは以下の通りです。

• Sheet2のデータ(表)をテキストにする
• テキストにする際、Sheet2の文字列をSheet1のIDに変換して出力する

実装

シートの参照

複数のシートを参照する場合にシート名を指定して参照します。

// Spreadsheetオブジェクト取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// Sheetオジェクト取得
var tablesheet = spreadsheet.getSheetByName("Sheet1");
var datasheet = spreadsheet.getSheetByName("Sheet2");

テーブルをRangeとして取得

データをRangeでまとめて取得します。
行数、列数は最後の行番号、最後の列番号を指定します。

// getRange(開始行番号,開始列番号,行数,列数)
var data = datasheet.getRange(1, 1, datasheet.getLastRow(), datasheet.getLastColumn()).getValues();

テーブルをMapとして格納

上記のRangeではIndexでしか参照できません。
データテーブルの文字列でそのままアクセスしたいので、
文字列とIDの変換テーブルはMap形式で格納します。

  var map = {};
  // B列の文字列をKeyとしてA列のValueを1行目から最終行まで格納する
  for (var i = 1; i <= tablesheet.getLastRow(); i++) {
    map[tablesheet .getRange(i, 2).getValue()] = tablesheet .getRange(i, 1).getValue();
  }

数値を0埋めで出力する

データの桁数を合わせるため、数値を0埋めで出力します。

var textdata = ""
// データの値を4桁で0埋めする
textdata = ('0000' + data[1][1]).slice(-4);

文字列をファイルに保存する

GoogleDriveのフォルダにファイルを新規で作成し、
作成したファイルに文字列を出力します。

格納先フォルダID

ファイルを格納したいフォルダをGoogleDrive内に作成します。
作成したフォルダを開き、URLの末尾のIDを控えておきます。
控えたIDをgetFolderById()の引数に指定します。

  var folder = DriveApp.getFolderById('※フォルダのID');
  var contentType = 'text/plain';
  var charset = 'utf-8';

  var blob = Utilities.newBlob('', contentType, ※ファイル名).setDataFromString(※文字列, charset);

  // ファイルに保存
  folder.createFile(blob);

コード

上記の説明の全体のコードは以下になります。

function OutputData() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Sheetオジェクト取得
  var tablesheet = spreadsheet.getSheetByName("Sheet1");
  var datasheet = spreadsheet.getSheetByName("Sheet2");
  
  // getRange(開始行番号,開始列番号,行数,列数)
  var data = datasheet.getRange(1, 1, datasheet.getLastRow(), datasheet.getLastColumn()).getValues();
  
    var map = {};
  // B列の文字列をKeyとしてA列のValueを1行目から最終行まで格納する
  for (var i = 1; i <= tablesheet.getLastRow(); i++) {
    map[tablesheet .getRange(i, 2).getValue()] = tablesheet .getRange(i, 1).getValue();
  }
  
  var textdata = "";
  // データ分文字列として格納
  data.forEach(function(values){
    textdata += ('0000' + values[0]).slice(-4) + "," + map[values[1]] + ',' + values[2] + '\n';
  });
  // テキストファイルに保存
  createFile("test.txt",textdata);
}

function createFile(filename, contents) {
  var folder = DriveApp.getFolderById('※ファイル名');
  var contentType = 'text/plain';
  var charset = 'utf-8';

  var blob = Utilities.newBlob('', contentType, filename).setDataFromString(contents, charset);

  // ファイルに保存
  folder.createFile(blob);
}

おわりに

今まではExcelのマクロでファイル出力していましたが、
スプレッドシートでGoogleDriveにファイル出力すれば他の人ともその場で共有できて楽ですね。

参考