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

Google Apps Scriptでピラミッドグラフを作ってみた【GAS】

社内の年齢層別の比率を出したり、また人口統計で有名なあのグラフを作るというのは、結構面倒です。そもそも、標準でピラミッドグラフというものを作る機能は、どのスプレッドシートでも用意されているわけではないので、積み上げ横棒グラフに色々手間を書けて実現してるのが現実です。

そのピラミッドグラフをGoogleスプレッドシートで実現しようというのが今回のテーマ。スプレッドシート連携なので、予めvlookupやsum関数などで社内の人事情報などから集計しておいたものを利用します。今回は総務省の人口統計データをそのまま利用していますが、他にも年齢別だけでなく、色々なシーンでこの手のピラミッドグラフは役に立つのではないでしょうか？

図：日本の少子化を表すときにはよく目にします

1 使用するクラスやメソッド・材料
2 実行結果
3 ソースコード
- 3.1 GAS側コード
- 3.2 HTML側コード
4 ポイント
5 関連リンク

使用するクラスやメソッド・材料

実行結果

スプレッドシートを開き、上部にある「▶チャート表示」メニューの中の【ピラミッドグラフを表示】を開くとスプレッドシート上のデータをVisualization APIを使用してピラミッドグラフで表示します。グラフだけ確認する場合にはこちらをクリックすると開きます。

また、【ピラミッドグラフを挿入】を開くと、スプレッドシートのgraphシートへグラフを挿入します。ただし、このコマンドの場合以下の問題点があります。

Visualization APIと違い、vAxis（縦軸）のタイトルがバグで反映しません。手動で入れる分には入れる事が可能です。
Visualization APIと違い、hAxis（縦軸）のDirectionの-1（メモリ反転）が反映しません。マイナス値のまま表示されてしまいます。

ずいぶんと長い間この問題は解決されていないようです。

<br />

ソースコード

GAS側コード

function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('▶チャート表示')
      .addItem('ピラミッドグラフを表示', 'chartman')
      .addItem('ピラミッドグラフを挿入', 'insertPyramid')
      .addToUi();
}

//スプレッドシートのデータを取得する
function datamanrev(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sheet.getSheetByName("pyramid");
  var dataman = ss.getRange("A1:C").getValues();
  return dataman;
}

//外部貼り付けようにウェブアプリケーションとして生成
function doGet(e){
  var html = HtmlService.createHtmlOutputFromFile('pyramid')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);

 return html;  
}

//スプレッドシートに挿入する
function insertPyramid(){
  //スプレッドシートを取得する
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("pyramid");
  var ssc = ss.getSheetByName("graph");
  var data = sheet.getRange("A1:C")
  var headerRow = sheet.getRange(1,1,1,3).getValues()
  
  //チャートを生成
  var chart = sheet.newChart()
                   .asBarChart()  // 縦棒グラフ
                   .setTitle("H25年9月段階人口推計男女別(単位：千人)")
                   .addRange(sheet.getRange('A1:C19'))  // グラフ範囲
                   .setPosition(1, 1, 0, 0)                 // 挿入位置
                   .reverseCategories()　　　//縦軸の値を反転させる
                   .setOption('width',800)
                   .setOption('height',500)                
                   .setOption('chartArea',{left:50, right:50, height:'80%'}) // グラフの位置
                   .setStacked() 
                   .setOption('legend.position','right')
                   .setOption('colors', ['#FF99CC', '#92D050']) // 色指定
                   .setOption('vAxis.direction', -1)        // メモリ反転（動かない）
                   .setOption('hAxis.format',';')  //マイナス値はプラスで表示（動かない）
                   .setOption('useFirstColumnAsDomain', true)  //列Aをラベルとして使用
                   .setOption('domainAxis',{direction:1})
                   .setXAxisTitle('男女別人口')
                   .setYAxisTitle('年齢別')　// Y方向ラベル（動かない）
                   .setOption('series', 
                               {0:{labelInLegend:headerRow[0][1]},
                                1:{labelInLegend:headerRow[0][2]},
                                2:{labelInLegend:headerRow[0][3]}})   //行1を見出しとして使用
                   .build();
  
  //チャートを挿入
  ssc.insertChart(chart);
}

//チャートをダイアログで生成する
function chartman() {
  var html = HtmlService.createHtmlOutputFromFile('pyramid')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setWidth(750)
    .setHeight(520);
 
 SpreadsheetApp.getUi() 
    .showModalDialog(html, 'ピラミッドグラフのテスト');
}

function onOpen(){

var ui = SpreadsheetApp.getUi();

ui.createMenu('▶チャート表示')

.addItem('ピラミッドグラフを表示', 'chartman')

.addItem('ピラミッドグラフを挿入', 'insertPyramid')

.addToUi();

}

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

function datamanrev(){

var sheet = SpreadsheetApp.getActiveSpreadsheet();

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

var dataman = ss.getRange("A1:C").getValues();

return dataman;

}

//外部貼り付けようにウェブアプリケーションとして生成

