【GAS】マイドライブにあるスプレッドシートをExcelに変換して、対象者へ自動でメールを送信する方法

Question

【やりたいこと】
マイドライブにある指定のスプレッドシートを毎日指定の時間になったら、Excel形式へと変換して対象者へとメールを自動送信する方法を実現させたいと考えております。

【これまでに】
https://teratail.com/questions/16326
上記のteratailが似たような事をやっているのではないかと思い試していますが上手くいきません。 私のやり方が間違っているのかも分からない状態です。

メールはうまく発行できるのですが、何も添付されていない状態です。
そもそも使い方が間違っているのでしょうか・・・・。

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

【参考コード】

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});
}

【うまく動作しないコード】
上記参考コードの内、ファイルIDとメールアドレスさえ修正しておけば、
スプレッドシートがExcel形式に変換されて、添付メールとなって送る事が出来ると思ったのですが・・・・・。
すみません。GASに触れる事が今回初めてなもので。

function sendMail_Excel2() {
  // フォルダ、ファイル関係
  var targetFolderIds = [”対象のスプレッドシートのファイルIDを入力"];
  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});
}

sgr-2様のアドバイス後下記の通り修正を行い、
添付のあるメールを送る事までは出来ましたが、
添付されたExcelには、データはなく開く事もできませんでした。
もしかしたらでもなく、まだ余計なコードを書いてしまっているのかもしれません。

