CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-visualization
Lily
Lily

Reputation: 637

Manipulating Spreadsheet data for visualization

I just started learning Google Apps Script/JavaScript and would like to know how to reshape, manipulate multi-dimensional data from Google Spreadsheets. I've read some posts like this one saying that GAS is not flexible with data manipulation. But the post is a bit dated so I wonder if there are any new changes/additions to GAS functionalities.

Below is the functional code to start with. It can successfully draw the chart below. But what I really want is:

  1. Group by "Weekday" (dayOfWeek(toDate(A))) and "Item" (column B)

  2. Use "Item" as the filter

    enter image description here.

I tried by using PIVOT B, GROUP BY dayOfWeek(toDate(A)), but it returned either One or more participants failed to draw()× error message or unexpected format.

<html>
  <head>
    <title>
      Test
    </title>
    <!--Load the AJAX API-->
    <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 url = 'https://docs.google.com/spreadsheets/d/1_mSbT87MVWOiX2cfKX_x3dgTnToY5ulCWeGGCVn13iQ/gviz/tq?sheet=Sheet1&tq='   

              var queryStringDaily = encodeURIComponent("SELECT dayOfWeek(toDate(A)), sum(C), sum(D), sum(E), sum(F), sum(G), sum(H) GROUP BY dayOfWeek(toDate(A)) LABEL dayOfWeek(toDate(A)) 'Weekday' " ); 

              var queryDaily = new google.visualization.Query(url+ queryStringDaily);
              queryDaily.send(drawDaily);
      }

      function drawDaily(dailyTicket) {

                //prepare data
                var dailyTicket_table = dailyTicket.getDataTable(firstRowIsHeader = true); 

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

                // Create a filter
                var categoryFilter = new google.visualization.ControlWrapper({
                  'controlType': 'CategoryFilter',
                  'containerId': 'filter_div2',
                  'options': {
                    'filterColumnLabel': 'Weekday'
                  }
                });

                //create chart
                var dailyChart = new google.visualization.ChartWrapper({
                  'chartType': 'LineChart',
                  'containerId': 'current_day',
                  'options': {
                    'title': 'Tickets by Rep, Item, and Weekday',
                    'legend': {position: 'right'},
                    //reformat x-axis tickmarks
                    'hAxis': {'viewWindow': {'min': 1.5, 'max': 6.5},
                              'ticks': [//{v: 1, f: 'Sunday'}, 
                                        {v: 2, f: 'Monday'}, 
                                        {v: 3, f: 'Tuesday'}, 
                                        {v: 4, f: 'Wednesday'}, 
                                        {v: 5, f: 'Thursday'}, 
                                        {v: 6, f: 'Friday'}, 
                                        //{v: 7, f: 'Saturday'}
                                        ] 
                            },                    
                  }
                });

                // bind charts and controls to dashboard 
                dashboard.bind(categoryFilter, dailyChart); 

                // Draw the dashboard.
                dashboard.draw(dailyTicket_table);

      }


    </script>
  </head>

  <body>
    <!--Div that will hold the dashboard-->
    <div id="dashboard_div2"></div>
    <!--Divs that will hold each control and chart-->
    <div id="filter_div2"></div>
    <div id="current_day" style="align: center; width: 500px; height: 250px;"></div>

  </body>
</html>

Upvotes: 1

Views: 764

Answers (2)

Lily
Lily

Reputation: 637

I think I had an epiphany moment this morning. After learning and borrowing codes from other posts (noted below in the script), I was able to produce the chart I want.

  1. As @Mogsdad suggested, I moved columns B (Issue) to the beginning of "SELECT" items and later used this column for annotations and filter. The annotation text showed up crazily above every single bar, but I was able to hide them by setting the fontsize of annotated.textStyle to 0.
  2. I also customize the tooltip so Weekday (number format) doesn't show up.

I think this is a work-around to my problem. If you have better suggestions, I would be grateful to learn. 

