【GAS】複数スプレッドシートをエクセルに変換し定期的に自動ダウンロード

Question

【やりたいこと】
Google Driveに複数あるスプレッドシートを、**”定期的かつ自動で”**エクセルに変換してPCに保管（バックアップ）したい。

【詳細】
Google Drive上に５つフォルダがあり、各フォルダ内には複数のスプレッドシートがあります。この複数あるスプレッドシートを月に1回、エクセルに変換したうえでPCにダウンロードして保管（バックアップ）したいと考えております。本件、手動で行えばできることは承知していますが、GASで（自動で）実現できないものか思案しております。

何か良い方法がありましたらご教示いただければ幸いです。
宜しくお願いいたします。

Answer 1

こんばんは。

時間が経ってしまっていますが、GASで目的の機能を調べてある程度の形になりましたので投稿させて頂きます。

「ダウンロードして保管」の部分は、ローカルPCに転送と考えると難しいように思えましたので、メール添付を代替案として実装しています。

以下コードで、targetFolderIdsへ5つのフォルダのID、mailtoへ送り先メールアドレスを記述して頂ければ、動作確認を頂けます。

フォルダは名前で取得する事もできますが、例えば違う階層で同じ名前のフォルダが存在するなどあると、意図しないフォルダも拾ってしまう可能性があるためIDを使って確実にする方が良いかと思いました。

フォルダ名がユニークである事が担保できるなら**getFolderByName()**を使っても良いと思います。

