Google Query For Dates in Last 30 Days

Question

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.

Answer 1

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

Answer 2

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!