複数のスプレッドシートのCSVをShift-JISで一括取得したい

Google spreadsheetで作成されたCSVをダウンロードすると文字化けする…！ってありませんか？

文字コードの問題なのですが、これ面倒ですよね。
Excelで普通に開くと自動変換してくれないし、SaaSツール使っているとインポートの文字コードに指定あるし…

ということで、私は文字コードを指定してShift-JISで取得したいんです。

単一のスプレッドシートで運用する場合

単一のスプレッドシートファイルを使いまわす場合は複雑な操作はいりません。
そこそこ記事があがっているので参考にしてください。
ちなみに、以下サイトでは紹介されていませんが、「underscoreGS」ライブラリを追加しないといけないのでご注意ください。

GASで文字コード指定してファイルを書き出す - Qiita

GAS(google app script)を使うならUnderscoreGSライブラリを使おう！ - エンジニアはこわくない

複数のスプレッドシートを一括でShift-JISでダウンロードする場合

さて、本題。
ファイルのアクセス権を分けて運用するケースはあると思います。
例えば、人事評価とか、予算とか。

予算だと、
１）事業ごとに予算入力用のスプレッドシートを用意
２）入力後会計担当がチェック
３）予算システムに投入
みたいなことが想定されます。

この時に予算が10個を超えるとやってられません。まじでしんどい。つらい。めんどくさい。

ということで一括でダウンロードできないか考えてみました。

一括ダウンロードするための前提

一括ダウンロードするために、以下の条件は守ってもらうこととします。
そこの編集権限は必ず渡さないようにしましょう。

・特定のフォルダにまとめて格納する。
・CSV出力用のシート名は統一する。

これだけです。これだけは絶対守ってもらう。

スプレッドシートと、GASの構成

シートは2つです。
「設定」シートと、「CSV」シートを用意しましょう。

GASは以下の構成で作っていきます。

function exportcsv(){
//設定ファイルの読み込み
////スプレッドシートが格納されているフォルダIDを取得

//フォルダIDに格納されているファイルリストを取得

//各ファイルをエクスポート用のシートに反映し、CSV出力用のファンクションを実行
////各ファイルを読み込み
////各ファイルのアクセス権を付与
////読み込み時間を考慮し、スリープ（任意）
////CSV出力用のファンクションを実行
}

//CSV出力用のファンクション
function convertcsv(){
}

//スプレッドシートにメニューを用意するファンクション
function onOpen() {
}

では、まずスプレッドシート側です。
「設定」シートには以下の内容で設定情報を記載します。
いつも一括ではなく、単一ファイルだけ取得したい場合も出てくると思うのでそれも設定できるようにしておきます。

「CSV」シートは空で大丈夫です。
スプレッドシートは便利で、他のシートの特定の範囲をファイルIDを使うことで表示させることが可能です。GASでそれをA1セルに表示させようと思います。

IMPORTRANGE - Google ドキュメント エディタ ヘルプ

では、コーディングしていきます。

設定情報の読み込み

スクリプトファイルをスプレッドシートから作成したらまず「underscoreGS」をライブラリに追加しましょう。

次に、設定部分を記載していきます。

var topfolderid = "";
var fileid = "";
var filelist = [];
var filename = "";
var today = new Date();//エクスポートファイルの命名に利用

var ss = SpreadsheetApp.getActiveSpreadsheet();
var ss_setting = ss.getSheetByName("設定");
var ss_export = ss.getSheetByName("CSV");

//設定値の読み込み
////スプレッドシートが格納されているフォルダIDを取得
topfolderid = ss_setting.getRange(2, 2).getValue();
////単一ファイルのファイルIDと、ファイル名を取得
fileid = ss_setting.getRange(3, 2).getValue();
filename = ss_setting.getRange(4, 2).getValue();

//CSVエクスポート用のフォルダを作成
var folderName = Utilities.formatDate(today, "Asia/Tokyo", "yyyyMMdd")+"エクスポートデータ";
var topfolder = DriveApp.getFolderById(topfolderid);
var newfolder = topfolder.createFolder(folderName);

