使用Google Apps脚本将query.setquery设置为当天 [英] query.setquery to current day with Google Apps Script
问题描述
我试图使用已存在的公式为Google Apps脚本创建Google图表。
此公式可在电子表格中正常工作：
= query（A：A;select A where A> = datetime'& TEXT（today（）;yyyy-MM -dd HH：mm：ss）&'; - 1）
按预期工作到Google Apps脚本：
query.setQuery（select A，B where A> = toDate now（）））;
var query = new
google.visualization.Query（'https://docs.google.com/spreadsheets/d/key'）;
query.setQuery（select A，B where A = date'+ nowone +'）;
var nowone = getNowDate（）;
query.send（handleQueryResponse）;
}
函数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）;
返回strDate;
}
我曾多次尝试将公式复制到Google Apps Script 。
在此先感谢...
在电子表格中进行查询，并能够获得预期的结果。
来到您的代码时，需要修改两件事情。
第一个是你在调用nowone变量之前将其设置为某个值。为此，您可以在设置查询之前添加该语句。
我发现的第二件事是在查询中，我应该给日期时间而不是日期。
请参阅以下代码以供参考：
函数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 where A> = toDate（now（）））;
query.setQuery（select A，B where A> = datetime'+ nowone +'）;
query.send（handleQueryResponse）;
$ / code> 为了使用可视化类设置查询，您必须
1.在应用程序脚本控制台的html文件中添加此代码。
2.从文件创建HTML输出。
3.然后将其作为web应用程序部署。
尝试下面的代码以获取查询中的数据：
index.html：
< html>
< head>
<！ - 加载AJAX API - >
< script type =text / javascriptsrc =https://www.google.com/jsapi>< / script>
< script type =text / javascript>
var data;
//加载Visualization API和控件包。
google.load（'visualization'，'1.0'，{'packages'：['controls']}）;
//设置回调以在加载Google Visualization API时运行。
google.setOnLoadCallback（drawDashboard）;
//创建并填充数据表的回调，
//传递数据并绘制它。
函数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 where A> = toDate（now（）））;
query.setQuery（select A，B where A> = datetime'+ nowone +'）;
query.send（handleQueryResponse）;
}
函数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）;
返回strDate;
$ b函数handleQueryResponse（响应）{
if（response.isError（））{
alert（'Error in query：'+ response .getMessage（）+''+ response.getDetailedMessage（））;
return;
}
data = response.getDataTable（）;
//创建仪表板。
alert（data）;
}
< / script>
< / head>
code.gs： p>
函数doGet（）{
var html = HtmlService.createHtmlOutputFromFile（'index'）;
html.setSandboxMode（HtmlService.SandboxMode.IFRAME）;
返回html;
}
当我访问webapp时，我能够看到响应正在返回一些对象。在handleQueryResponse（response）函数中，您可以添加一些代码来为返回的数据创建图表或表格。
您可以参考文档来创建数据值表。希望有帮助！
I'm trying to create a Google Chart to Google Apps Script using a formula that already exists.
this formula works fine in the spreadsheet:
=query(A:A; "select A where A >= datetime '"&TEXT(today();"yyyy-MM-dd HH:mm:ss")&"'";-1)
this code doesn't work to Google Apps Script as intended:
query.setQuery("select A, B Where A >= toDate( now() )");
var query = new
google.visualization.Query('https://docs.google.com/spreadsheets/d/key');
query.setQuery("select A, B Where A = 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 to replicate the formula to Google Apps Script.
thanks in advance...
I tried the query in the Spreadsheet and was able to get the expected results.
When coming to your code there are two things to be changed.
First one is you are calling the nowone variable before it is set to some value. For that you can just add that statement before the set query.
The second thing I found is that I instead of date in the query you should give datetime.
Please find the below code for 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 Where A >= toDate( now() )");
query.setQuery("select A,B where A >= datetime '"+nowone+"'");
query.send(handleQueryResponse);
}
In order to set the query using visualization class, you have to 1. Add this code in html file in apps script console. 2. create HTML output from file. 3. Then Deploy this as a webapp.
Tried this code below to get the data from query:
index.html:
<html>
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
var data ;
// Load the Visualization API and the controls package.
google.load('visualization', '1.0', {'packages':['controls']});
// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawDashboard);
// Callback that creates and populates a data table,
// passes in the data and 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 Where A >= toDate( now() )");
query.setQuery("select A,B where A >= 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 a dashboard.
alert(data);
}
</script>
</head>
code.gs:
function doGet() {
var html = HtmlService.createHtmlOutputFromFile('index');
html.setSandboxMode(HtmlService.SandboxMode.IFRAME);
return html;
}
When I access the webapp, I was able to see that the response is returning some object. In the handleQueryResponse(response) function you could add some more code to create a chart or a table for the returned data.
You can refer to this documentation for creating table for the data values. Hope that helps!
这篇关于使用Google Apps脚本将query.setquery设置为当天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
