querying for specific date range in google docs spreadsheets

[Derek]

I want to query a google spreadsheet to select some columns dependant
on 1 date. For example, I want all records for orders shipped in the
month of November. The ship date cell uses mm/dd/yyyy. How do I
write that I want all records for orders shipped between 11/1/2011 and
11/30/2011?

Thanks,
Derek

[Roni Biran]

you can use the query language to obtain that.

use the playground sample to test it: http://code.google.com/apis/ajax/playground/?type=visualization#using_the_query_language

1. change the spreadsheet link to yours

2. in the setQuery function change the query to this:

query.setQuery("SELECT C,D WHERE C >= date '2011-11-01' and C <= date '2011-11-30' ORDER BY C");

where C is the date column and D is the data column.

pay attention to the 'date' type declaration in the query.

for more details on the query language go to this link: http://code.google.com/apis/chart/interactive/docs/querylanguage.html

Good luck,

Derek

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

[asgallant]

Use something like this:

var today = new Date();
var todayString = today.getYear() + '-' + today.getMonth() + '-' + today.getDay();
query.setQuery("SELECT C,D WHERE C = date '" + todayString + "' ORDER BY C"); 

On Thursday, June 28, 2012 10:08:18 AM UTC-4, Robert.G wrote:

How would you query for todays date only?

Thanks you,

Rob

On Thursday, December 1, 2011 12:52:39 AM UTC-6, TheNez wrote:

you can use the query language to obtain that.

use the playground sample to test it: http://code.google.com/apis/ajax/playground/?type=visualization#using_the_query_language

1. change the spreadsheet link to yours

2. in the setQuery function change the query to this:

query.setQuery("SELECT C,D WHERE C >= date '2011-11-01' and C <= date '2011-11-30' ORDER BY C");

where C is the date column and D is the data column.

pay attention to the 'date' type declaration in the query.

for more details on the query language go to this link: http://code.google.com/apis/chart/interactive/docs/querylanguage.html

Good luck,

On Wed, Nov 30, 2011 at 11:07 PM, Derek <derek....@drakerlabs.com> wrote:

I want to query a google spreadsheet to select some columns dependant
on 1 date.  For example, I want all records for orders shipped in the
month of November.  The ship date cell uses mm/dd/yyyy.  How do I
write that I want all records for orders shipped between 11/1/2011 and
11/30/2011?

Thanks,

Derek

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To post to this group, send email to google-visualization-api@googlegroups.com.
To unsubscribe from this group, send email to google-visualization-api+unsub...@googlegroups.com.

[Sergei Ledvanov]

This example has 

var query = new google.visualization.Query(
      'http://spreadsheets.google.com/tq?key=dGpwODBQaEpITnRVcjJlYllNNkRSVXc6MQ&pub=1');

But where can I get this key for my document? Do I also have to set its permissions to public?

[asgallant]

Open the spreadsheet in google docs, the key is in the URL "https://docs.google.com/spreadsheet/ccc?key=<your key here>#gid=0"

[Charitey Dawn]

where C =  now()

[訓育組]

https://docs.google.com/a/lkes.mlc.edu.tw/spreadsheet/ccc?key=0AsMhUCf4hZD6dEtuVlRydHZLQ3c4UWZLbzRYMUJiREE#gid=2
I want the data between the date "B2 and D2"
How to put the command "where" in the following QUERY ？
=QUERY('表單回應'!A:D,"select B,C,D order by B desc")
PLEASE HELP ME, THANKS~!

TheNez於 2011年12月1日星期四UTC+8下午2時52分39秒寫道：

To unsubscribe from this group, send email to google-visualization-api+unsub...@googlegroups.com.

[Sergey Grabkovsky]

You can do this by specifying the range parameter in the URL, when you request the spreadsheet. Here is an example URL for your spreadsheet that requests the range B2:D2. I bolded the range parameter. https://docs.google.com/spreadsheet/tq?key=0AsMhUCf4hZD6dEtuVlRydHZLQ3c4UWZLbzRYMUJiREE&range=B2:D2#gid=0

- Sergey

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

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

Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.

[訓育組]