フォルダにあるファイルリストの読み込みから、エクスポート機能の実行指示まで

//各ファイルをエクスポート用のシートに反映し、CSV出力用のファンクションを実行
//各ファイルを読み込み
//単一ファイルかどうかの確認
if(fileid == ""){
  //フォルダIDに格納されているファイルリストを取得
  var files = topfolder.getFiles();
  while(files.hasNext()) {
    var buff = files.next();
    //IDとファイル名を配列に追加
    filelist.push([buff.getId(),buff.getName()]);
  };
}else{ //単一フィアルの場合
　　filelist.push([fileid,filename]);
}

for(var i=0;i<filelist.length;i++){
  //CSV!A1:P150の範囲をCSVシートに出力
  ss_export.getRange(1, 1).setValue("=IMPORTRANGE(\"https://docs.google.com/spreadsheets/d/"+filelist[i][0]+"\",\"CSV!A1:P150\")");
  //各ファイルのアクセス権を付与
  DriveApp.getFileById(filelist[i][0]).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  //読み込み時間を考慮し、5秒スリープ（任意）
  Utilities.sleep(5000);
  //CSV出力用のファンクションを実行。
  //ファイルIDと、エクスポート用のフォルダIDを変数として渡す。
  convertcsv(filelist[i][1],newfolder);       
}

読み込み用に5秒間のスリープを持たせていますが、これがなくても動くかは検証していません。動くような気もするけど心配なので付けています。
ただ、GASの制限に引っかからないように注意しましょう。

参考：特定フォルダ配下のファイル情報の取得

Google Drive上の指定のフォルダ以下のファイル一覧をSpreadSheetに書き出す - Qiita

参考：アクセス権の付与

Google Apps Script(GAS)でスプレッドシートのリンク共有を自動化する方法(ドキュメントも) | AutoWorker〜Google Apps Script(GAS)とSikuliで始めるRPA入門

エクスポート機能の実装

function convertcsv(filename,folder){
  var today = new Date();
  filename += Utilities.formatDate(today, "Asia/Tokyo", "yyyyMMdd") + ".csv";//ファイル名の指定
  var contentType = "text/csv";//出力形式の指定
  var charSet = "Shift_JIS";//文字コードの指定
  var lineDelimiter = ",";
  var newLineChar = "\r\n";
  //CSVシートからデータを取得
  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CSV").getDataRange();
  var values = range.getValues();

  // 2次元配列になっているデータをcsvのstringに変換
  var csvString = underscoreGS._map(
    values,
    function(row){return row.join(lineDelimiter);}
  ).join(newLineChar);

  // Shift_JISなBlobに変換
  var blob = Utilities.newBlob("", contentType, filename).setDataFromString(csvString, charSet);

  // Blobをファイルに出力
  folder.createFile(blob);
}

CSVシートからデータ取得する際、実行元から、変数を渡してあげてもいいかもしれません。

スプレッドシートのメニューに追加

普段使う時にいちいちスクリプトを開くのは面倒なので、メニューに表示されるように追加しておきます。

function onOpen() {
 // メニューバーにカスタムメニューを追加
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var entries = [{
   name : "Shift-JISで出力",
   functionName : "exportcsv"
   }];
 spreadsheet.addMenu("出力", entries);
}

作り終わった後に思ったのですが、出力時に機能を単一ファイルの出力、一括出力に分けるとよりユーザーフレンドリかもしれませんね。
スプレッドシートから実行すると、指定したTopフォルダにエクスポートフォルダが作成されていると思います。

これで以上になります。
あとは要件によって工夫してみてください。
シート名が可変になる場合、行列数が可変になる場合、他のシートからFMTに合わせてデータを加工するなど実務にあわせて必要な処理は変わってくると思います。

何度も繰り返されて面倒くさいことは自動化してストレスフリーな業務を作っていきましょう。