2018年11月24日 / 最終更新日時 : 2022年7月12日 officeの杜 Google

Google Apps Scriptでローソク足チャートを作る【GAS】

G Suiteも数多くのアップデートを重ねて、標準でできる事が増えてきました。強力なスクリプト環境のおかげで様々な自動化だけでなく、VBAでは難しいインターネットを跨いだ連携や自動化も可能です。

今回はそんな標準機能と外部ライブラリを利用したグラフの生成の１つである「ローソク足チャート」を作ってみようと思います。外部ライブラリといってもGoogle提供のVisualization APIを利用したもので、両者それぞれ用途が少々異なります。目的に応じて作ると良いでしょう。

※ローソク足チャートは、株価や外国為替などの取引データの表示では最もよく利用されている分析用のチャートです

1 今回使用するスプレッドシートや資料
2 ソースコード
- 2.1 GAS側コード
- 2.2 HTML側コード
3 実行と結果
4 関連リンク

今回使用するスプレッドシートや資料

ソースコード

GAS側コード

//起動時に表示するメニュー
function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('<img draggable="false" data-mce-resize="false" data-mce-placeholder="1" data-wp-emoji="1" class="emoji" alt="▶" src="https://s.w.org/images/core/emoji/11/svg/25b6.svg">ローソク足')
      .addItem('セットアップ', 'setup')
      .addSeparator()
      .addItem('動的チャートを表示', 'candleman')
      .addItem('通常チャート表示生成', 'makeCandleChart')
      .addToUi();
}

//セットアップ
function setup(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetId = sheet.getId();
  var Properties = PropertiesService.getScriptProperties();
  var spfile = Properties.setProperty("sheetid",sheetId);
  
  SpreadsheetApp.getUi().alert("セットアップ完了");
}

//スプレッドシートからデータを取得する
function datamanrev(){
  //シートのIDを取得する
  var Properties = PropertiesService.getScriptProperties();
  var ssid = Properties.getProperty("sheetid");

  //シートlデータを取得する
  var sheet = SpreadsheetApp.openById(ssid);
  var ss = sheet.getSheetByName("data");
  var dataman = ss.getRange("A2:E").getValues();
  
  //シートデータを返す
  return JSON.stringify(dataman);
}

//ダイアログとしてローソク足チャートを表示
//Visualization APIを使用
function candleman() {
  var html = HtmlService.createHtmlOutputFromFile('candle')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setWidth(630)
    .setHeight(400);

 SpreadsheetApp.getUi() 
    .showModalDialog(html, 'ドル円日足チャート');
}

//外部貼り付け用として表示
function doGet(){
  var html = HtmlService.createHtmlOutputFromFile('candle')
            .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
            .addMetaTag("viewport", "initial-scale=1.0");
  return html;
}

//通常のローソク足チャートを生成する
function makeCandleChart(){
  //生成場所を指定
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target = ss.getSheetByName("Graph");   //生成するシート
  var sheet = ss.getSheetByName("data");   //グラフ用データ
  
  //チャート範囲を指定
  var range = sheet.getRange("A1:E20");　　//20ポイントだけ取ってみる
  
  //チャートオプション作成実行
  var chart = target.newChart()
    //ローソク足チャート
    .setChartType(Charts.ChartType.CANDLESTICK)
    .addRange(range)
    .setPosition(1, 1, 20, 20)   //グラフの生成位置を指定
    .setOption('series.0.color', '#0000FF')    //グラフの色を指定する
    .setOption('title', 'ドル円日足チャート')    //グラフのタイトルを指定する
    .setOption('height', 400)   //グラフの高さ
    .setOption('width', 550)   //グラフの横幅
    .build();
  
  //チャートを作成
  target.insertChart(chart);
}

//起動時に表示するメニュー

