elude丸

日々の思い描くことをツラツラと綴るブログ

*

GoogleスプレッドシートのデータをGASを使ってHTML出力する方法

      2017/04/03


GoogleスプレッドシートとGoogle Apps Scriptを使ってデータをHTML出力する方法

先日、Netflixのコンテンツラインナップを改善するぞ!
と紹介をさせて頂いたのですが、その時にこれまで以上に更新作業を効率化したい!
という改善欲求がふつふつと沸き起こり、なんとか半自動化にて簡易化できないか!
一念発起致してこのシステムを作りました。

codeing

取り組んだのが「Googleスプレッドシート」「Google Apps Script」(GAS)を組み合わせ。
スプレッドシート内にコンテンツ情報を追記して保存し
ボタンひとつで自動的にHTML形式のリストをGASにて生成して出力します。
後はコピーによってテキストをWordpress上に流し込むだけで万事解決。というシステムになります。

まあ、簡単に説明すると
「GoogleスプレッドシートのリストをHTML形式で書き出すGAS」
というものになります。

現状二つのパターンで公開しております。

……どちらもそれぞれ何かしらの問題のあるプログラム。と、酷いものですが
そんなプログラムでも参考にしていただければと思います。

これまでの更新とその更新に行き詰った理由

プログラムの話に入る前にこれまでの更新状況を説明。
GASのスクリプト情報が知りたい場合は読み飛ばして下さい。

これまでの更新は、更新リストをテキストファイルにて保存し
Netflixのラインナップを見つつ、これまで蓄積してきたテキストファイルと
整合性をチェックし、増えていた場合にはテキストファイルへと追記。

という、なんとも初歩的でアナログな方法をとっていました。

初期の頃はこれでも問題なく、更新作業はできていたのですが
徐々にNetflixのラインナップが増えてくるとテキストファイルとの整合性チェックに
膨大な時間が掛かるようになってきたわけです。そこで改善を検討し半自動化へと踏み込んだのです。

とはいえ、私個人は必要なときに「Google Apps Script」(GAS)を勉強する位で
プログラマとしての知識があるわけではありません。過去にこんなのを作っていたりするぐらいです。

Googleアナリティクスの情報をチャットワークへと流す備忘録

なので、不具合やもっと簡易的な方法。というのもあるでしょうが……。
その点は参考程度でご注意して利用頂ければと思います。

GASにてHTML形式で出力するための準備と設計思考

それではまず準備から。

「Google Apps Script」(GAS)は「Googleドライブ」上に個別に設計することもできますが
どのスプレッドシートを読み込んで、などの設定が面倒。
今回は「Googleスプレッドシート」内にある「ツール」>「スクリプトエディタ」にて
Googleスプレッドシート内に書き込む「Google Apps Script」(GAS)を作成します。

google_gas_01

どうして内部に「Google Apps Script」(GAS)を組み込んでいるのか、というと。
スプレッドシートをジャンル毎に分けてリスト化する設計を考えており
外部に設けてしまうとスプレッドシートを指定するのが面倒そうだったことが要因。
内部に持つことで、処理を現状使っているシート、というプログラムで共有化させるのが目的。
(プログラムの共有化ができれば、後はコピーにて複製が簡単)

HTML形式に変換しshowにて出力を実現するソースコード

実現する機能。としては

スプレッドシート内の「メニュー」に項目を追加し
そのボタンを押す事で、リスト情報を元にしたHTML形式へと変換した情報を出力させます。

というものになる。それではソースをまずはどうぞ


