How do I do a date-based query in new Google Spreadsheets?

0
votes

I am using the new Google Spreadsheets that was rolled out a couple of weeks back. I'm using a following query to pull data out of another spreadsheet:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheet/ccc?key=blablathisisanexample", "samplesheet!A:D"), "select SUM(Col4) where ( Col1 >= date '2012-1-1' ) ")

The query works perfectly well if I remove the bit 'where ( Col1 >= date '2012-1-1' )'. It will pull data and sum it correctly.

But what's wrong with my where-col1-date filter thingy? It's written the way it was in previous version of Google Spreadsheet, and it gives no error messages. It just returns a header and no result, as if it didn't find a single date. The dates in the data file are in column 1, written in format 4/7/2014, for example. This used to work in the previous version and I'm at a loss now.

1
google-sheets

1 Answers

0
votes

I have several suggestions here. But first: Let's copy the "2012-1-1" value to a particular cell, say F5, and write it with the exact same date format az in the original spreadsheet: 1/1/2012.

  1. Use FILTER function:

    =SUM(FILTER(IMPORTRANGE("https://docs.google.com/spreadsheet/ccc?key=blablathisisanexample", "samplesheet!D:D"), IMPORTRANGE("https://docs.google.com/spreadsheet/ccc?key=blablathisisanexample", "samplesheet!A:A")>=F5))

I admit that it's not that pretty because of the two ImportRange functions, but it works for sure, I tried it.

  1. If you insist on using QUERY then try to replace the where condition as follows:

    "select SUM(Col4) where ( Col1 >= " & F5 & ")"

I haven't tried this one, but the first one (with FILTER) should work.