function doGet(e){

var html = HtmlService.createHtmlOutputFromFile('pyramid')

.setSandboxMode(HtmlService.SandboxMode.IFRAME)

.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);

return html;

}

//スプレッドシートに挿入する

function insertPyramid(){

//スプレッドシートを取得する

var ss = SpreadsheetApp.getActiveSpreadsheet();

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

var ssc = ss.getSheetByName("graph");

var data = sheet.getRange("A1:C")

var headerRow = sheet.getRange(1,1,1,3).getValues()

//チャートを生成

var chart = sheet.newChart()

.asBarChart() // 縦棒グラフ

.setTitle("H25年9月段階人口推計男女別(単位：千人)")

.addRange(sheet.getRange('A1:C19')) // グラフ範囲

.setPosition(1, 1, 0, 0) // 挿入位置

.reverseCategories()　　　//縦軸の値を反転させる

.setOption('width',800)

.setOption('height',500)

.setOption('chartArea',{left:50, right:50, height:'80%'}) // グラフの位置

.setStacked()

.setOption('legend.position','right')

.setOption('colors', ['#FF99CC', '#92D050']) // 色指定

.setOption('vAxis.direction', -1) // メモリ反転（動かない）

.setOption('hAxis.format',';') //マイナス値はプラスで表示（動かない）

.setOption('useFirstColumnAsDomain', true) //列Aをラベルとして使用

.setOption('domainAxis',{direction:1})

.setXAxisTitle('男女別人口')

.setYAxisTitle('年齢別')　// Y方向ラベル（動かない）

.setOption('series',

{0:{labelInLegend:headerRow[0][1]},

1:{labelInLegend:headerRow[0][2]},

2:{labelInLegend:headerRow[0][3]}}) //行1を見出しとして使用

.build();

//チャートを挿入

ssc.insertChart(chart);

}

//チャートをダイアログで生成する

function chartman() {

var html = HtmlService.createHtmlOutputFromFile('pyramid')

.setSandboxMode(HtmlService.SandboxMode.IFRAME)

.setWidth(750)

.setHeight(520);

SpreadsheetApp.getUi()

.showModalDialog(html, 'ピラミッドグラフのテスト');

}

insertPyramid関数がグラフを生成して挿入するコードです。但し、バグにより不完全です。
chartman関数がVisualization APIを利用してグラフを生成します。こちらは素直に動作します。

HTML側コード

  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
    google.load("visualization", "1", {packages:["corechart"]});
    google.setOnLoadCallback(test);
    
    function test(){
      google.script.run.withSuccessHandler(onSuccess).datamanrev();
    }
    
    function onSuccess(data){
      var gdata = google.visualization.arrayToDataTable(data);
      var chart = new google.visualization.BarChart(document.getElementById('series_chart_div'));
      
      //グラフオプションの指定
      var options = {
         isStacked: true,
         chartArea: {left:80,top:30,width:"75%",height:"80%"},
         title: 'H25年9月段階人口推計男女別(単位：千人)',
         height: 450,
         width: 700,
         hAxis: {
             format: ';',
             title: '男女別人口数',
         },
         vAxis: {
             direction: -1,
             title: '年齢層',
         }
      };
 
      var formatter = new google.visualization.NumberFormat({
         pattern: ';'
      });
 
      formatter.format(gdata, 2)
 
      //グラフの描画
      chart.draw(gdata, options);
    }
    </script>
  </head>
  <body>
    <div id="series_chart_div" style="width: 700px; height: 500px;"></div>
  </body>

<head>

google.load("visualization", "1", {packages:["corechart"]});

google.setOnLoadCallback(test);

function test(){

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

}

function onSuccess(data){

var gdata = google.visualization.arrayToDataTable(data);

var chart = new google.visualization.BarChart(document.getElementById('series_chart_div'));

//グラフオプションの指定

var options = {

isStacked: true,

chartArea: {left:80,top:30,width:"75%",height:"80%"},

title: 'H25年9月段階人口推計男女別(単位：千人)',

height: 450,

width: 700,

hAxis: {

format: ';',

title: '男女別人口数',

vAxis: {

direction: -1,

title: '年齢層',

}

};

var formatter = new google.visualization.NumberFormat({

pattern: ';'

});

formatter.format(gdata, 2)

//グラフの描画

chart.draw(gdata, options);

}

</script>

</head>

<body>

</body>

Visualization APIで生成する場合に使用するHTML側のコードです。
現在は、ダイアログ上でしか表示ができませんが、getBlobで取得し、GAS側へ渡せれば画像として貼り付けは可能になると思います。

ポイント

用意するデータの片方（このグラフの場合、女性の人口数）は、マイナス値で作成すること。
setStacked / isStackedで積み上げグラフとして指定すること。これをやらないと、男女の棒が平行にならないで互い違いになるので汚くなる。
vAxisのdirectionオプションで、-1を指定することで、一番下が0才からのスタートになる。
hAxisのformatオプションで、セミコロンを指定することで、男女両方の値はプラス値で表示される。同時にNumberFormat側でもpatternオプションでセミコロンを入れておく。これで完璧。