GoogleAppsScriptを使って、SpreadSheetのテーブルをHTMLのテーブルとしてメールで送る

SpreadSheetのピボットテーブルの範囲をGmailで送りたい

普通にスプレッドシートで範囲を選択してCtrl+C、GmailでCtrl+Vすると
元の形式で指定範囲を張り付けられるのに、GoogleAppsScript（GAS）では簡単に実行できなさそう。

GASの公式ではChartは画像に変換して添付できるらしいが、SpreadSheetで作れる「Table Chart」はChar形式なのに、getAs()やgetBlob()でエラーになる。

仕方なく、HTMLのテーブルに変換することにしたけれど、使いまわしできそうなコードが書けたので共有します。

---

コードの説明

Googleスプレッドシートを開いて、[ツール]→[スクリプトエディタ]を選択すると、名前を付けるダイアログが出るので、適当な名前をつけて開きます。初期設定は下記のようになっています。

初期設定.gs

function myFunction() {
  var test
}

これを消して、下記のsendTable.gs,makeTable.gs,sendMail.gsに置き換えます。

コードの説明：sendTable.gs

ここでは、スプレッドシートの中で、テーブルとして送りたい範囲を決めて、
テーブルに変換する関数makeTable.gsに送り、htmlTableとしてHTMLのコードを取得、
それをメールで送る関数sendMail.gsに送ります。
var sheetName = "write here sheet name in which the Range included";
をvar sheetName = "sheet1";
var range = sheet.getRange("write Range which is sent to email as HTML table");
をvar range = sheet.getRange("A2:F23");
のようにして、メールで送りたい範囲を指定します。

sendTable.gs

//Send Spread sheet table with HTML Table by email
function sendTable() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "write here sheet name in which the Range included"; //"sheet1"
  var sheet = spreadsheet.getSheetByName(sheetName);
  var range = sheet.getRange("write Range which is sent to email as HTML table");  //"A2:F23"

  //make range to HTML Table
  var htmlTable = makeTable(sheet,range);

  // Send email
  sendMail(htmlTable);
}

コードの説明：makeTable.gs

まず、変換したい範囲（range)とそれが含まれるシート(sheet)の値をもとに
rangeの各列の幅、各セルの値と書式を取得します。
その後、htmlTable変数にHTMLのコードを記載していきます。
最後に、呼び出し元にHTMLコードを戻します。
メール送付などしなくても、これだけ使って色々できると思います。

makeTable.gs

//convert Range to HTML Table (overflow-wrap:break-word;)
function makeTable(sheet,range){
  var rangeHeight = range.getHeight();
  var rangeWidth = range.getWidth();
  var rangeValue = range.getValues();
  var rangeFontSize = range.getFontSizes();
  var rangeFontColor = range.getFontColors();
  var rangeFontStyle = range.getFontStyles();
  var rangeBackgroundColor = range.getBackgrounds();
  var rangeHAlign = range.getHorizontalAlignments();
  var rangeVAlign = range.getVerticalAlignments();

  var rangeFirstCol = range.getColumn();  //get first colmn of range in sheet
  var rangeColWidth = [];  //Array to hold the width of first row or range

  //HTML Table
  var htmlTable = "<table>";
  for(i = 0; i < rangeHeight; i++){
    htmlTable = htmlTable + "<tr>";
    for(j = 0; j < rangeWidth; j++){
      //get each column width to rangeColWidht[]
      if(i == 0){
        rangeColWidth.push(sheet.getColumnWidth(rangeFirstCol+j));
      }
      htmlTable = htmlTable + "<td style = 'overflow-wrap:break-word; width:"+rangeColWidth[j]+"px; text-align:"+rangeHAlign[i][j]+"; vertical-align:"+rangeVAlign[i][j]+"; font-size:"+rangeFontSize[i][j]+"; color:"+rangeFontColor[i][j]+"; font-style:"+rangeFontStyle[i][j]+"; background-color:"+rangeBackgroundColor[i][j]+";'>"+rangeValue[i][j]+"</td>";
    }
    htmlTable = htmlTable + "</tr>";
  }
  htmlTable = htmlTable + "</table>";

  return htmlTable;
}

コードの説明：sendMail.gs

自分にメールを送るコードになっています。
実行時に許可を求められるので、許可してください（参照記事No.2）
まず、emailに実行者のGmailアドレスを取得します。ほかの人に送りたいときは、送付先のアドレスを入れてください。
メールのタイトルをsubjectに、本文をhtmlBodyに記述します。
定期実行で、subjectに日付を入れるのは、下記「自動実行&タイトルに日付を追加」参照。

sendMail.gs

function sendMail(htmlTable){
  // Get the email address of the active user - that's you.
  var email = Session.getActiveUser().getEmail();

  // Get the name of the document to use as an email subject line.
  var subject = 'write subject here';

  var body ='write body here'; //ignored when htmlBody can be used in mailer

  // document to use as an email body
  var htmlBody = "" + htmlTable + "";

  // Send yourself an email with a link to the document.
  GmailApp.sendEmail(email, subject, body, {htmlBody: htmlBody}); 
}

---

オプション：「自動実行&タイトルに日付を追加」するコード

毎日自動実行するとき、日付をつけたタイトルにしたいと思いますよね。

calc_yesterday

  var today = new Date();
  var yesterday = Utilities.formatDate(new Date(today.getYear(), today.getMonth(), today.getDate() - 1), 'Asia/Tokyo', 'yyyy-MM-dd');

上記のsendMail.gsでsubject = '【日報】_'+yesterday;とすれば、
メールのタイトルが「【日報】_18-4-30」となります。（2018年5月1日に実行した場合）

Utilities.formatDate()の中は（YYYY,mm,dd）が入るのですが、ddが「0」だと前月末、「マイナス」だと年もマイナスの分前に計算してくれるようです。知ってよかった。

---

■参考にさせてもらった記事

No.1:『【Google Apps Script】その13　スプレッドシート上のグラフを画像として保存やメール送信する』
　https://qiita.com/rf_p/items/0480d85325811017b715

No.2:『【Google Apps Script】その1　Hello, world!』
　https://qiita.com/rf_p/items/0535ec495df8f9d2b894

• 日付

No.3:『Google Apps Scriptで 日付計算（加算／減算）をしたい』
　https://macprimary.white-doll.net/?p=304

No.4:『【Google Apps Script】その4　日付を綺麗に整形する』
　https://qiita.com/rf_p/items/ff841885ef6346afe5d4

• 自動実行

No.5:『【Google Apps Script】その8　スクリプトを定期実行し、ビットコインの1分ごとの価格を自動取得する』
　https://qiita.com/rf_p/items/267a8d9daa8c9f1ef027

---