0

I am trying to develop a small web app which query the multiple google spreadsheets and make the graphs on the same page. I can query the single spreadsheet and chart the matching data like this and it is working fine.

<html>
  <head>
<title>
  Test
</title>

<script src="http://www.google.com/jsapi"></script>
<script src="http://code.jquery.com/jquery-2.0.3.min.js"></script>

<script type="text/javascript" src="https://www.google.com/jsapi">
</script>
<script type="text/javascript">
  google.load('visualization', '1', {'packages': ['table', 'controls', 'corechart']});
  google.setOnLoadCallback(initialize);

  function initialize() {

 var urlMonth = 'https://docs.google.com/spreadsheets/d/1y5MgFR67kn1-GHbmeIi6wuC5hmP10x4O8vAs5RWD8Sw/edit#gid=0'   

var queryStringMonthly = encodeURIComponent("select A, B, C, D, E, F, G, H, I GROUP BY A LABEL A 'Gene' ");
var queryMonthCurrent = new google.visualization.Query(urlMonth+ 
queryStringMonthly);
queryMonthCurrent.send(megaData); 

  }


 function megaData(monthData) {
    var monthData_table = monthData.getDataTable(firstRowIsHeader = true);

    var monthData_tablePivot = new google.visualization.DataTable();
     monthData_tablePivot.addColumn('string');
     monthData_tablePivot.addColumn('number');
     monthData_tablePivot.addColumn({type: 'string', label: 'Gene', role: 'annotation'}); 

    var  newRows = []; //

     //iterate through each row
     for (i = 0; i < monthData_table.getNumberOfRows(); i ++) {
      var issue = monthData_table.getValue(i, 0);
      //iterate through each column
      for (j = 1; j < monthData_table.getNumberOfColumns(); j ++ ){
        var newRow = []; 
        rep = monthData_table.getColumnLabel(j);
        newRow.push(rep);
        newRow.push(monthData_table.getValue(i, j));
        newRow.push(issue);
        newRows.push(newRow); //push each newRow to newRows
      }
     }

    monthData_tablePivot.addRows( newRows);

    // Create a dashboard.
    var dashboard = new google.visualization.Dashboard(
        document.getElementById('dashboard_div4'));

   // Create filter
    var issueFilter = new google.visualization.ControlWrapper({
      'controlType': 'StringFilter',
      'containerId': 'filter_div4',
      'options': {
        'filterColumnLabel': 'Gene', 
        'ui': {
        'allowMultiple': false,
        'allowNone': false, 
        }
      },
      //Set default filter value
      'state': {'selectedValues': [monthData_table.getValue(0 , 1)]} 
    }
    );


    //create chart
    var yearChart = new google.visualization.ChartWrapper({
      'chartType': 'ColumnChart',
      'containerId': 'current_year',
      'options': {
       'legend': 'none',
      'width': 1100,
      'height': 500,
hAxis: {

    textStyle: {
    color: 'black',    // any HTML string color ('red', '#cc00cc')
    fontName: 'Times New Roman', // i.e. 'Times New Roman'
    fontSize: 12, // 12, 18 whatever you want (don't specify px)
    bold: true,    // true or false
    italic: false   // true of false

   },
       'title': 'Gene', titleTextStyle:{color:'black',fontSize: 16,bold: 
true,italic: false}
},

vAxis: {title: 'Expression', titleTextStyle:{color:'black',fontSize: 
16,bold: true,italic: false},
    textStyle: {
    color: 'black',    // any HTML string color ('red', '#cc00cc')
    fontName: 'Times New Roman', // i.e. 'Times New Roman'
    fontSize: 12, // 12, 18 whatever you want (don't specify px)
    bold: true,    // true or false
    italic: false   // true of false
}
},

 //Set the fontsize of labels so they don't show up crazily
          'annotations': {'textStyle': {'opacity': 0},
                         //use 'line' style so to remove the line 
pointer
                         'style': 'point',
                         'stemLength': 0
                        },
      }
    });

    // bind charts and controls to dashboard 
    dashboard.bind(issueFilter, yearChart);
    // Draw the dashboard.
    dashboard.draw(monthData_tablePivot);         
  }

</script>



<style>
.SearchBar input {
 height: 30px;
 width: 400px;
}

</style>
</head>

  <body>
 <!--Div that will hold the dashboard-->
  <center>  
<h2>Seach gene expression</h2>
<div id="dashboard_div4" class="SearchBar" placeholder="Search">
  <div id="filter_div4" > </div></center>
  <div id="current_year" style="width:1100px; height: 300px;">
  </div>
    </div>   

</html>

This repository is very close to my need but I don't know how to query the multiple spreadsheet. My another spreadsheet look like this https://docs.google.com/spreadsheets/d/1vmPmaL78N-Ywz7s1y_VRSvQAZxjacN4mo7uKKrWrwzE/edit#gid=0

Zig Mandel
  • 19,571
  • 5
  • 26
  • 36
pali
  • 195
  • 2
  • 14
  • show us your modification attempts and specific issues with your own attempts. – Zig Mandel Jun 10 '17 at 12:19
  • Thanks for your comment. Actually i modified the answer of@whiteHat [link]https://stackoverflow.com/questions/39322493/google-chart-dashboard-multiple-data as per my need https://jsfiddle.net/prp291/u37hLgk2/. The only problem is that I don't how to connect it to my datasheets and I want charts only after matching the string. – pali Jun 10 '17 at 13:24
  • but the code does not include anything that I would see as an attempt at solving the specific issue you mention. Show us where in the code is the attempt and issues. It appears its more of a javascript issue than google apis since you can already query from a spreadsheet, but maybe you are looking into refactoring the code so the spreadsheet URL is not hardcoded but a function paramenter, and separate the query code from the display code so you can query multiple datasources, then display them all at once? – Zig Mandel Jun 10 '17 at 14:01
  • I am trying to make query like this but it's not working. Thanks. https://jsfiddle.net/prp291/1w9awhpc/ – pali Jun 11 '17 at 03:11

0 Answers0