function sendMail_Excel() {
  // フォルダ、ファイル関係
  var targetFolderIds = ["folder1", "folder2", "folder3", "folder4", "folder5"];
  var targetFolder;
  var folderName;
  var objFiles;
  var objFile;
  var fileName;
  
  // スプレッドシート関係
  var ss;
  var key;
  var sheets;
  var sheetId;
  
  // メール関係
  var mailto = "@gmail.com";
  var subject = "";
  var body = "";
  
  // その他
  var nowDate = Utilities.formatDate(new Date(), 'JST', 'yyyyMMdd');
  var fetchUrl;
  var fetchOpt = {
    "headers" : { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
    "muteHttpExceptions" : true
  };
  var xlsxName;
  var attachmentFiles = new Array();
  
  
  for (var i = 0; i < targetFolderIds.length; i++) {
    // Idから対象フォルダの取得
    targetFolder = DriveApp.getFolderById(targetFolderIds[i]);
    folderName = targetFolder.getName();
    body = body + folderName + "\n";
    
    // 対象フォルダ以下のSpreadsheetを取得
    objFiles = targetFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
    
    while (objFiles.hasNext()) {
      objFile = objFiles.next();
      fileName = objFile.getName();
      body = body + " - " + fileName + "\n";
      
      // Spreadsheetのオープン
      ss = SpreadsheetApp.openByUrl(objFile.getUrl());
      key = ss.getId();
      sheets = ss.getSheets();
      
      // 各シートの処理
      for (var sheetNum = 0; sheetNum < sheets.length; sheetNum++){
        sheetId = sheets[sheetNum].getSheetId();
        xlsxName = fileName + "_" + sheets[sheetNum].getName() + "_" + nowDate + ".xlsx";
        body = body + "    [" + xlsxName + "]\n";
        
        // Excelファイルにして添付ファイルへ追加
        fetchUrl = "https://docs.google.com/spreadsheets/d/" + key + "/export?gid=" + sheetId + "&amp;format=xlsx&amp;sheetnames=true";
        attachmentFiles.push(UrlFetchApp.fetch(fetchUrl,fetchOpt).getBlob().setName(xlsxName));
      }
    }
  }
  
  // メール送信
  subject = "[GoogleDrive]定期バックアップ(" + Utilities.formatDate(new Date(), 'JST', 'yyyy/MM/dd') + ")";
  MailApp.sendEmail(mailto, subject, body, {attachments:attachmentFiles});
}

また、「定期的かつ自動で」もトリガー設定で実現可能と思います。
ドライブへのアクセスやメール送信など、承認が必要な機能がありますので、それらは一度手動で実行して許可してしまえば問題ないと思います。(月トリガーで日と時間帯を指定)

---

追記 2015/09/24 18:27頃

編集 2015/09/26 20:48頃

Spreadsheet単位(Excelで言うワークブック単位)でxlsxファイルを作成し、メール添付出来るようにしたコードを追記させてもらいます。
ルールは前と同じで、「フォルダID」と「送信先メールアドレス」を指定して頂ければ動作確認する事ができると思います。

function sendMail_Excel2() {
  // フォルダ、ファイル関係
  var targetFolderIds = ["folder1", "folder2", "folder3", "folder4", "folder5"];
  var targetFolder;
  var folderName;
  var objFiles;
  var objFile;
  var fileName;
  var key;
  
  // メール関係
  var mailto = "@gmail.com";
  var subject = "[GoogleDrive]定期バックアップ(" + Utilities.formatDate(new Date(), 'JST', 'yyyy/MM/dd') + ")";
  var body = "";
  
  // その他
  var nowDate = Utilities.formatDate(new Date(), 'JST', 'yyyyMMdd');
  var fetchUrl;
  var fetchOpt = {
    "headers" : { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
    "muteHttpExceptions" : true
  };

  var xlsxName;
  var attachmentFiles = new Array();
  
  try
  {
    for (var i = 0; i < targetFolderIds.length; i++) {
      // Idから対象フォルダを取得
      targetFolder = DriveApp.getFolderById(targetFolderIds[i]);
      folderName = targetFolder.getName();
      body = body + folderName + "\n";
    
      // 対象フォルダ以下のSpreadsheetを取得
      objFiles = targetFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
    
      while (objFiles.hasNext()) {
        objFile = objFiles.next();
        key = objFile.getId();
        fileName = objFile.getName();
        body = body + " - " + fileName + "\n";
      
        xlsxName = fileName + "_" + nowDate + ".xlsx";
        fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + key + "&amp;exportFormat=xlsx";
        attachmentFiles.push(UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setName(xlsxName));
      }
    }
  }
  catch(e){
    subject = "ERROR:" + subject;
    body = "エラー : " + e.message;
  }
  
  // メール送信
  MailApp.sendEmail(mailto, subject, body, {attachments:attachmentFiles});
}

Comments

[tetu]

sgr-2様

ご回答いただきありがとうございました。
早速試させていただき、エクセルファイルが添付されたメールが届きました！
トリガーの設定も定期的にできそうです。感謝いたします！！

しかし、大変図々しいのですが（汗）、重ねて質問をさせてください。

ご提示いただいたスクリプトですと、スプレッドシート内に複数シートがある場合、エクセルファイルが複数作成されます。

できることならば、”１つのファイル（スプレッドシート）に対して１つファイル（エクセル）を作成”という形が理想的です。可能でしょうか？

図々しい質問で恐縮ですが、宜しくお願いいたします。

[sgr-2]

こんばんは。

試してみたところ、”１つのファイル（スプレッドシート）に対して１つファイル（エクセル）を作成”が実現できましたので、回答本文の方へ追記をしました。
確認用データ等で動作を見て頂ければと思います。

[sgr-2]

すいません。
Googleドライブ上で、エクスポートしたSpreadsheetファイルは存在しているのですが
見えなくなる現象が起こる事がありました。
もう少し確認してみます。

[tetu]

sgr-2様

ありがとうございますm(_ _)m
宜しくお願いいたします。

[sgr-2]

こんばんは。
だいぶ時間を頂いてしまいました。
結論ですが、私がテストしていたGoogleドライブ(アカウント)に問題があったようです。
そのアカウントで方法を変えながらいろいろ試していたのですがファイルが見えなくなる現象を回避する事ができずでした。
別のアカウントでこれを実行したところ、手動とトリガーによる実行を10回以上行ってみましたが同現象を再現できませんでした。。

アカウント固有の問題だった可能性が高いと思えます。

回答本文中、後方のコードでご確認を頂ければと思います。

[tetu]

sgr-2様

ご丁寧にご対応をいただき大変感謝しております！
こちらの環境でも問題なく実現できました。早速実務で利用させていただきます！！
今後とも宜しくお願いいたしますm(__)m

[sgr-2]

お知らせ頂きありがとうございます。
問題なく実現できたとの事、良かったです。

[FdaNpo]

sgr-2様

初めまして。検索でたどり着きました。
tetu様と同じような状況で四苦八苦してましたので、助かりました。
こちらの質問と回答を参考にさせていただきました。ありがとうございます。

失礼ながら重ねて質問させていただきますが、同様にGoogle Docs を　wordファイルでバックアップ場合はどうしたらいいでしょうか？

よろしくお願いいたします　m(_ _)m

[sgr-2]

FdaNpoさん

こんにちは。
ドキュメントについて、スプレッドシートと同じような処理で良いですよね?

ほぼほぼ同じになるので、差分で書きますが
分かりにくい等ありましたらお知らせください。
-----
// MSWordファイルのcontentType
var content_type = "application/vnd.openxmlformats-officedocument.wordprocessingml.document"

// 対象フォルダに紐付くドキュメントを取得
objFiles = targetFolder.getFileByType(MimeType.GOOGLE_DOCS);

docName = fileName + ".docx"; // fileNameは「objFile.getName()」の値

fetchUrl = "https://docs.google.com/document/u/0/d/" + objFile.getId() + "/export?format=docx";
attachmentFiles.push(UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setContentType(content_type).setName(docName));
-----

[FdaNpo]

sgr-2様

返信、大変ありがとうございます!!　お陰様で一歩前進しました。

ですが試してみたら、何故か
--------------
エラー : オブジェクト xxx で関数 getFileByType が見つかりません。
--------------
となってしまいます。

指定したフォルダには test.gdoc というGoogle ドキュメントは
確かにあります。。。。
こちらのテスト環境では、MimeType.GOOGLE_DOCS で該当ファイルは取得できないようです。

もし設定等の変更が必要であれば、ご教示頂きたく思います m(_ _)m

[sgr-2]

FdaNpoさん

すいません、タイプミスがありました。。
>objFiles = targetFolder.getFileByType(MimeType.GOOGLE_DOCS);
こちらのメソッド「getFileByType」ですが、正しくは「getFilesByType」です。
正)
objFiles = targetFolder.getFilesByType(MimeType.GOOGLE_DOCS);

[FdaNpo]

sgr-2様

ありがとうございます!! 動作しました!! m(_ _)m
本当に助かりました。

また大変図々しいのですが、指定したフォルダの中の下層フォルダも再帰的に同時に処理できる方法がありましたら、こちらもご教示いただけますでしょうか？

図々しい質問を重ねて恐縮ですが、何卒宜しくお願いいたします。 m(_ _)m

[sgr-2]

FdaNpoさん

例えばになりますが、フォルダの列挙であれば
以下のようなコードで実現できます。
※CheckFolderを実行
-----
function EnumFolders(parentFolder){
var objFolders = parentFolder.getFolders();
var objFolder;

while(objFolders.hasNext()){
objFolder = objFolders.next();
Logger.log(objFolder.getName());
EnumFolders(objFolder);
}
}

function CheckFolder() {
var rootFolder = DriveApp.getRootFolder(); // ルート(マイドライブ)を取得
EnumFolders(rootFolder);
}
-----

FdaNpoさんの利用されているGoogle Driveがどのような状況になっているか分かりませんので、具体的なところは言い難いのですが「フォルダ」(と呼んでいる物)の扱いは、場合によっては注意が必要です。
「フォルダ」ですが、感覚的には「タグ」に近いもので
例えばファイルは「複数のフォルダ」にまたがって存在する事ができます。
targetFolder.getFilesByType()などで、フォルダ内のファイルを取得した結果、他の走査対象のフォルダでも同ファイルを取得する事が起こりえます。そうすると、都度getId()でID(ユニークな値)を取得して重複が発生しないような配慮が必要になると思います。

# ネタのボリュームから見ても、新規の質問で投稿頂いた方が良かったかも知れませんね。
# 蓄積されていく情報(回答)と質問タイトルの紐付けにギャップが出てきてしまうので…

[FdaNpo]

sgr-2様

重ねてお礼申し上げます m(_ _)m　早速試してみます!!

># ネタのボリュームから見ても、新規の質問で投稿頂いた方が良かったかも知れませんね。
># 蓄積されていく情報(回答)と質問タイトルの紐付けにギャップが出てきてしまうので…

確かにご指摘通りでした。申し訳ございません。

[sgr-2]

FdaNpoさん
いえ、
上のコメントの内容で言えば、EnumFoldersにバックアップ対象になるドキュメントの親フォルダを渡して、attachmentFilesにdocxファイルを追加していってやれば良いと思います。
分からないや躓いた等あれば、質問やコメントを頂ければと思います。

[FdaNpo]

sgr-2様

上記スクリプトの再帰的処理の、

var rootFolder = DriveApp.getRootFolder(); // ルート(マイドライブ)を取得

を

var rootFolder = DriveApp.getFolderById("xxxx");

に設定しての動作の確認ができました。

ですが…カレントフォルダ、下層フォルダを参照しながら、各フォルダ内のスプレッドシートをエクセルに変換及びGoogle Docs をワードに変換するという風に組み合わせることが、どうしてもできません。

本当に何度も図々しいお願いをしてしまいますが、お教えいただいたサンプルコードの組み合わせ方をご教示いただけませんでしょうか？　m(_ _)m

よろしくお願いいたします。m(_ _)m

[sgr-2]

FdaNpoさん

こんばんは。
対象をドキュメントとスプレッドシート両方にしたいのだと思いますが、あまり深く考えなくても良くて、ドキュメントに対する処理とスプレッドシートの処理を並べて書いてしまえば良いと思います。
前にコメントで書かせてもらったEnumFoldersを採用するなら、以下のような形で確認ができると思います。
-----
function EnumFolders(parentFolder, attachmentFiles){
var objFolders = parentFolder.getFolders();
var objFolder;

var objFiles;
var objFile;
var backupFileName;

Logger.log("[" + parentFolder.getName() + "]");

// GOOGLE_DOCの処理
objFiles = parentFolder.getFilesByType(MimeType.GOOGLE_DOCS);
while(objFiles.hasNext()){
objFile = objFiles.next();
backupFileName = objFile.getName() + ".docx";
attachmentFiles.push(backupFileName);
}

// GOOGLE_SHEETSの処理
objFiles = parentFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
while(objFiles.hasNext()){
objFile = objFiles.next();
backupFileName = objFile.getName() + ".xlsx";
attachmentFiles.push(backupFileName);
}

// 子フォルダの確認
while(objFolders.hasNext()){
objFolder = objFolders.next();
attachmentFiles = EnumFolders(objFolder, attachmentFiles);
}
return attachmentFiles;
}

function CheckFolder(){
var targetFolder = DriveApp.getFolderById("xxxx");
var attachmentFiles = new Array();

attachmentFiles = EnumFolders(targetFolder, attachmentFiles);
// 以下は確認用(ログにattachmentFilesの文字列を列挙)
for (var i = 0; i < attachmentFiles.length; i++){
Logger.log(attachmentFiles[i]);
}
}
-----
簡易的に書いているのでattachmentFilesにはファイル名(文字列)を入れていますが、実際は回答本文中のようにfetchUrlを作って、fetchして～の流れでattachmentFilesに追加していきます。

[FdaNpo]

sgr-2様

コード例をわざわざ提示いただき、本当にありがとうございます　m(_ _)m

上記のコードにfetchUrlを組み合わせ、メールに添付させることをここ数日トライしてみたのですが、うまくいきません…

私が根本的なjavascriptの知識がないまま質問しているせいですね…m(_ _)m　反省です。

大変図々しいお願いをして恐縮なのですが、上記のコードにfetchUrlを組み込み、メールに添付するやり方をご教示いただければ…と思います。m(_ _)m

何卒よろしくお願いします。

[sgr-2]

FdaNpoさん

送信先メールアドレスや対象フォルダIDなど、動作させる際のルールは回答本文と同様ですので省略させてもらいますが、以下のような内容で目的を実現できると思います。
-----
function sendMail_Backup() {
// フォルダ、ファイル関係
var targetFolderIDs = ["folder1", "folder2", "folder3"];
var targetFiles = {};
var attachmentFiles = new Array();
var objFolder;

// メール関係
var mailto = "@gmail.com";
var subject = "[GoogleDrive]定期バックアップ(" + Utilities.formatDate(new Date(), 'JST', 'yyyy/MM/dd') + ")";
var body = "";

try
{
// バックアップ対象のファイル情報を取得
for (var i = 0; i < targetFolderIDs.length; i++) {
objFolder = DriveApp.getFolderById(targetFolderIDs[i]);
targetFiles = getTargetFiles(objFolder, objFolder.getName(), targetFiles);
}

// バックアップ対象のファイル群を取得
attachmentFiles = getAttachmentFiles(targetFiles);

// メール本文
for (var fileId in targetFiles){
body = body + "[" + targetFiles[fileId]["path"] + "]\n";
body = body + " file : " + targetFiles[fileId]["name"] + "(" + targetFiles[fileId]["type"] + ")\n\n";
}
}
catch(e){
subject = "ERROR:" + subject;
body = "エラー : " + e.message;
}

// メール送信
MailApp.sendEmail(mailto, subject, body, {attachments:attachmentFiles});
}

// 指定されたフォルダ配下のバックアップ対象ファイル情報を返す
function getTargetFiles(parentFolder, path, targetFiles){
var objFolders = parentFolder.getFolders();
var objFolder;
var objFiles;
var objFile;
var fileId;
var fileName;

// GOOGLE_DOCSの処理
objFiles = parentFolder.getFilesByType(MimeType.GOOGLE_DOCS);
while(objFiles.hasNext()){
objFile = objFiles.next();
fileId = objFile.getId();
fileName = objFile.getName();
if (fileId in targetFiles != true){
targetFiles[fileId] = {path:path, type:"document", name:fileName};
}
}

// GOOGLE_SHEETSの処理
objFiles = parentFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
while(objFiles.hasNext()){
objFile = objFiles.next();
fileId = objFile.getId();
fileName = objFile.getName();
if (fileId in targetFiles != true){
targetFiles[fileId] = {path:path, type:"spreadsheet", name:fileName};
}
}

// 子フォルダの確認
while(objFolders.hasNext()){
objFolder = objFolders.next();
targetFiles = getTargetFiles(objFolder, path + "/" + objFolder.getName(), targetFiles);
}

return targetFiles;
}

// 添付するファイル群を返す
function getAttachmentFiles(targetFiles){

var fetchUrl;
var fetchOpt = {
"headers" : { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
"muteHttpExceptions" : true
};
var content_MSWord = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
var attachmentFiles = new Array();
var targetFile;
var backupName;

for (var fileId in targetFiles){
targetFile = targetFiles[fileId];

if (targetFile["type"] == "document"){
// MSWord形式での保存名
backupName = "[" + targetFile["path"].replace(/\//g, "_") + "]" + targetFile["name"] + ".docx";
fetchUrl = "https://docs.google.com/document/u/0/d/" + fileId + "/export?format=docx";
attachmentFiles.push(UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setContentType(content_MSWord).setName(backupName));
}
else if (targetFile["type"] == "spreadsheet"){
// MSExcel形式での保存名
backupName = "[" + targetFile["path"].replace(/\//g, "_") + "]" + targetFile["name"] + ".xlsx";
fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + fileId + "&amp;exportFormat=xlsx";
attachmentFiles.push(UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setName(backupName));
}
}
return attachmentFiles;
}

[FdaNpo]

sgr-2様

本当に、本当にありがとうございます m(_ _)m

思った通りの動作ができました!! 心から感謝申し上げます m(_ _)m
もう、感激です…

これをいい機会に、Javascript と　GAS をしっかり勉強したいと思います。

重ねて御礼申し上げます。ありがとうございました!!

Answer 2

tetuさんへ

ごくごく簡単で、解決には到らないと思いますが、情報だけは挙げておきますね。

この機能はスタンドアロンスクリプトとしてContentクラスのContentServiceクラス、TextOutputクラスを利用して構築、WEBアプリとして実現可能です。
https://developers.google.com/apps-script/reference/content/
https://developers.google.com/apps-script/guides/content

WEBアプリとしてはスプレッドシートからCSVダウンロード、というものを構築したことがありますが、
トリガーでの実行はいくつかの制限がありますので、実現の可否としては「？」です。
（認証が必要なサービスや外部ファイルへのアクセス制限に引っかかる可能性があるためダメかと思います。）

以上です。

Comments

[tetu]

stshisho様

ご回答いただきありがとうございました。

印象的に実現が厳しそうですが、Contentクラスあたりのキーワードで色々とググってみます。情報感謝です。