<html>
  <head>
    <title>
      Test
    </title>
    <!--Load the AJAX API-->
    <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 url = 'https://docs.google.com/spreadsheets/d/1_mSbT87MVWOiX2cfKX_x3dgTnToY5ulCWeGGCVn13iQ/gviz/tq?sheet=Sheet1&tq='   
              
              var queryStringDaily = encodeURIComponent("SELECT B, dayOfWeek(toDate(A)), sum(C), sum(D), sum(E), sum(F), sum(G), sum(H) GROUP BY dayOfWeek(toDate(A)), B LABEL dayOfWeek(toDate(A)) 'Weekday' , B '{role: \"annotation\"}' " ); 

              var queryDaily = new google.visualization.Query(url+ queryStringDaily);
              queryDaily.send(drawDaily);
      }

      function drawDaily(dailyTicket) {

                //prepare data
                var dailyTicket_table = dailyTicket.getDataTable(firstRowIsHeader = true); 
                
                //NEW!!! create tooltip
                //inspired by http://stackoverflow.com/questions/17924826/add-tooltips-to-a-google-line-chart-with-multiple-data-series-with-simplified
                var columns = [2]; // jump to the value columns
                for (var i = 2; i < dailyTicket_table.getNumberOfColumns(); i++) { 
                    columns.push(i);
                    columns.push({
                        type: 'string',
                        properties: {
                            role: 'tooltip'
                        },
                        calc: (function (j) {
                            return function (dt, row) {
                                return dt.getColumnLabel(j)  //+ ': Weekday:' + dt.getValue(row, 1) 
                                + ' Tickets:' + dt.getValue(row, j)
                            } 
                        })(i) 
                    });

                    columns.push({sourceColumn: 0,
                        //calc: getValueAt.bind(undefined, 1)
                        type: "string",
                        role: "annotation",
                        label: "Issue"} );
                }
                columns = [1].concat(columns);

                var view = new google.visualization.DataView(dailyTicket_table);
                view.setColumns(columns)

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

                // Create filter
                var issueFilter = new google.visualization.ControlWrapper({
                  'controlType': 'CategoryFilter',
                  'containerId': 'issue-div',
                  'options': {
                    'filterColumnLabel': 'Issue',
                    'ui': {
                    'allowMultiple': false,
                    'allowNone': false, 
                    }
                  },
                  //Set default filter value
                  'state': {'selectedValues': [dailyTicket_table.getValue(1, 1)]}
                });

                //create chart
                var dailyChart = new google.visualization.ChartWrapper({
                  'chartType': 'ColumnChart',
                  'containerId': 'current_day',
                  'options': {
                      'legend': {position: 'right'},
                      //Set the fontsize of labels so they don't show up crazily
                      'annotations': {textStyle: {'fontsize': 0},
                                     //use 'line' style so to remove the line pointer
                                      style: 'line'},
                      'hAxis': {'viewWindow': {'min': 1.5, 'max': 6.5},
                                'ticks': [//{v: 1, f: 'Sunday'}, 
                                          {v: 2, f: 'Monday'}, 
                                          {v: 3, f: 'Tuesday'}, 
                                          {v: 4, f: 'Wednesday'}, 
                                          {v: 5, f: 'Thursday'}, 
                                          {v: 6, f: 'Friday'}, 
                                          //{v: 7, f: 'Saturday'}
                                          ] 
                               },  
                  }
                });

                // bind charts and controls to dashboard 
                dashboard.bind(issueFilter, dailyChart);

                // Draw the dashboard.
                dashboard.draw(view);
      }

    </script>
  </head>

  <body>
    <!--Div that will hold the dashboard-->
    <div id="dashboard_div2"></div>
    <!--Divs that will hold each control and chart-->
    <div id="filter_div2"></div>
    <div id="issue-div"></div>
    <div id="current_day" style="align: center; width: 1100px; height: 500px;"></div>

  </body>
</html>

Upvotes: 0

Mogsdad
Mogsdad

Reputation: 45720