function onOpen(e) {

var ui = SpreadsheetApp.getUi();

ui.createMenu('<img draggable="false" data-mce-resize="false" data-mce-placeholder="1" data-wp-emoji="1" class="emoji" alt="▶" src="https://s.w.org/images/core/emoji/11/svg/25b6.svg">ローソク足')

.addItem('セットアップ', 'setup')

.addSeparator()

.addItem('動的チャートを表示', 'candleman')

.addItem('通常チャート表示生成', 'makeCandleChart')

.addToUi();

}

//セットアップ

function setup(){

var sheet = SpreadsheetApp.getActiveSpreadsheet();

var sheetId = sheet.getId();

var Properties = PropertiesService.getScriptProperties();

var spfile = Properties.setProperty("sheetid",sheetId);

SpreadsheetApp.getUi().alert("セットアップ完了");

}

//スプレッドシートからデータを取得する

function datamanrev(){

//シートのIDを取得する

var Properties = PropertiesService.getScriptProperties();

var ssid = Properties.getProperty("sheetid");

//シートlデータを取得する

var sheet = SpreadsheetApp.openById(ssid);

var ss = sheet.getSheetByName("data");

var dataman = ss.getRange("A2:E").getValues();

//シートデータを返す

return JSON.stringify(dataman);

}

//ダイアログとしてローソク足チャートを表示

//Visualization APIを使用

function candleman() {

var html = HtmlService.createHtmlOutputFromFile('candle')

.setSandboxMode(HtmlService.SandboxMode.IFRAME)

.setWidth(630)

.setHeight(400);

SpreadsheetApp.getUi()

.showModalDialog(html, 'ドル円日足チャート');

}

//外部貼り付け用として表示

function doGet(){

var html = HtmlService.createHtmlOutputFromFile('candle')

.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)

.addMetaTag("viewport", "initial-scale=1.0");

return html;

}

//通常のローソク足チャートを生成する

function makeCandleChart(){

//生成場所を指定

var ss = SpreadsheetApp.getActiveSpreadsheet();

var target = ss.getSheetByName("Graph"); //生成するシート

var sheet = ss.getSheetByName("data"); //グラフ用データ

//チャート範囲を指定

var range = sheet.getRange("A1:E20");　　//20ポイントだけ取ってみる

//チャートオプション作成実行

var chart = target.newChart()

//ローソク足チャート

.setChartType(Charts.ChartType.CANDLESTICK)

.addRange(range)

.setPosition(1, 1, 20, 20) //グラフの生成位置を指定

.setOption('series.0.color', '#0000FF') //グラフの色を指定する

.setOption('title', 'ドル円日足チャート') //グラフのタイトルを指定する

.setOption('height', 400) //グラフの高さ

.setOption('width', 550) //グラフの横幅

.build();

//チャートを作成

target.insertChart(chart);

}

今回、動的チャートおよび外部貼り付け用チャートでは、スプレッドシートのdataシートの全てを範囲として含めています。
一方通常貼り付けチャート(makeCandleChart)はチャート範囲として20ポイント分だけを指定しています（全部を指定するともはやローソク足としてはデータが多すぎ）
外部貼り付け用では、Google Sites以外でも貼り付けられるように、.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)を指定しています。
外部貼り付け用および動的チャート用にデータのセットアップ項目を用意し、シートのIDをスクリプトプロパティに格納しています。
通常チャートでは、setDimensionsが使えないので、高さと幅はsetOptionで指定します。
通常チャートでのチャートタイプは、CANDLESTICKを選択しています。

