Googleドライブのフォルダ内にある複数のCSVファイルを一括でスプレッドシートにインポートしたい

2022年10月26日 GAS活用法

期間別に別々にしかcsv出力できないデータがあります。これをスプレッドシートの1枚のシートに統合してインポートしたいというというのが今回のお題です。

ちょっとパワークエリ的な使い方かもしれませんが、パワークエリいまいちよくわかっていないのGoogle Apps Script（以下 GAS）で実装しました。

取り込むcsvが同じフォーマットであることが前提になります。この複数のcsvをGoogleドライブの任意のフォルダに保存し、1ファイルごとに読み出して、ヘッダー行を削除しつつシートの最終行に追加していきます。

csvファイルを保存するフォルダを準備してIDを控える

まず取り込みたいcsvファイルを保存する任意のフォルダをGoogleドライブ内に作成します。今回はcsv_importというフォルダを作成して、csvファイルを保存しました。

GASでフォルダ内のファイルを操作するには、このフォルダのIDが必要です。

フォルダIDの確認方法はいくつかありますが、Googleドライブで指定したフォルダを開いてURLを確認するのが早いです。

…folders/以降の部分がフォルダIDになります。

フォルダ内のcsvファイルをシートに追加していく

csvファイルをシートに転記するスクリプトは、ノンプロ研仲間のカワムラさんの記事を参考にしました。

カワムラ｜[GAS]CSVファイルをスプレッドシートに転記

記事との違いは、フォルダ内に複数のcsvファイルが存在する点です。FileIteratorオブジェクトから取り出しできる全てのファイルに対してシートにインポートする処理を実装しました。

このFileIteratorオブジェクトというのが、GAS初心者のころにパッとわからなかったのですが、getFiles()メソッドの戻り値は、Fileオブジェクトを格納した配列ではなく、このFileIteratorオブジェクトだったんですね。

whileループとhasNext()メソッドとnext()メソッドを組み合わせて、すべてのFileオブジェクトに何らかの処理を実行するというのが定番です。

// ドライブ内の全てのファイル名をログ出力する関数
const files = DriveApp.getFiles();
while (files.hasNext()) {
  const file = files.next();
  console.log(file.getName());
}

ということで、FolderクラスのgetFiles()メソッドを使って、FileIteratorオブジェクトを取得して、そこから各Fileオブジェクトを取得していきます。

const filesCsv = folderCsv.getFiles();

続いて各Fileオブジェクトを

Blobオブジェクトに変換
Blobオブジェクトを文字列に変換
文字列をparse（分析）して二次元配列に変換
shiftメソッドで

という処理にかけます。

FileクラスのgetBlob()メソッド
BlobクラスのgetDataAsString()メソッド
UtilitiesクラスのparseCsv(csv)メソッド

をそれぞれ利用します。

この辺りは、どのオブジェクトにどのメソッドが使用できるか、また戻り値が何かという理解をしっかりしていると助けになりますね。

ということで、まずGoogleドライブのFileオブジェクトからBlobオブジェクトに変換します。

Blobオブジェクトは、ざっくりいうとファイルそのものです。このBlobオブジェクトに変換することで、汎用的に利用できるようになります。

const file = filesCsv.next();
const blob = file.getBlob();

ただ、Blobオブジェクトそのものはバイナリ（0と1の二進法）で表されるデータとのことで、今回は中身がcsvファイルなので、これをまず文字列に変換します。

const strCsv = blob.getDataAsString();

さらにその文字列をGASで扱える2次元配列に変換してあげる必要があります。

const ary2D = Utilities.parseCsv(strCsv);

あとはいつものGASによるスプレッドシートの操作ですね。

今回は、同じフォーマットのcsvファイルのデータをどんどんと追加していく処理のため、任意のシートの最終行にデータを追加するaddDataSheet_()関数を別に作成しました。

任意のシートの最終行にデータを追加していく処理は他でも利用しそうですよね。

/* シートの最終行にデータを追加していく関数 */
function addDataSheet_(sheet, ary2D) {
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(lastRow + 1, 1, ary2D.length, ary2D[0].length);
  range.setValues(ary2D);
}

ということで、完成したスクリプトはこちらです。

function importCsv() {

  // csvファイルを格納しているフォルダをIDから取得
  const folderCsv = DriveApp.getFolderById('フォルダID');

  // フォルダ内の全てのファイルをFileIteratorオブジェクトとして取得
  // https://developers.google.com/apps-script/reference/drive/file-iterator
  const filesCsv = folderCsv.getFiles();

  // アクテイブなスプレッドシートから指定した名前のシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetTest = ss.getSheetByName('test'); // 任意のシート名を指定

  // FileIteratorオブジェクトからFileオブジェクトを1つづつ取り出しcsvを読み込む
  while (filesCsv.hasNext()) {
    const file = filesCsv.next();
    const blob = file.getBlob();
    const strCsv = blob.getDataAsString();
    const ary2D = Utilities.parseCsv(strCsv);
    ary2D.shift(); // ヘッダー行を削除
    
    // シートの最終行にデータを追加していく処理はよく使いそうなので、別に関数化
    addDataSheet_(sheetTest, ary2D);
  }
}

/* シートの最終行にデータを追加していく関数 */
function addDataSheet_(sheet, ary2D) {
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(lastRow + 1, 1, ary2D.length, ary2D[0].length);
  range.setValues(ary2D);
}