2

I'm trying to figure out how to do a google query for dates in the last 30 days. I'm using a wordpress plugin that lets you display spreadsheet data and do queries.

Here is the current working query

query="SELECT B,N,E,G,H,I WHERE A='Exited Positions Bullish Stocks'"

And here is the one I'm trying that just doesn't work.

query="SELECT B,N,E,G,H,I WHERE A='Exited Positions Bullish Stocks' and G>NOW()-30"

This is inside a shortcode in WordPress, so I don't think that the formatting is exactly the same as in Google. Here is a working example:

[gdoc key="https://docs.google.com/a/justinhandley.com/spreadsheets/d/15q2H_X0nohUJs8vgZ6NFiHrtU5lBKrEQLM1dJoQgB6E/edit" gid="2078888428" class="no-datatables" query="SELECT B,N,E,G,H,I WHERE A='Exited Positions Bullish Stocks' "]

Based on answers below, I have tried...

[gdoc key="https://docs.google.com/a/justinhandley.com/spreadsheets/d/15q2H_X0nohUJs8vgZ6NFiHrtU5lBKrEQLM1dJoQgB6E/edit" gid="2078888428" class="no-datatables" query="SELECT B,N,E,G,H,I WHERE A='Exited Positions Bullish Stocks' "]

Which works (but doesn't filter by date) and can be seen at

http://leibovitvrnewsletters.com/portfolio-test-1/

[gdoc key="https://docs.google.com/a/justinhandley.com/spreadsheets/d/15q2H_X0nohUJs8vgZ6NFiHrtU5lBKrEQLM1dJoQgB6E/edit" gid="2078888428" class="no-datatables" query="('VR Platinum'!A:N, "SELECT * WHERE A='Exited Positions Bullish Stocks' AND G >= date '" & TEXT(NOW()-30, "yyyy-MM-dd") & "' ") "]

Which copies the query below, but doesn't work (doesn't sort by anything, seems to return entire list of everything) and can be seen at

/portfolio-test-2/

[gdoc key="https://docs.google.com/a/justinhandley.com/spreadsheets/d/15q2H_X0nohUJs8vgZ6NFiHrtU5lBKrEQLM1dJoQgB6E/edit" gid="2078888428" class="no-datatables" query="SELECT B,N,E,G,H,I WHERE A='Exited Positions Bullish Stocks' AND G >= date '" & TEXT(NOW()-30, "yyyy-MM-dd") & "' "]

Which just totally breaks, and can be seen at

/portfolio-test-3/

Any help putting this together is much appreciated.

2

Try the following modification.

Formula

=QUERY(DATA, "SELECT B WHERE A='Exit' AND G >= date '" & TEXT(NOW()-30, "yyyy-MM-dd") & "' ")

Reference

Google Spreadsheet: Count values from last 30 days

  • This gave me an error. What was actually tried was adding G >= date '" & TEXT(NOW()-30, "yyyy-MM-dd") & "' to the end of the query block I just added on to the end of the original question. I'm wondering if it is a quotes issue - with nesting like this do they need to be commented out? – Justin Handley Apr 21 '15 at 11:45
  • @JustinHandley This worked for me: =QUERY('VR Platinum'!A:N, "SELECT * WHERE A='Exited Positions Bullish Stocks' AND G >= date '" & TEXT(NOW()-30, "yyyy-MM-dd") & "' ") – Jacob Jan Tuinstra Apr 21 '15 at 12:08
  • I have been trying to get that to work. I've done three pages... – Justin Handley Apr 30 '15 at 20:12
  • Please see the new full ammended questions for examples. – Justin Handley Apr 30 '15 at 20:13
1

OK, so I finally got this to work as a combination of forces. The plugin author gave me some great advice here:

https://wordpress.org/support/topic/and-and-quotes-in-a-query?replies=4#post-6894935

Which was - why try to be so complex in the plugin itself? Instead, why not do the heavy lifting in Google.

So, I used the query provided by @JacobJanTuinstra

=QUERY('VR Platinum'!A:N, "SELECT * WHERE A='Exited Positions Bullish Stocks' AND G >= date '" & TEXT(NOW()-30, "yyyy-MM-dd") & "' ")

In a new spreadsheet in the Google doc. In fact, I took out the stock type filter and just filtered by date.

=QUERY('VR Platinum'!A:N, "SELECT * WHERE AND G >= date '" & TEXT(NOW()-30, "yyyy-MM-dd") & "' ")

Then, I was able to use my exact same query that was currently working, and just switch out the spreadsheet ID. So in the plugin I still used

query="SELECT B,N,E,G,H,I WHERE A='Exited Positions Bullish Stocks'"

and just queried against the spreadsheet I created based on the Google query above, and it works!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.