HTML側コード

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script type="text/javascript">
  google.load('visualization', '1.0', {'packages':['corechart', 'table', 'gauge', 'controls']});
  google.setOnLoadCallback(test);
  
  //GAS側からデータを取得する
  function test(){
    google.script.run.withSuccessHandler(onSuccess).datamanrev();
  }
  
  //画像ダウンロード用のURLをセットする
  function seturl(url){
    document.getElementById("ss").href = url;
  }
  
  //GAS側からのデータを加工する
  function onSuccess(dataman) {
    var tempdata = JSON.parse(dataman);
    var numRows = tempdata.length
    
    //ダッシュボードを用意する
    var dashboard = new google.visualization.Dashboard(
        document.getElementById('chartRangeFilter_dashboard_div'));
    
    //レンジフィルターを用意する
    var control = new google.visualization.ControlWrapper({
      'controlType': 'ChartRangeFilter',
      'containerId': 'chartRangeFilter_control_div',
      'options': {
        // Filter by the date axis.
        'filterColumnIndex': 0,
        'ui': {
          'chartType': 'LineChart',
          'chartOptions': {
            'chartArea': {'width': '100%'},
            'hAxis': {'baselineColor': 'none'}
          },
          'chartView': {
            'columns': [0, 3]
          },
          //1日/ms = 24 * 60 * 60 * 1000 = 86,400,000
          'minRangeSize': 86400000
        }
      },
      
      //初期表示する日付範囲の指定
      'state': {'range': {'start': new Date(2012, 1, 9), 'end': new Date(2012, 2, 20)}}
    });
    
    //ローソク足チャートを用意する
    var chart = new google.visualization.ChartWrapper({
      'chartType': 'CandlestickChart',
      'containerId': 'chartRangeFilter_chart_div',
      'options': {
        'chartArea': {'height': '80%', 'width': '90%'},
        'hAxis': {'slantedText': false},
        'legend': {'position': 'none'}
      },
      'view': {
        'columns': [
          {
            'calc': function(dataTable, rowIndex) {
              return dataTable.getFormattedValue(rowIndex, 0);
            },
            'type': 'string'
          }, 1, 2, 3, 4]
      }
    });

    //データを準備する
    var gdata = new google.visualization.DataTable();
        gdata.addColumn('date', '日付');
        gdata.addColumn('number', '最安値');
        gdata.addColumn('number', '開始値');
        gdata.addColumn('number', '終値');
        gdata.addColumn('number', '最高値');

    //データテーブルにデータを流し込む
    var cnt = numRows -1;
    for(i=1;i<cnt;i++){
      var row = []; 
      row.push(new Date(tempdata[i][0]));
      row.push(tempdata[i][1]);　　　//最安値を指定
      row.push(tempdata[i][2]);　　　//開始値を指定
      row.push(tempdata[i][3]);　　　//終値を指定
      row.push(tempdata[i][4]);　　　//最高値を指定
      gdata.addRow(row);
    }
    
    //画像ダウンロード用のエレメントを取得
    var imgurl = document.getElementById("imgurl");
    
    //画像ダウンロード用URLをイベント登録
    google.visualization.events.addListener(chart, 'ready', function() {
            var img = chart.getChart().getImageURI();
            seturl(img);
    });    
    
    //ダッシュボードに描画する
    dashboard.bind(control, chart);
    dashboard.draw(gdata);
  }
</script>

<div id="chartRangeFilter_dashboard_div" style="border: 1px solid #ccc">
  <div id="chartRangeFilter_chart_div" style="width: 600px; height: 300px;"></div>
  <div id="chartRangeFilter_control_div" style="width: 600px; height: 50px;"></div>
</div>
<p><a href="" id="ss" download="file.png"><b>画像として保存</b></a></p>

100

101

102

103

104

105

106

107

108

109

110

google.load('visualization', '1.0', {'packages':['corechart', 'table', 'gauge', 'controls']});

google.setOnLoadCallback(test);

//GAS側からデータを取得する

function test(){

google.script.run.withSuccessHandler(onSuccess).datamanrev();

}

//画像ダウンロード用のURLをセットする

function seturl(url){

document.getElementById("ss").href = url;

}

//GAS側からのデータを加工する

