Google Apps ScriptでCSVファイルを取り扱う【GAS】

Googleスプレッドシートを使用していてぶつかる厄介な問題がCSVファイルの取り扱いに関する問題。主に、GoogleスプレッドシートからエクスポートしたCSVファイルをExcelで読み込めない問題や、逆に大量のCSVファイルをインポートを読み込みたい(しかもローカルPCから)となると、非常に融通の効かないアプリケーションだなと今でも思います。

これらの問題は、前者はGoogleスプレッドシートからエクスポートされるテキストがUTF-8でエンコードされたもので、取り分け日本やEXCELで普通に扱うShift-JISでエンコードされたCSVファイルではないから起き、後者はそもそも標準機能だと1個ずつしか読み込めないなどの仕様があるからです。後者の複数ファイルを読み込む件に関しては、Google Picker APIを使ったドライブから複数ファイルの読み込みや、HTML5を利用したローカルPCから複数読み込む手法を使えば行けます。

※一部のコードで不具合が出ていたので、修正するとともに、行列入替えインポートのコードを追加しました。

準備するもの

  1. 今回使用するスプレッドシート
  2. 今回使用するcsvファイル
  3. DriveAppクラスのgetDataAsString等
  4. UtilitiesクラスのnewBlobメソッドやsetDataFromStringメソッド
  5. UtilitiesクラスのparseCsvメソッド

CSVの文字コードを確認する

世の中には大きくわけて、2つのCSVファイルが存在します。それは文字コードがUTF-8のCSVと、Shift-JISのCSV。両者は見た目は同じでも、コンピュータで扱う場合、文字化けなどが発生する原因となるので、取り込む場合には事前にそのCSVの文字コードをよく考慮する必要があります。(古いオフコンだとEUCなんてものもあったりしますが)

文字コードの確認方法ですが、Windowsの場合はメモ帳で開き確認できます。名前を付けて保存をすると、文字コードが下に表示されています。これがANSIならば通常はShift-JISであろうと思われます。EUCなどの他の文字コードの場合は扱いにくいので、できればGASで取り込む前に、UTF-8に変換しておいたほうがトラブルは少ないと思いますし、後述のgetDataAsStringを使わずに済むので、楽です。

図:ANSIならばShift-JISであろう

CSVファイルをインポートするコード

普通にCSVファイルをインポートする

  • 以前は、Google Developerで公開していた「CSVToArray関数」を取り込んで変換していましたが、現在は、Utilities.parseCsvで処理が出来るようになっています。
  • 今回のコードは指定フォルダ内のCSVをDriveAppで全て拾って、取り込むコードになっています。まとめて、CSVファイルを取り込む事が可能です。
  • Excel標準のCSVはShift-JISなので、getDataAsStringにてShift_JISを指定して取り込みます。今回のファイルはUTF-8なので、UTF-8で指定しています。ただし、Excel2016くらいから標準がUTF-8になってたりするので、CSVの文字コードをよく確認して設定しましょう。
  • 指定フォルダ内ファイルだけを取り込む為、DriveApp.searchFilesを使ってファイルを取り出す場合には、DriveApp.searchFiles("'"+csvFolderkey+"' in parents")を使います。ただこのコードだと指定フォルダにcsv以外のファイルがあっても取り込もうとしてしまうので注意!!
  • CSVだけをきっちり取り出す場合には、MIMETYPE指定DriveApp.getFolderById(フォルダID).getFilesByType(MimeType.CSV);を使うとフィルタして取り出してくれます。

V8のバグ回避の為のコード

今現在もV8移行で動かない、Google側が修正していない為バグのまま取り残されてるケースが散見されます。そのうちの1つに、CSVの区切り文字のトラブルがあります。この場合parseCsvを使うには以下のようにコードを変更する必要があります。

  • delimiterが区切り文字。通常はカンマなので、指定しなくても良いのですが
  • この区切り文字がセミコロンなど特殊なケースでは今回のような工夫が必要になります。

CSVToArray関数について

