2

Have a Google Sheet that I'm trying to build. It uses IFTTT to pull articles people read into a individual, and then an aggregate spreadsheet.

In terms of specs, it needs to:

Pull in data from multiple sheets.

The first column in each source sheet is a date column. Some are formulas (to remove extraneous data from another date column), and some are hard-coded. This may differ sheet to sheet, but is constant per sheet.

Once imported into the aggregate sheet, I need to sort by date.

Problem I'm a query/importrange newbie, and I'm currently stuck on the sorting by date.

Current URL

https://docs.google.com/spreadsheets/d/1GLGYvApJgRheg7rgzoB8rFyTUgkRpZ2O8eKVE4bZyo4/edit?usp=sharing

When I order by Col1, I can't honestly tell how it is sorting, the end result is:

March 7, 2017 February 15, 2007 February 28, 2017 March 7, 2017 March 8, 2017 November 9, 2010

If you inspect the cells, the first March 7, 2017 is situated where the formula resides, which does not seem to move no matter how I sort. If you look at the sort order without that cell, it seems to be sorting alphabetically.

So it comes down to two main questions:

-What am I doing wrong that is making it so the order by is not including the first row. Edit: This is now fixed

-How do I get it to recognize that the contents of the sorting column is a date?

Thanks ahead of time - J.

Improve this question

1 Answer 1

Reset to default
2

Your formula seems to have a few problems.

  1. importrange should take key not url. But it seems that it works anyway...
  2. Pulled sheets have no header, so the 3rd parameter of query should be -1 or omitted, not 1.
  3. If Col1 is a valid date, <> '' should not work. It should be is not null.
  4. But it turns out that your pulled sheets' dates are not yyyy-mm-dd format so they weren't recognized as dates by query.

Thus, more valid formula should be like this:

=query({importrange("...", "Sheet1!A:E");importrange("...", "Sheet1!A:E")}, 
 "select * where Col1 is not null order by Col1 asc",
 -1)

And you should format dates(column A) on your pulled sheets to yyyy-mm-dd. Check my working sample aggregator and pulled sheet one and two.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you for the quick response! I switched to key, which doesn't seem to impact it, but if it's the right way, it's fixed. The -1 query fixes the "sticky" row occupied by the formula. Adding not null makes all of my entries disappear. I've granted you access to the source sheet I think is the problem, which has formula based dates. Yours worked, but i noticed they are hardcoded. I've saved a second sheet in the aggregator with not null as the selection criteria.
Just noticed they don't disappear on the second sheet, rather get pushed to row 1993. They are still sorted alphabetically. So basically I just need to figure out how to have the column sort as a date.
My mistake, is not null is wrong, it should be <> ''. I edited the answer, and I modified the sample aggregator to import your file 'Jer's Tech Feed'.
Thank you again for the help. I added in the second sheet to your copy, and as I'm sure you can see it's sorting alphabetically, as opposed to chronologically. Is there any way to fix this? Defining the sort column data type as a date or something along those lines?
It turns out that your pulled sheets' dates are not yyyy-mm-dd format so it cannot be recognized as a date by query. You should format dates(column A) on your pulled sheets to yyyy-mm-dd. Check sample sheet again.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.