function outputSheetToCsvFile() {

  /* スプレッドシートのシートを取得と準備 */
  var mySheet=SpreadsheetApp.getActiveSheet(); //シートを取得
  var rowSheet=mySheet.getDataRange().getLastRow(); //シートの使用範囲のうち最終行を取得

  var strAllBody="";
  var j=0;
 
  for(var i=1;i<=rowSheet;i++){
    var strTitle=mySheet.getRange(i,1).getValue(); //タイトル
    var strID=mySheet.getRange(i,2).getValue(); //ID
    var strYear=mySheet.getRange(i,3).getValue(); //年度
    var strSeries=mySheet.getRange(i,4).getValue(); //シリーズ
    var strTime=mySheet.getRange(i,5).getValue(); //時間
    var strCate1=mySheet.getRange(i,6).getValue(); //カテゴリ1
    var strCate2=mySheet.getRange(i,7).getValue(); //カテゴリ2
    var strRat=mySheet.getRange(i,8).getValue(); //レーティング
    var strOri=mySheet.getRange(i,9).getValue(); //オリジナル
   
    var strBody="";
    var strSpan="";
   
    //アイコン選択
    if(strRat=="キッズOK"){
      strSpan= "&lt;span class=reting_kizok>";
    }else if(strRat=="G"){
      strSpan= "&lt;span class=reting_g>";
    }else if(strRat=="大人向け"){
      strSpan= "&lt;span class=reting_otona>";
    }else if(strRat=="PG12"){
      strSpan= "&lt;span class=reting_r12>";
    }else if(strRat=="R15+"){
      strSpan= "&lt;span class=reting_r15>";
    }else if(strRat=="R18+"){
      strSpan= "&lt;span class=reting_r18>";
    }

    //タイトル
    strBody=strBody + "「" + strTitle + "」<span class=komoji>(";
    
    //年代
    strBody=strBody + strYear + ")<span>";
    
    //シリーズ・上映時間
    if(strSeries!=""){
      strBody=strBody + " 総シリーズ" + strSeries + "";
    }
    if(strTime!=""){
      strBody=strBody + " 上映時間:" + strTime + "";
    }
    
    if(strOri!=""){
      strBody=strBody + "&lt;span CLASS=font_red>(Netflixオリジナル)&lt;/span>";
    }
       
    //SPAN閉じ
    if(strSpan!=""){
      strBody=strSpan + strBody + "&lt;/span>";
    }
    strBody=strBody + "<br>";
    
    strAllBody=strAllBody + strBody;
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var html = HtmlService.createHtmlOutput(strAllBody);
  ss.show(html);
}

function onOpen() {
  // メニューバーにカスタムメニューを追加
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  var entries = [];
  entries.push({name: "HTML書き出し", functionName: "outputSheetToCsvFile"});
  spreadsheet.addMenu("CUSTOM MENU", entries);
};

という風になっている。

html書き出しの詳しい説明と機能解説

それぞれの詳しい説明を行わせて頂きます。

[function onOpen()]というのは、スプレッドシートを開いた時に自動的に実行されるプログラム。
この部分で「メニュー」部分に項目を表示させるという命令を含んでおり、メニューボタンを生成します。
[entries.push]にて追加する項目名、参照先関数を記述。
[spreadsheet.addMenu]にてメニューに表示し項目を追加します。

本題になるのは[outputSheetToHtml()]という関数内
とはいえ、やっていることは正直簡単なもの

4行~5行はシートを取得して、シート上の最終行を確認。
10行「For文」にて繰り返しリストからデータを読み込み
Stringへと追加して行く処理になります。
そして、67行にて出力先として「showオブジェクト」を利用しています。
……ですが、ここに修正の余地があるがそれは最後に。

注意点としては、HTML形式をそのまま「<td>」のように記述してしまうと
スプレッドシート上に表示する際に、タグ情報と認識され非表示対象となってしまいます。
そのため「<」を「<」という形に変換して無効化することで回避しましょう。

【出力結果】
google_gas_02

これで実行すると、スプレッドシート内のリスト情報を元にHTML形式に整形したテキストが表示されます。
あとはコピーを行いWordpressへと貼り付けて完成です。

意外と作ってみると
「GoogleスプレッドシートのリストをHTML形式で書き出すGAS」
というのは簡単だったのですが、参考にさせて頂いたのが「CSVファイルで出力」であるとともに
そこで使われていたのが今は使用できない「DocsList」だったりして
別ファイルに保存するためにはどうすれば良いのか。などなど
遠回りして四苦八苦した結果、ファイル保存しなくても良いんじゃない?という逆転の発想にて現状に行き着きました。

とは言え、実はそこに落とし穴があったようです。
というのもリストができた後にプログラムを実行してみると、あまりにもリスト数が多いため
メモリーオーバーを引き起こしてしまい異常終了してしまうのです。
その問題点が先ほど「修正の余地」と紹介した「showオブジェクト」です。

リストの数がそれほどないような状況の場合には
「showオブジェクト」でも十二分に対応することができますが
リスト数が多くなりすぎた場合、上手く行きませんでした。

改善の余地としては「for文」が長すぎるせいかもしれません。
リストが多くメモリを圧迫するのなら、定期的に「showオブジェクト」で出力できれば
改善することもできるかもしれません。

Google Apps Scriptの改善でHTML出力先を「Googleドキュメント」へ

上記で書いていたように、スプレッドシートの「show」を使う場合
どうやら出力させるリストが多すぎると異常終了してしまう現象がありました。
そこで、もっと簡単にするべく取り組んだのがこちらのプログラムです。
今回は「show」を使うのではなく、出力先を「Googleドキュメント」へと修正させて頂きました。
難しいかな、と思ったのですが、それほど難しくなく簡単に改善できましたので
参考にしていだければと思います。

function outputSheetToCsvFile() {

  /* スプレッドシートのシートを取得と準備 */
  var mySheet=SpreadsheetApp.getActiveSheet(); //シートを取得
  var rowSheet=mySheet.getDataRange().getLastRow(); //シートの使用範囲のうち最終行を取得

  var doc = DocumentApp.openById("※ID指定※");
  doc.clear();
  
  // シートの全ての行について社名、姓名を差し込みログに表示
  //for(var i=1;i<=rowSheet;i++){
 
  for(var i=1;i<=rowSheet;i++){
    var strTitle=mySheet.getRange(i,1).getValue(); //タイトル
    var strID=mySheet.getRange(i,2).getValue(); //ID
    var strYear=mySheet.getRange(i,3).getValue(); //年度
    var strSeries=mySheet.getRange(i,4).getValue(); //シリーズ
    var strTime=mySheet.getRange(i,5).getValue(); //時間
    var strCate1=mySheet.getRange(i,6).getValue(); //カテゴリ1
    var strCate2=mySheet.getRange(i,7).getValue(); //カテゴリ2
    var strRat=mySheet.getRange(i,8).getValue(); //レーティング   
    var strOri=mySheet.getRange(i,9).getValue(); //オリジナル
    
    var strBody="";
    
    //タイトル
    strBody=strBody + "「" + strTitle + "」<span class=komoji>(";
    
    //年代
    strBody=strBody + strYear + ")<span>";
    
    //シリーズ・上映時間
    if(strSeries!=""){
      strBody=strBody + " 総シリーズ" + strSeries + "";
    }
    if(strTime!=""){
      strBody=strBody + " 上映時間:" + strTime + "";
    }
    if(strOri!=""){
      strBody=strBody + "<span CLASS=font_red>(Netflixオリジナル)</span>";
    }

    //アイコン選択
    if(strRat=="キッズOK"){
      strBody=strBody + " <span class=reting_kizok>キッズOK</span>";
    }else if(strRat=="G"){
      strBody=strBody + " <span class=reting_g>G</span>";
    }else if(strRat=="大人向け"){
      strBody=strBody + " <span class=reting_otona>大人向け♥</span>";
    }else if(strRat=="PG12"){
      strBody=strBody + " <span class=reting_rg12>PG12</span>";
    }else if(strRat=="R15+"){
      strBody=strBody + " <span class=reting_r15>R15+</span>";
    }else if(strRat=="R18+"){
      strBody=strBody + " <span class=reting_r18>R18+</span>";
    }

    var p = doc.appendParagraph("");
    var t1 = p.appendText(strBody); 
  }
}

function onOpen() {
  // メニューバーにカスタムメニューを追加
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  var entries = [];
  entries.push({name: "HTML書き出し", functionName: "outputSheetToCsvFile"});
  spreadsheet.addMenu("CUSTOM MENU", entries);
};

以上となります。共通する部分の解説は省くとして、追加した部分などを解説したいと思います。

Googleドキュメントを扱う場合、「Paragraph」が重要になってきます。
簡単に言うと、一行ごとに挿入するテキストを指定します。
他にもフォントの大きさを指定したり、色付けを行ったりを設定することができます。
今回はそういうものは必要ないので、簡単に「Paragraph」へと追加することだけを考えています。

7行にて出力先である「Googleドキュメント」を指定しています。
※ID指定※」はそれぞれGoogleドキュメントのIDコードを入力して下さい。
8行の「clear()」にてGoogleドキュメント内のテキストをすべて削除。
つまり、実行されるたびにドキュメントが消える設定になっています。
67~68行で、「Paragraph」を追加し
追加した「Paragraph」内へと生成したHTMLリストを挿入。

という動作を行っています。

まあ、これでも実は問題がありましてスプレッドシートの行数が多い。
私の場合には「2000行」以上になるとタイムアウトしてしまいます。
どうやら処理に時間が掛かりすぎているからなのでしょう。
現状で回避方法としては、for文で上から下まで回すのではなく
途中で中継地点を設けて、ぶつ切りにしながら出力することで対応をおこなっています。

先に紹介した「show」よりも長いリストを利用できるのですが……。
プログラム的問題も考えられるのでおいおい時間を見て詳しく調査などできればと思います。

 - ヨモヤマ