title: Manipulating Spreadsheet data for visualization
body: <p>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 <a href="https://groups.google.com/forum/#!topic/google-visualization-api/P5hcco19Rfw" rel="nofollow noreferrer">this one</a> 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.</p>
  
  <p>Below is the functional code to start with. It can successfully draw the chart below. But what I really want is:</p>
  
  <ol>
  <li>Group by "Weekday" (<code>dayOfWeek(toDate(A))</code>) and "Item" (<code>column B</code>)</li>
  <li><p>Use "Item" as the filter</p>
  
  <p><a href="https://i.stack.imgur.com/mF4GR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mF4GR.png" alt="enter image description here"></a>.</p></li>
  </ol>
  
  <p>I tried by using <code>PIVOT B</code>, <code>GROUP BY dayOfWeek(toDate(A)),</code> but it returned either <code>One or more participants failed to draw()×</code> error message or unexpected format.</p>
  
  <pre><code>&lt;html&gt;
    &lt;head&gt;
      &lt;title&gt;
        Test
      &lt;/title&gt;
      &lt;!--Load the AJAX API--&gt;
      &lt;script type="text/javascript" src="https://www.google.com/jsapi"&gt;&lt;/script&gt;
      &lt;script type="text/javascript"&gt;
        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&amp;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);
  
        }
  
  
      &lt;/script&gt;
    &lt;/head&gt;
  
    &lt;body&gt;
      &lt;!--Div that will hold the dashboard--&gt;
      &lt;div id="dashboard_div2"&gt;&lt;/div&gt;
      &lt;!--Divs that will hold each control and chart--&gt;
      &lt;div id="filter_div2"&gt;&lt;/div&gt;
      &lt;div id="current_day" style="align: center; width: 500px; height: 250px;"&gt;&lt;/div&gt;
  
    &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>