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 <https://mogsdad.wordpress.com/2015/07/19/converting-from-uiapp-chart-service-to-html-service-google-visualization-api/>. 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 <https://developers.google.com/chart/interactive/docs/spreadsheets> 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. On Tuesday, February 7, 2017 at 1:35:04 PM UTC-5, Daniel LaLiberte wrote: > > 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. > > On Tue, Feb 7, 2017 at 12:33 PM, Jimbo <[email protected] <javascript:>> > wrote: > >> 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. >> >> >> <https://lh3.googleusercontent.com/-KrpGa6LmrB4/WJoEz_GofoI/AAAAAAAAACE/5I1OykeIu_EUg5DaSfRIhq7V4ZR8MHkrACLcB/s1600/Screen%2BShot%2B2017-02-07%2Bat%2B12.31.15%2BPM.png> >> >> >> >> On Tuesday, February 7, 2017 at 12:27:04 PM UTC-5, Daniel LaLiberte wrote: >>> >>> 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. >>> >>> On Tue, Feb 7, 2017 at 11:43 AM, Jimbo <[email protected]> wrote: >>> >>>> Hello, I'm currently using this sample spreadsheet >>>> <https://docs.google.com/spreadsheets/d/1rZPcx18GCGHFz0lzSPZE9hYj8pCdHLyh--aI-__PoUA/edit?usp=sharing> >>>> >>>> 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 src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js >>>> "></script> >>>> <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. >>>> * From gist.github.com/mogsdad/60dcc4116ed74fceb5f9 >>>> */ >>>> 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. >>>> * From gist.github.com/mogsdad/60dcc4116ed74fceb5f9 >>>> * >>>> * @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> >>>> >>>> >>>> -- >>>> 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 [email protected]. >>>> To post to this group, send email to [email protected]. >>>> 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 >>>> >>>> <https://groups.google.com/d/msgid/google-visualization-api/ad5eebbb-1e64-4e5a-a153-55a13f590bf8%40googlegroups.com?utm_medium=email&utm_source=footer> >>>> . >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> >>> >>> -- >>> Daniel LaLiberte <https://plus.google.com/100631381223468223275?prsrc=2> >>> [email protected] 5CC, Cambridge MA >>> >> -- >> 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 [email protected] >> <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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 >> >> <https://groups.google.com/d/msgid/google-visualization-api/74b8491a-28e0-4291-a8db-5b215abc36f4%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> >> For more options, visit https://groups.google.com/d/optout. >> > > > > -- > Daniel LaLiberte <https://plus.google.com/100631381223468223275?prsrc=2> > [email protected] <javascript:> 5CC, Cambridge MA > -- 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 [email protected]. To post to this group, send email to [email protected]. 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/af56b619-eed6-4fdc-988e-b95f1a8e0090%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