function onSuccess(dataman) {

var tempdata = JSON.parse(dataman);

var numRows = tempdata.length

//ダッシュボードを用意する

var dashboard = new google.visualization.Dashboard(

document.getElementById('chartRangeFilter_dashboard_div'));

//レンジフィルターを用意する

var control = new google.visualization.ControlWrapper({

'controlType': 'ChartRangeFilter',

'containerId': 'chartRangeFilter_control_div',

'options': {

// Filter by the date axis.

'filterColumnIndex': 0,

'ui': {

'chartType': 'LineChart',

'chartOptions': {

'chartArea': {'width': '100%'},

'hAxis': {'baselineColor': 'none'}

'chartView': {

'columns': [0, 3]

//1日/ms = 24 * 60 * 60 * 1000 = 86,400,000

'minRangeSize': 86400000

}

//初期表示する日付範囲の指定

'state': {'range': {'start': new Date(2012, 1, 9), 'end': new Date(2012, 2, 20)}}

});

//ローソク足チャートを用意する

var chart = new google.visualization.ChartWrapper({

'chartType': 'CandlestickChart',

'containerId': 'chartRangeFilter_chart_div',

'options': {

'chartArea': {'height': '80%', 'width': '90%'},

'hAxis': {'slantedText': false},

'legend': {'position': 'none'}

'view': {

'columns': [

{

'calc': function(dataTable, rowIndex) {

return dataTable.getFormattedValue(rowIndex, 0);

'type': 'string'

}, 1, 2, 3, 4]

}

});

//データを準備する

var gdata = new google.visualization.DataTable();

gdata.addColumn('date', '日付');

gdata.addColumn('number', '最安値');

gdata.addColumn('number', '開始値');

gdata.addColumn('number', '終値');

gdata.addColumn('number', '最高値');

//データテーブルにデータを流し込む

var cnt = numRows -1;

for(i=1;i<cnt;i++){

var row = [];

row.push(new Date(tempdata[i][0]));

row.push(tempdata[i][1]);　　　//最安値を指定

row.push(tempdata[i][2]);　　　//開始値を指定

row.push(tempdata[i][3]);　　　//終値を指定

row.push(tempdata[i][4]);　　　//最高値を指定

gdata.addRow(row);

}

//画像ダウンロード用のエレメントを取得

var imgurl = document.getElementById("imgurl");

//画像ダウンロード用URLをイベント登録

google.visualization.events.addListener(chart, 'ready', function() {

var img = chart.getChart().getImageURI();

seturl(img);

});

//ダッシュボードに描画する

dashboard.bind(control, chart);

dashboard.draw(gdata);

}

</script>

</div>

<p><a href="" id="ss" download="file.png"><b>画像として保存</b></a></p>

このコードはダイアログ用および外部公開用で共用しています。
おまけとして、表示されているグラフを画像としてダウンロード出来るようにダウンロード用のURLを生成して画像として保存にaddListnerしています。
外部ライブラリとして、Visualization APIをロードし、testがまず実行されGAS側のdatamanrevが実行されます。
返り値をOnSuccessで受け取り、移動用のダッシュボードコントロールの追加、レンジフィルターの追加、ロードするチャートオプションの指定を行っています。
データは変数のままではなく、dataTableを用意して格納してからChartBuilderに引き渡しています。
今回はチャート移動用にダッシュボードを用意し、その上にチャートを描画している為、よりインタラクティブなグラフを利用出来ます。

実行と結果

今回のスクリプトは全部で３種類のタイプが含まれています。「スプレッドシート上にグラフを表示」「ダイアログに外部ライブラリでグラフを表示」「外部貼り付け用にグラフを表示」となっています。以下の手順でそれぞれを使えるようにしましょう。

セットアップ

スプレッドシートのメニューより「ローソク足」⇒「セットアップ」をクリックします。この実行によって、スプレッドシートのIDがスクリプトプロパティに格納されます。必ず実行するようにしてください。実行することでグラフが表示されるようになります。

ウェブアプリケーションとして導入

こちらは、グラフを外部サイトなどに貼り付ける（Google Siteや自前のブログ等）場合には、この作業が必要です。今回は特にGoogle Site以外にも貼り付けられるように、.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)をオプション追加しています。

スクリプトエディタを開く
メニューより「公開」⇒「ウェブアプリケーションとして導入」をクリック
プロジェクトバージョンを新規作成に。コードを修正する度に新規にしないと結果が反映しないので注意！！
次のユーザとしてアプリケーションを実行では「自分」を選ぶ（各ユーザ毎にも出来るけれど、スプレッドシートへのアクセス権限も付与してあげないといけない）
アプリケーションにアクセス出来るユーザでは「全員（匿名ユーザを含む）」としておく。G Suiteの設定によっては匿名実行は出来ません。
更新ボタンを押す。
現在のウェブアプリケーションのURLを控えておく。最後がexecになっているはず（devはデバッグ用なので外部に貼り付けないように）。

図：ウェブアプリケーションとして導入画面

生成したURLを開くと、doGetの内容が実行されてグラフが表示されます。外部に貼り付ける場合には、iFrameタグで貼り付ける事が可能です。iframeで貼り付ける場合のコードと貼り付けてみた結果は以下の通り。widthだけはグラフの横サイズよりちょっとだけ多めに数値を取って置きます。

<iframe id="candlestick"
    title="ローソク足チャート"
    width="630"
    height="400"
    src="ここに生成したウェブアプリケーションURLを貼り付ける">
</iframe>

title="ローソク足チャート"

width="630"

height="400"

src="ここに生成したウェブアプリケーションURLを貼り付ける">

</iframe>

<br />

図：実際に貼り付けてみたグラフ

動的チャートを表示

動的チャートは上記のグラフをスプレッドシート上のダイアログ内で表示させる為のものです。メニューより「ローソク足」⇒「動的チャートを表示」を実行するだけです。

図：ぐりぐり動くローソク足チャートが表示出来ます。

通常チャート表示

こちらは通常のGoogleスプレッドシートの機能を利用したチャートなので、動的なチャートではありません。スプレッドシートのデータが変わればグラフの内容も変化する点は同じです。メニューより「ローソク足」⇒「通常チャートの表示」で、Graphシートにグラフが生成されます。

ちなみにこのグラフも外部に貼り付ける事が可能です。

右上の「︙」をクリックして、「グラフを公開」をクリック
ウェブに公開ダイアログにて、埋め込むを選択
公開ボタンを押す。iframeのタグが表示されるので、コピーしておく
ブログなどにコードを貼り付ける。
もちろん、スプレッドシートのデータが変わるとグラフの中身も変わる。ただし、細かい設定が出来ないので、特に横のサイズで苦労する事も（このブログのように・・・）
グラフのサイズはスプレッドシート上のサイズを変更させてから公開すると、変更が可能です。

図：ウェブに公開オプション

図：実際にウェブに公開で貼り付けたインタラクティブグラフ

図：標準機能で作ったローソク足チャート

Google Apps Scriptでローソク足チャートを作る【GAS】

今回使用するスプレッドシートや資料

ソースコード

GAS側コード

HTML側コード

実行と結果

セットアップ

ウェブアプリケーションとして導入

動的チャートを表示

通常チャート表示

関連リンク

コメントを残すコメントをキャンセル

Google Apps ScriptでLINEに通知を送る【GAS】

Google Apps Scriptで6分の壁（タイムアウト）を突破する【GAS】

今回使用するスプレッドシートや資料

ソースコード

GAS側コード

HTML側コード

実行と結果

セットアップ

ウェブアプリケーションとして導入

動的チャートを表示

通常チャート表示

関連リンク

コメントを残す コメントをキャンセル

Google Apps ScriptでLINEに通知を送る【GAS】

Google Apps Scriptで6分の壁（タイムアウト）を突破する【GAS】

コメントを残すコメントをキャンセル