Thank you very much~!
I find that it works too.
=QUERY('表單回應'!A:D,"select B,C,D WHERE B >= date """&text(B5,"yyyy-MM-dd")&""" and B <= date """&text(D5,"yyyy-MM-dd")&""" order by B asc")

2014-01-27 Sergey Grabkovsky <gra...@google.com>:

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/KuDj3rJ762k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

[Sky Power and Partners]

Hello.

I have similar problem.  It works perfectly on the old spreadsheet, but not on the new ones.... Very annoying.....

- Sergey

Derek

To unsubscribe from this group, send email to google-visualization-api+unsubscr...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

--
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.

[Sergey Grabkovsky]

Yes, this is now a known issue and the Docs team is looking into it. There are a bunch of problems with the new Sheets that they are trying to fix at the moment, so please be patient.

- Sergey

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

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.

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

[Sky Power and Partners]

Many thanks.

- Sergey

- Sergey

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

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

Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.

--
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 http://groups.google.com/group/google-visualization-api.

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

[Jereme Guenther]

Sergey,

Normally I wouldn't have any problem with that either.  But the docs team just broke the ability for forms to submit data to the old google sheets.  Perhaps a better method would have been to have an old and new forms as well.  As it is I currently have to choose between having a working form and being able to query the data in the new sheets.

Parts of the URL are still working, if they could re-add the ability to do a tq query in the url that would solve my issue as well.

[Timo Kataja]

Any updates on this? I just spent hours trying to get my previous query to work, and now I don't know if the problem is with me or with faulty handling of dates in a query :/

- Sergey

- Sergey

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

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

Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/groups/opt_out.

--
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 http://groups.google.com/group/google-visualization-api.

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

[Sergey Grabkovsky]

The new Sheets is still having issues with queries. We will update the mailing list when this is fixed.

- Sergey

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

[Sky Power and Partners]

Nope. Still not working till this moment.  Just want to make sure it is not only me.

- Sergey

- Sergey

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

[hotm...@gmail.com]

Plese check this link ;

Query function with manipulation of dates

This work with new google spreadsheet.

[Gomez]

Any idea about this?

one month later and no response

[Sergey Grabkovsky]

Hello everyone,

Unfortunately I don't have any updates for you guys or an estimate of when this will be fixed, but rest assured that we are still aware of this problem and are working towards a solution with the Docs team. We know that this is affecting a lot of users and are working as fast as we can. I will post to this thread, issue #1476, and create a new post when we have a solution or even a solid update.

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

[gaston....@gmail.com]

Have a problem.

Trying to query a list of stocks with upcoming earnings but I only want the stocks that have earnings over then next 7 days. 

the next seven days needs to be dynamic 

Stocks are in column D and Earnings dates are in column AF 

I have tried: 

query(A21:aj1336,"Select D, where AF >=" &Today() &"and AF<= "& Today()+77")")

Obvioulsy does not work.

Any help would be appreciated.

Thank you.

[gaston....@gmail.com]

Solution.

=query(Final!A21:AJ1336,"Select D where AF >= date'"&text(today(),"yyyy-MM-dd")&"'and AF <= date'"&text(today()+7,"yyyy-MM-dd")&"' and J = 'U'" )

Have been unable to understand syntax of " ' and &s but hey..

[gaston....@gmail.com]

That actually did not work.... For some reason it gives me the right answer and then gives me value. 

[Daniel LaLiberte]

Gaston,

Can you copy a more complete example of your code?  I can't tell even what language you are trying to use.

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 http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

--
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-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

--

Daniel LaLiberte  - 978-394-1058

dlaliberte@Google.com   5CC, Cambridge MA

daniel.laliberte@GMail.com 9 Juniper Ridge Road, Acton MA

[Gaston Bullrich]

Google spread sheets:

=query(Final!A21:AJ1336,"Select D where AF >= date'"&text(today(),"yyyy-MM-dd")&"'and AF <= date'"&text(today()+7,"yyyy-MM-dd")&"'and J = 'U' " )

Column D tickers

Column J (U || Recognized)

Column AF a date in mm/dd/yyyy format

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/KuDj3rJ762k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

--

Gaston Bullrich
20 East 67th Street Apt 2F
New York, NY 10065
Cel. 917 517 9907

[Daniel LaLiberte]

Ah sorry, so you are just trying to do a query inside the spreadsheet.  For that kind of question, you should ask in a forum that focuses on the spreadsheets rather than this forum which is about charts.  Here is a good place to start: https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/SxnD0CgW_fY

[brandon elsenpeter]

I want to do something similar to this but Only if it has a certain name in another column. How would I write that?