i'm trying create google chart google apps script using formula exists.
this formula works fine in spreadsheet:
=query(a:a; "select >= datetime '"&text(today();"yyyy-mm-dd hh:mm:ss")&"'";-1) this code doesn't work google apps script intended:
query.setquery("select a, b >= todate( now() )"); var query = new google.visualization.query('https://docs.google.com/spreadsheets/d/key'); query.setquery("select a, b = date '" + nowone + "'"); var nowone = getnowdate(); query.send(handlequeryresponse); } function getnowdate(){ var date = new date(); var year = date.getfullyear(); var month = date.getmonth() + 1; var date = date.getdate(); if (month < 10) { month = "0" + month; } if (date < 10) { date = "0" + date; } var strdate = string(year + "-" + month + "-" + date + " 00:00:00"); return strdate; } i've tried many times without success replicate formula google apps script.
thanks in advance...
i tried query in spreadsheet , able expected results.
when coming code there 2 things changed.
first 1 calling nowone variable before set value. can add statement before set query.
the second thing found instead of date in query should give datetime.
please find below code reference:
function drawdashboard() { var query = new google.visualization.query( 'https://docs.google.com/spreadsheets/d/key/edit#gid=0'); var nowone = getnowdate(); //alert(nowone); //query.setquery("select a, b >= todate( now() )"); query.setquery("select a,b >= datetime '"+nowone+"'"); query.send(handlequeryresponse); } in order set query using visualization class, have 1. add code in html file in apps script console. 2. create html output file. 3. deploy webapp.
tried code below data query:
index.html:
<html> <head> <!--load ajax api--> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> var data ; // load visualization api , controls package. google.load('visualization', '1.0', {'packages':['controls']}); // set callback run when google visualization api loaded. google.setonloadcallback(drawdashboard); // callback creates , populates data table, // passes in data , draws it. function drawdashboard() { var query = new google.visualization.query( 'https://docs.google.com/spreadsheets/d/key/edit#gid=0'); var nowone = getnowdate(); //alert(nowone); //query.setquery("select a, b >= todate( now() )"); query.setquery("select a,b >= datetime '"+nowone+"'"); query.send(handlequeryresponse); } function getnowdate(){ var date = new date(); var year = date.getfullyear(); var month = date.getmonth() + 1; var date = date.getdate(); if (month < 10) { month = "0" + month; } if (date < 10) { date = "0" + date; } var strdate = string(year + "-" + month + "-" + date + " 00:00:00"); return strdate; } function handlequeryresponse(response) { if (response.iserror()) { alert('error in query: ' + response.getmessage() + ' ' + response.getdetailedmessage()); return; } data = response.getdatatable(); // create dashboard. alert(data); } </script> </head> code.gs:
function doget() { var html = htmlservice.createhtmloutputfromfile('index'); html.setsandboxmode(htmlservice.sandboxmode.iframe); return html; } when access webapp, able see response returning object. in handlequeryresponse(response) function add more code create chart or table returned data.
you can refer documentation creating table data values. hope helps!
Comments
Post a Comment