DateRangeFilter produces a ScriptError

118 views
Skip to first unread message

Jimbo

unread,
Feb 7, 2017, 11:43:12 AM2/7/17
to Google Visualization API
Hello, I'm currently using this sample spreadsheet to extract data.
It has four columns: Date, School,Students ALL,Student ID, Staff Name

However, when I try to display the data in a table chart, I get a ScriptError.  If I remove the date column, I can display the data quite easily and filter with the "Staff Name" and "School".

Now, I need to filter by the Date; however even while using the DateRangeFilter, I get the same error. 

The Date column in my spreadsheet is of the "Date" Format. Because of this format, I am unsure why I am getting a ScriptError, however, it seems like there's something wrong here.


This is the JavaScript code I used for the sample spreadsheet. The code that is related to the DateRangeFilter is marked in orange.

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

<script>
  // Load the Visualization API and desired package(s).
  google.load('visualization', '1.0', {'packages':['controls']});

  /**
   * Run initializations on dialog load.
   */
  $(function() {
    // Set a callback to run when the Google Visualization API is loaded.
    // Note: could also be accomplished via google.load options.
    google.setOnLoadCallback(sendQuery);

    // Assign handler functions to dialog elements here, if needed.

    // Call the server here to retrieve any information needed to build
    // the dialog, if necessary.
  });

  /**
   * Issue asynchronous request for spreadsheet data.
   */
  function sendQuery() {
    google.script.run
      .withSuccessHandler(drawDashboard)
      .withFailureHandler(function(msg) {
            // Respond to failure conditions here.
            $('#main-heading').text(msg);
            $('#main-heading').addClass("error");
            $('#error-message').show();
          })
      .getSpreadsheetData();
  }

  /**
   * Callback function to generate visualization using data in response parameter.
   * 
   * @param {Object[][]}  Two-Dim array of visualization data
   */
  function drawDashboard(response) {
    $('#main-heading').addClass("hidden");

    if (response == null) {
      alert('Error: Invalid source data.')
      return;
    }
    else {
      // Transmogrify spreadsheet contents (array) to a DataTable object
      var data = google.visualization.arrayToDataTable(response,false);

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

     //table chart to display data     
      var table = new google.visualization.ChartWrapper({
        'chartType': 'Table',
        'containerId': 'table-div'
        });
        
        //Range Filter
        //fails if binding is carried out on 
        var donutSlider = new google.visualization.ControlWrapper({
        'controlType': 'DateRangeFilter',
        'containerId': 'slider-date-div',
        'options': {
          'filterColumnLabel': 'Date'
          }
         });
      
      //Category filter for the school
      var school = new google.visualization.ControlWrapper({
        'controlType': 'CategoryFilter',
        'containerId': 'selector-school-div',
        'options': {
          'filterColumnLabel': 'School'}});
      
      //Category filter for the staff      
      var staff = new google.visualization.ControlWrapper({
        'controlType': 'CategoryFilter',
        'containerId': 'selector-staff-div',
        'options': {
          'filterColumnLabel': 'Staff Name'
        }
      });

      // Set up dependencies between controls and charts
      
      dashboard.bind([school, staff,donutSlider], table);//<-Fails
      
      // Draw all visualization components of the dashboard
      dashboard.draw(data);
    }
  }  

</script>


Daniel LaLiberte

unread,
Feb 7, 2017, 12:27:04 PM2/7/17
to Google Visualization API
Make sure your date column is formatted as dates in the spreadsheet, otherwise it won't be sent over as dates but as plain text.  That is a likely problem, from what I can guess reading your code.  Copying your spreadsheet, it appears the date column is not formatted.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/ad5eebbb-1e64-4e5a-a153-55a13f590bf8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



--

Jimbo

unread,
Feb 7, 2017, 12:33:18 PM2/7/17
to Google Visualization API
Do you mean something like the following? (I've attached an image.) I did it, but there does not seem to be a change in the error.


To post to this group, send email to google-visua...@googlegroups.com.



--

Daniel LaLiberte

unread,
Feb 7, 2017, 1:35:04 PM2/7/17
to Google Visualization API
OK, I would suggest you narrow down the cause of the error by manually generating a couple rows of the data as you expect it to arrive in the browser.  In other words, bypass your sendQuery function and just provide the array of arrays data to your drawDashboard function.  

But I also notice you are trying to use arrayToDataTable with the response directly.   I'm not sure what you are doing in your sendQuery function, but the response from a query for data from a spreadsheet is not in the right form to be passed to arrayToDataTable.  See the documention here for details: https://developers.google.com/chart/interactive/docs/spreadsheets

You should also probably figure out how to use the debugger in the browser to identify where in your code the failure is occurring.  

To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsubscr...@googlegroups.com.



--

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.

Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/74b8491a-28e0-4291-a8db-5b215abc36f4%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--

Jimbo

unread,
Feb 8, 2017, 10:23:15 PM2/8/17
to Google Visualization API
Bypassing it with the manual data made it work! Thank you very much for offering that hint. So, it seems, as you highlighted earlier, that the data being fed does not seem to format the Date as a Date. Other than changing the Date format using the graphical interface of Google Sheets, I'm unsure how to do it in a programmatic manner. Do you have any ideas?

Concerning the code, I must confess that the sendQuery function has been adapted from a tutorial. I wasn't sure how to move from the UIApp method to the Visualization API, especially when connecting to a Sheet. The author of the tutorial mentioned that google.script.run calls the getSpreadsheetData() function. Since its an asynchronous function, one of the two handlers will be invoked. The successHandler expects to receive the data as a two-dimensional array, which it then interpreted into a DataTable using google.visualization.arrayToDataTable(). 

If I were to follow the instructions on the spreadsheet-link you posted earlier I think calling a function that uses a vizulaization.Query should make this work. This is but a hunch, but I think this might just be what I need.

Do you have any other suggestions? Thank you again for the earlier suggestion and the link.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.



--

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.



--

Jimbo

unread,
Feb 10, 2017, 11:17:29 AM2/10/17
to Google Visualization API
That did it! I updated the sendQuery and obtained the DataTable via the link you gave me. Thank you!!

watwiboon praemongkol

unread,
Apr 12, 2017, 6:18:45 AM4/12/17
to Google Visualization API
Hi Jimbo,

I am in the same situation using the same code and it produced the same error. Not too sure how you fixed the problem. Would you mind to share the code that you updated?

Thank you

Boon

Jimbo

unread,
May 9, 2017, 2:26:54 PM5/9/17
to Google Visualization API
Hi Boon,
I know this is quite late, but this is what I did, at least in sendQuery. The query.send command obtains the Datatable and passes it as an argument to the method handleQueryResponse. I hope this helps. 

function sendQuery() {
//      Logger.log("Before Query")
//
//      Logger.log("Before sending")
      var queryString = encodeURIComponent('select D,B,E,H,N');
      var query = new google.visualization.Query(/* Insert query string here. I used the link Daniel LaLiibrerte posted earlier.*/); 
 //     Logger.log("Before sending")          
      var chicken = query.send(handleQueryResponse);
     
}
Reply all
Reply to author
Forward
0 new messages