この記事は、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にファイル出力すれば他の人ともその場で共有できて楽ですね。
参考