かつて利用していたCSVToArray関数は以下のようなコード。テキストデータが配列になって返ってくる非常に便利なコードでした。V8でparseCsvを使った場合、区切り文字指定で取り込めないバグが出ているケースでは、この関数に置き換えると取り込めるので、現在もバグ回避の手段としても利用出来ます。

データの行列を入替えてインポートする

エクセルでもよく、データの行列を入替えて貼り付けを行います。簡単に行う関数として、Transpose関数などがありますね。しかし、大量にインポートする場合、普通にインポートを終えてから手動でこれらの作業を行うのは不便です。そこで、インポート時にそもそも行列入替えて貼り付けられたら、手間がなくなります。

今回のコードはそれを実現するコードです。

  • 途中までは通常インポートと同じコードですが、最後の書き込み時の部分で、配列の行列を入替えて貼り付けています。

特定の条件に合致するレコードだけ取り込みたい

CSVデータの特定の列にある値がある場合には取り込み対象から除外したいといったような、条件をつけてCSVをインポートしたいケースはループの中で検証して別に用意した配列にレコード単位でpushし、その配列を書き込み対象にすればOKです。

今回、22-xという文字列が入ってるレコードは除外という条件でコードを作りました。使用したファイルはこちらです。

  • 取り込みたいCSVファイルのIDを指定します。
  • レコードを1行ずつ取り出し、indexOfで入ってるかどうかをチェック。入っていない場合は取り込み対象となる(-1が返ってくる)
  • 取り出したレコードを別に用意した、arrayにpushする
  • 最後にarrayをシートに書き込む

必要な列だけをスプレッドシートに書き込みたい

CSVデータ取り込みについて、必ずしもCSVの全データを必要としてるケースは少ないと思います。既存のシートに用意された列に必要な列のデータだけを書き込みたいことはままあります。そういった場合、CSV取り込みで取得したデータをそのまま書き込むわけにはいかないので、配列操作で必要な列だけで新たな配列を用意し、それを書き込むようにします。

  • 書き込み用にarrayという配列を用意する
  • また、一時処理用のtempArrayという配列も用意する。用意する位置に注意。
  • 配列は1列目は0から始まるので要注意。3ならば4列目となります。
  • csvDataではなくarrayを書き込むようにする

CSVファイルをエクスポートするコード

GAS側コード(通常版)

  • csvchange関数はCSVToArray付属の関数を使いました。
  • Excel標準はShift-JISですので、エクスポートする時も文字コードとしてsetDataFromStringにてShift_JISを指定します。
  • Utilities.newBlobにて出力するデータのMIMETYPEとしてtext/comma-separated-valuesを指定します。
  • Google Driveからのファイル直ダウンロードは、https://drive.google.com/uc?export=download&id=に続けてファイルIDをつなげれば簡単に作れます。

GAS側コード(UTF8-BOM有り)

現在最新のExcelは、Shift-JISのCSVだけでなく「UTF8のBOM付き」であれば、ダブルクリックでも読み込めるようになっています。また、ウェブサービスの多くはUTF8でなければ読み込めないものが多いです(多くが海外のサービスでありShift-JIS未対応な所が多い)。

そこで、Google Apps ScriptからのエクスポートするCSVに対して.setDataFromStringを付けなければ、通常のUTF8となりますが、これにBOMを付けてエクスポートする場合はとても簡単。前述のコードに対して以下のようなコードの修正を加えてエクスポートするだけ。これでBOM付きUTF8のCSVになります。

このほうが、ウェブサービスでもExcelでも余計な変換の手間を掛けずにCSVを扱えるようになるので、システム間連携する場合は、BOM付きUTF8にして扱うと良いでしょう。

図:きちんとUTF8のBOM付きになった

GAS側コード(変換版)

  • こちらのコードは、PDF変換などで使うGoogleスプレッドシート標準機能を利用して変換するコードです。
  • そのままだとUTF-8形式のCSVが出力されます。
  • Access Tokenが必要なので、getOAuthToken関数を利用しています。

