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ファイルをインポートするコード

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

ちなみにかつて利用していたCSVToArray関数は以下のようなコード。テキストデータが配列になって返ってくる非常に便利なコードでした。

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側コード(変換版)

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

HTML側コード

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

図:ダウンロードをクリックすると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行ずつ書き込みをしている。csvFileには全CSVデータを連結する形で一気に流し込んでいます。
  7. エクスポートする場合なのですが、日付のデータは日付型で出力してしまうと、相手側で取り込むのがやっかいです。予め、スプレッドシート上で書式なしテキストにしておきましょう。Excelなら自動で日付型と認識してくれるようになります。

関連リンク

共有してみる: