【GAS】GoogleDrive内のcsvファイルをスプレッドシートに一括インポートする

こんにちは、UUUMシステムユニットの sawada_y です。 第2回オンラインハッカソンにて、シェルスクリプトを使用して月次作業を自動化したことについて発表させていただきました。その後、別の月次作業においてもGASを使用して自動化することができましたので、そのことについて書きたいと思います。

はじめに

担当業務の中で月次でGoogleDriveにあるcsvファイル(50~60位)をスプレッドシートに手動でアップロードするという、とても面倒な月次作業がありました。そこで、GAS(Google Apps Script)を使用してGoogleDriveにある複数のcsvファイルをスプレッドシートに一括でインポートすることができたので、そのことについて書きたいと思います。※GASの環境構築についてはまとめて下さっている記事が多数あったので、省略します。

自動化する前の作業内容

Google Driveに格納されている複数のcsvファイルをスプレッドシートに手作業で1つ1つインポートする作業を月次で行なっておりました。csvファイルの種類については最近になって増えてきて、その数50~60ファイル程度になっています。ポチポチと1つ1つファイルを選択して[データをインポート]を押すというイメージです。

これは面倒。。。

ということでこの作業を自動化することにしました。

自動化した後の結果イメージ

GoogleDriveの指定フォルダにcsvファイルを格納しておいた状態で

スクリプトを実行すると、複数のcsvファイルがスプレッドシートに一括でインポートされます。

要件

結果をイメージできたところで、具体的に要件を整理します。

• Google Driveに格納された複数のcsvファイルを該当のスプレッドシートに一括でインポートしたい

• 1つのファイルの内容が1つのシートに反映されるようにする。(50ファイルあったら、合計50シートになる)

• それぞれのファイルの内容が、特定のシート名(そのファイルの一部が名前となっている)のシートに反映されるようにする

  • ex)13tokyo_2202.csvなら13tokyoという名前のシートに、11saitama_2202.csvなら11saitamaという名前のシートに反映されるようにする。(細かいですが、前月のスプレッドシートのデータが残っていることを前提にそのデータをクリアにしてから反映するようにします)

• 特定のシート名(そのファイルの一部が名前となっている)のシートがなければ、特定のシート名(そのファイルの一部が名前となっている)のシートを新たに作成しそこに反映されるようにする。

コード内容

上記の要件を満たした、GASで記述したスクリプトを以下に記載します。 初学者向けにコードの内容を書いたコメントアウトも細かめに記載しております(参考にさせて頂いた記事のコードを読み解くのに時間がかかってしまったので)。 このスクリプトを実行すると、[2.実行イメージ]のようにインポートされます。

// 書き込む対象のアクティブなスプレッドシート(スクリプトにバインドされているシート)を取得
let ss = SpreadsheetApp.getActiveSpreadsheet();

function Import() {
  // getFoldersByIdでフォルダを取得
  let folder = DriveApp.getFolderById("格納フォルダのID");
  
  // ドライブの指定フォルダの全てのファイルのコレクションを取得
  let files = folder.getFiles();
  // ()内の条件式がtrueの間、{}内の処理が実行
    // hasNextで次のファイルが存在していたらtrueを返す。全て処理をしていればfalseを返す(未処理の場合、true。処理が終わればfalseとなる。)
  while (files.hasNext()){
  // nextで未処理の次のファイルを取得
  let file = files.next();
  // ファイル名を取得
  let fileName = file.getName();
  // _で区切って配列にする
  let fileNames = fileName.split('_');
  // 0番目から最後の要素以外をアンダーバーでくっつけたシート名にする。(13tokyo_2202.csvというファイル名なら13tokyoが返ってくる。)
  let sheetName = fileNames.slice(0,fileNames.length - 1).join('_');

  // ファイル名から抽出したシート名のシートがあるか判定
  let sh = ss.getSheetByName(sheetName);
    if(sh == null)
    {
      // なければ、引数の名前の新しいシートを挿入し、取得
      ss.insertSheet(sheetName);
      sh = ss.getSheetByName(sheetName);
    }
    else
    {
      // あればシートの値や数式や書式をクリアする
      sh.clear()
    }

  // getBlobでBlobオブジェクトとして取得(Blobとはデータの内容を操作したり、データを交換する為のオブジェクト)
  // getDataAsStringでブロブをエンコーディング。charset(文字コード)による文字列として取得
  let data = file.getBlob().getDataAsString("UTF-8");
  // 文字列データcsvを区切り文字 delimiterで分割して二次元配列を取得
  let csv = Utilities.parseCsv(data);

  // セルA1からCSVの内容を書き込む
  // getRangeで書き込む範囲を指定(開始セルは1行目の1列目、書き込み行数は配列数、書き込み列数は1次元配列の要素数)
  // setValuesは配列を対象のセル範囲に入力するメソッド
  sh.getRange(1,1,csv.length,csv[0].length).setValues(csv);
  }
}

コードの細かい内容はコメントに書いておりますが、ざっと以下のことを行っています。

• 書き込むスプレッドシートを取得

• csvが格納されているフォルダを取得し、そのフォルダ内の全てのファイルを順番に処理していく

• ファイル名から一部抽出したものをシート名とする。

  • 13tokyo_2202.csvなら13tokyo、11saitama_2202.csvなら11saitamaをシート名として抽出。また、13_tokyo_2202.csvと途中で (ハイフン)がついていても 2202だけ削除し13_tokyoだけ抽出できるようにしている

• 抽出したシート名のシートが既にあればクリアしてから、csvの内容を書き込む

• 抽出したシート名のシートがなければ、その名前で新しいシートを作成し、csvの内容を書き込む

最後に

簡単なスクリプトにはなりますが、月次でインポートする作業を手動で行う手間と間違うリスクを少しばかり削減できたので、やって良かったなと思いました。ただ一旦インポートできた!という段階なので、より使用・管理しやすいように今後改良していきたいと思います。

参考にしたもの

• 公式ドキュメント: Apps Script  |  Google Developers

• Udemy: 【新IDE対応】Google Apps Script（GAS）の基礎を完全習得 -初心者歓迎-【爆速で習得しちゃおう】 | Udemy

• 書籍: 詳解！ Google Apps Script完全入門 [第3版] | 高橋宣成 | 工学 | Kindleストア | Amazon

(Udemyの動画と書籍については、自己研鑽補助の制度を利用し購入しました。)