HTML側コード

ダイレクトに生成したCSVをダウンロードが出来ないので、HTML Serviceでダイアログを作り、ダウンロードさせるようにしています。取得データはドライブに格納されており、生成されたリンクをクリックするとダウンロードが始まります。

図:ダウンロードをクリックするとCSVファイルが入手出来る

実行と結果

今回のスプレッドシートには4つのコードが入っています。スプレッドシートのメニューより、「CSVメニュー」より実行します。GAS内で指定してるフォルダのIDを元に一括取り込みとエクスポートを実装しています。

  1. エクスポート - 通常のCSVファイル生成をしてエクスポートしています(Shif-JIS形式)
  2. エクスポートその2 - PDF生成のときにも利用してるUrlfetchAppを利用して出力(UTF-8形式)
  3. インポート - 通常のCSVファイルをUtilities.parseCSVを使って一括取り込み
  4. 行列入替インポート - インポート時に行列を入替えて一括インポートします。
Google Apps ScriptでCSVファイルの入出力

ポイント

  1. getContentAsString()は使用しない。UTF-8であるというならばそのまま使えば結構ですが。encode_utf8()にてエンコードしているような例もありますが、今回は使いません。
  2. Shift_JISなCSVの多い日本では、インポート時はgetDataAsString(“Shift_JIS”)を使用するのが良いでしょう。
  3. 逆にエクスポートする時は、.setDataFromStringにてcsvデータをshift_jisを使用するのが良いでしょう。
  4. DriveAppを今回は使用しているので、帰ってきた値は変数filesで受けて、files.next().getBlob()にて取得すること。
  5. 80個の平均5KBのCSV(合計2,800レコード/31列)読み込み完了まで、50秒程度でした。結構早いですね。
  6. 書き込み自体は1ファイルずつ書込をしています。
  7. エクスポートする場合なのですが、日付のデータは日付型で出力してしまうと、相手側で取り込むのがやっかいです。予め、スプレッドシート上で書式なしテキストにしておきましょう。Excelなら自動で日付型と認識してくれるようになります。

関連リンク

Google Apps ScriptでCSVファイルを取り扱う【GAS】” に対して2件のコメントがあります。

  1. まこきち より:

    本コードを使用してSpreadsheetに約8000行のCSVを5回連続でインポートを試みたところ、途中から処理が重くなってしまい、最後までデータをSpreadsheetにインポートすることができませんでした。何かよい対処方法ありましたら教えていただけますでしょうか。

    1. officeの杜 より:

      まこきちさん

      途中まではインポートができてると思われるのですが、結果的には最後までいけないということは、おそらく「列数が相当ある」と思われます。
      故に、最終的にはタイムアウトしてしまってるのではないかと思います。

      GASの場合このタイムアウトをいかにしてクリアするか?は、必ずぶつかる壁なのですが、以下の2つをまず、体得してみてください。

      Google Apps Scriptを高速化するテクニックまとめ
      https://officeforest.org/wp/2018/11/24/google-apps-script%e3%82%92%e9%ab%98%e9%80%9f%e5%8c%96%e3%81%99%e3%82%8b%e3%83%86%e3%82%af%e3%83%8b%e3%83%83%e3%82%af%e3%81%be%e3%81%a8%e3%82%81/

      Google Apps Scriptで6分の壁(タイムアウト)を突破する
      https://officeforest.org/wp/2018/11/24/google-apps-script%e3%81%a75%e5%88%86%e3%81%ae%e5%a3%81%ef%bc%88%e3%82%bf%e3%82%a4%e3%83%a0%e3%82%a2%e3%82%a6%e3%83%88%ef%bc%89%e3%82%92%e7%aa%81%e7%a0%b4%e3%81%99%e3%82%8b/

      件数や列数の大小というよりも、結果的に存在するデータの個数(セルの数分)が、決め手になるので、列数が少なければ数万件でも上手く6分の枠内に収まるので、
      この辺りを一度検討してみてはいかがかと思います。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)