You can add multiple filters. To do so with your source data:

  1. You need to SELECT the additional text column B and include it in the GROUP BY and optionally LABEL statements.

    var queryStringDaily = encodeURIComponent("SELECT B, dayOfWeek(toDate(A)), sum(C),
          sum(D), sum(E), sum(F), sum(G), sum(H)
          GROUP BY dayOfWeek(toDate(A)), B
          LABEL dayOfWeek(toDate(A)) 'Weekday', B 'Issue' ");

    Notice that we have B first. If we don't, we get the errors you mentioned. That is because LineChart expects a label column then columns of related sequences. Moving the text column to the start of the SELECT side-steps the errors.

  2. Add a filter for "Issue".

    var issueFilter = new google.visualization.ControlWrapper({
  'controlType': 'CategoryFilter',
  'containerId': 'issue-div',
  'options': {
    'filterColumnLabel': 'Issue'
  }
});

  3. Bind the new filter to the dashboard.

    dashboard.bind(issueFilter, dailyChart);

And that's it!

Results

... No, it's not. Because Issue was our first column, it's been picked up as the major axis, which isn't so useful.

Your challenge at this point is to select an appropriate visualization that matches your data - because the LineChart doesn't.

Note: This snippet is runnable.

google.load('visualization', '1', {
  'packages': ['table', 'controls', 'corechart']
});
google.setOnLoadCallback(initialize);

function initialize() {

  var url = 'https://docs.google.com/spreadsheets/d/1_mSbT87MVWOiX2cfKX_x3dgTnToY5ulCWeGGCVn13iQ/gviz/tq?sheet=Sheet1&tq='

  var queryStringDaily = encodeURIComponent("SELECT B, dayOfWeek(toDate(A)), sum(C), sum(D), sum(E), sum(F), sum(G), sum(H) GROUP BY dayOfWeek(toDate(A)),B LABEL dayOfWeek(toDate(A)) 'Weekday', B 'Issue' ");

  var queryDaily = new google.visualization.Query(url + queryStringDaily);
  queryDaily.send(drawDaily);
}

function drawDaily(dailyTicket) {

  //prepare data
  var dailyTicket_table = dailyTicket.getDataTable(firstRowIsHeader = true);
  console.log(JSON.stringify(dailyTicket_table).replace(/\\"/g,"'").replace(/"/g,''));

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

  // Create a filter
  var issueFilter = new google.visualization.ControlWrapper({
    'controlType': 'CategoryFilter',
    'containerId': 'issue-div',
    'options': {
      'filterColumnLabel': 'Issue'
    }
  });

  var categoryFilter = new google.visualization.ControlWrapper({
    'controlType': 'CategoryFilter',
    'containerId': 'weekday-div',
    'options': {
      'filterColumnLabel': 'Weekday'
    }
  });

  //create chart
  var dailyChart = new google.visualization.ChartWrapper({
    'chartType': 'LineChart',
    'containerId': 'linechart-div',
    'options': {
      'title': 'Tickets by Rep, Item, and Weekday',
      'legend': { position: 'right'},
      //reformat x-axis tickmarks
      'hAxis': {
        'viewWindow': { 'min': 1.5, 'max': 6.5},
        'ticks': [ 
          //{v: 1, f: 'Sunday'}, 
          { v: 2, f: 'Monday' },
          { v: 3, f: 'Tuesday' },
          { v: 4, f: 'Wednesday'},
          { v: 5, f: 'Thursday' },
          { v: 6, f: 'Friday' },
          //{v: 7, f: 'Saturday'}
        ]
      },
    }
  });

  // bind charts and controls to dashboard 
  dashboard.bind(issueFilter, dailyChart);
  dashboard.bind(categoryFilter, dailyChart);

  // Draw the dashboard.
  dashboard.draw(dailyTicket_table);

}
<html>
  <head>
    <title>
      Test
    </title>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>

  </head>

  <body>
    <!--Div that will hold the dashboard-->
    <div id="dashboard-div">
      <div id="control-div">
        <div id="issue-div">
        </div>
        <div id="weekday-div">
        </div>
      </div>
      <div id="charts-div">
        <div id="linechart-div" style="align: center; width: 500px; height: 250px;">
        </div>
      </div>
    </div>    

  </body>
</html>

Upvotes: 1

Related Questions