function sendMail_Excel() {
// フォルダ、ファイル関係
var targetFileId = ["スプレッドシートのファイルID"];
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();

// 添付するファイルの名前を指定
xlsxName = "付けたい名前.xlsx";

try
{
fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + targetFileId + "&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

[kei344]

具体的にコードを質問文に追記いただいたほうが回答を得られやすいと思います。

[asahina_dev]

そのうまく言ってないコードを貼り付けようもちろんコードブロック付きでね

[TS8332]

Kei344様・asahina_dev様　早速の質問へのアドバイスありがとうございます。
それでは、アドバイス通りにコードを質問事項に書き込ませていただきます。

[kei344]

コードはasahina_devさんのおっしゃるように、コードブロックで囲んでいただけませんか？ ```（バッククオート3つ）で囲み、前後に改行をいれるか、コードを選択して「</>」ボタンを押すとコードブロックになります。

Answer 1

TS8332さん
こんばんは。

「うまく動作しないコード」見せて頂きました。
参考頂いたコード側ですが、以下のように書いている通り
対象スプレッドシートについてフォルダ指示の形式をとっています。

var targetFolderIds = ["フォルダのID",...]

対して、うまく動作しないコードの側では

var targetFolderIds = ["対象のスプレッドシートのファイルIDを入力"];

のように記述している事から、「直接スプレッドシートを指示する」が
実現したい事になるでしょうか?

であるとすれば。の前提になりますが、参考頂いたコードの処理には
不要な部分が出てきてしまい、それがそのまま残っていて
「ファイルのIDに対してフォルダ絡みの処理をさせようとしている」
が直接的な原因になるかと思います。

具体的には、以下が該当します。

// 先ず、フォルダでループを回している(29行目から)
for (var i = 0; i < targetFolderIds.length; i++) {
    targetFolder = DriveApp.getFolderById(targetFolderIds[i]); // ←躓くはず


// フォルダに属する(タグ付けされた)スプレッドシートを列挙している(35行目から)
objFiles = targetFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
while (objFiles.hasNext()) {
    objFile = objFiles.next();
    key = objFile.getId();
    (省略)
}

ご質問頂いている文面からですと、送信対象のスプレッドシートが1つなのか複数なのかが分かりませんが、仮に1つならループはまったく必要なく、以下のように書けると思います。(主要部分のみ)

// 対象がファイルなので、targetFolderIdsは変更しておきますね。
var targetFileId = "対象スプレッドシートのID";

// 添付するファイルの名前を指定
xlsxName = "付けたい名前.xlsx";

try
{
    fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + targetFileId + "&exportFormat=xlsx";
    attachmentFiles.push(UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setName(xlsxName));
}
catch(e){
    // 省略
}

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

実際に動作の確認までは出来ていないのですが、大丈夫ではないかと思います。。

Comments

[TS8332]

sgr-2様　質問への御回答ありがとうございます。

回答アドバイスどおり実施してみた処、添付のメールを発行する事まではできましたが、
添付のファイルは容量1KBの空データ状態で出力されてきました。
中身が全くなく、開くことさえも出来ない状態です。

[TS8332]

コードはこのように書きました。
function sendMail_Excel() {
// フォルダ、ファイル関係
var targetFileId = ["スプレッドシートのファイルID"];
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();

// 添付するファイルの名前を指定
xlsxName = "付けたい名前.xlsx";

try
{
fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + targetFileId + "&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});
}

[sgr-2]

こんばんは。
私の手元で参考コードを元に変更しテストしてみました。
結果、うまくxlsxファイルが添付されたファイルが送信されました。

以下が、テストに使った完全なコードです。
(※メールアドレスとファイルIDは伏字)
function sendMail_Excel() {
// ファイル関係
var targetFileId = "*****"
var objFile;
var fileName;

// メール関係
var mailto = "*****@gmail.com";
var subject = "スプレッドシート添付テスト";
var body = "";

// その他
var fetchUrl;
var fetchOpt = {
"headers" : { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
"muteHttpExceptions" : true
};

var xlsxName;
var attachmentFiles = new Array();

try
{
// ファイルIDからファイルの名前を取得する
objFile = DriveApp.getFileById(targetFileId);
fileName = objFile.getName();

// 添付するExcelファイルの名前
xlsxName = fileName + ".xlsx";

body = xlsxName + "を添付します";

fetchUrl = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + targetFileId + "&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});

コードを同じような形にしてもうまくいかない場合は、可能性を潰していく事になりますが、単純に思い付くところとしては
・ファイルID(targetFileIdに設定している文字列)は正しいでしょうか?
・対象のスプレッドシートはスクリプトが実行されるアカウントでアクセス可能でしょうか?
があるかと思います。

一つの確認方法として、fetchUrlに設定しているURLの文字列
https://docs.google.com/ ... Export?key=(対象のファイルID)&amp ...
にブラウザから直接アクセスしてみてはいかがでしょうか?

[TS8332]

sgr-2様　
御連絡が遅れてしまい申し訳ございません。
アドバイスどおりスクリプト修正後、処理を実行した処、マイドライブよりスプレッドシートをExcel形式に変換して添付メールを送る事は成功し、問題となっていた添付のファイルを開けない問題も解決されました。　ありがとうございます。
しかし、一部Google関数を使用しており、その内『IMPORTRANGE』を使用しているセルのみ結果がエラーとなっている箇所があります。
当然の結果ではあるのですが、マイドライブから対象のファイルをメールへと添付する際に、データの形式を全て値に変える方法などもご教授いただけると、問題解決に至れるかと思います。
また、可能であればの話しではありますが、データの形式を値に変えるのは、指定のセル範囲のみでお願いします。

[sgr-2]

こんにちは。
先ずは、Excel形式で添付して正常に開く事が出来るようになったとの事、良かったです。

一括で式(IMPORTRANGE等)を結果の値(Value)に置き換える方法については、すいませんが私には分かりません。(存在するかどうかのレベルから)

少々泥臭くなりますが、例えば以下のように書いて指定セルの「値」「式」を取得できますので、それを利用する手はあると思います。
---
var ss = SpreadsheetApp.openById(targetFileId);
var sheet = ss.getActiveSheet(); // 仮にアクティブシートを対象
var range = sheet.getRange("A1"); // 仮にA1セルを対象

var value = range.getValue(); // 値
var formula = range.getFormula(); // 式
// 確認用にログへ
Logger.log(value);
Logger.log(formula);
---

オリジナルのセルの値を上書きしてはいけないと思いますので、
(a)添付前に対象スプレッドシートの複製を作成し、複製したファイルのセルの値を上書き(getValueの結果で上書き)して、これを添付する
(b)オリジナルのスプレッドシートにExport用のシートを追加し、メール送信のタイミングでExport用シートの値を最新の値に更新する(オリジナルシート側のgetValueの結果でExport用シート側のセルの値を上書き)
等が方法として考えられると思います。

(a)の方法なら
・添付対象となるスプレッドシートに複数のシートが存在する場合に(b)より楽
・メール送信(スクリプトが実行される)のタイミングで、複製を作成して送信後に複製を破棄する
(b)の方法なら
・単一シートなら、Export用にシートを1つ追加するだけ(複数ならその数必要になる)
・都度、複製の作成と破棄が必要ない
・Export用シートのみでxlsxファイルを作って、オリジナル側のシート(エラーのあるセルが存在)を除外する

[TS8332]

sgr-2様
アドバイスありがとうございました。
上記、アドバイスを元に色々と試させていただきます。
なお、今回の質問はあくまでもGoogleドライブに保存されたスプレッドシートを、Excelの添付ファイルにしてスクリプトエディタのトリガーを使って自動送信であった為、問題は解決として、sgr-2様の回答をベストアンサーとして登録させていただきます。
ありがとうございました。