折れ線グラフ

Google Apps Scriptで作成する

function doGet() {

    //スプレッドシートのIDとシート名を指定

    var sheet = SpreadsheetApp.openById("0AjN10lCzkUqkdHJqaUxkaUdyOUR3VGQwM3VHWTdWcGc").getSheetByName("sheet1");

    //データテーブルを作成し、カラムを設定する

    var dataTable = Charts.newDataTable();

    dataTable.addColumn(Charts.ColumnType.STRING, "年度");

    dataTable.addColumn(Charts.ColumnType.NUMBER, "売上");

    dataTable.addColumn(Charts.ColumnType.NUMBER, "費用");

dataTable.addColumn(Charts.ColumnType.NUMBER, "収益");

  //特定のレンジを配列として取得

  var varValues = sheet.getRange("A2:D10").getValues();

    //配列データをテーブルに格納

    var counter = Number(0);

    for(var i in varValues) {

      var row = [];

      row.push(String(varValues[counter][0]));

      row.push(Number(varValues[counter][1]));

      row.push(Number(varValues[counter][2]));

row.push(Number(varValues[counter][3]));

      dataTable.addRow(row);

      counter++;

    var data = dataTable.build();

    //チャートを作成

    var chart = Charts.newLineChart()

       .setTitle('費用と売上')

       .setXAxisTitle('年度')

       .setYAxisTitle('単位 (円)')

       .setDimensions(700, 600)

       .setDataTable(data)

       .setColors(["#7FFFD4","#BA55D3"])

       .build();

    //チャートを表示する

    return UiApp.createApplication().add(chart);

<?xml version="1.0" encoding="UTF-8" ?>

<Module>

<ModulePrefs title="sample1" />

<Content type="html">

<![CDATA[

<html xmlns="http://www.w3.org/1999/xhtml">

  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>

    <title>

      Google Visualization API Sample

    </title>

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

    <script type="text/javascript">

//corechartのパッケージをロード

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

      google.setOnLoadCallback(initialize);

      function initialize() {

        // The URL of the spreadsheet to source data from.

        var query = new google.visualization.Query(

            'https://docs.google.com/a/hmw.gr.jp/spreadsheet/ccc?key=0AjN10lCzkUqkdHJqaUxkaUdyOUR3VGQwM3VHWTdWcGc&usp=drive_web#gid=0');

        query.send(draw);

      function draw(response) {

        if (response.isError()) {

          alert('Error in query');

        var data = response.getDataTable();

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

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

var options = {

title: '売上分析',

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

legend: {fontSize: 11},

dataOpacity: '0.4',

hAxis: {title: '年度'},

vAxis: {title: '金額（円）'},

height: '600',

width: '1200'

};

//グラフの描画

        chart.draw(data, options);

}

    </script>

  </head>

  <body style="font-family: Arial;border: 0 none;">

    <div id="chart_div"></div>

  </body>

</html>

]]>

</Content>

</Module>

Google Sites

Report abuse