CODEWITHSUNDEEP
sortinggoogle-sheetsspreadsheetformulas
Jeremy Niedt
Jeremy Niedt

Reputation: 33

Sorting by Date After Mult-IMPORTRANGE and QUERY

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.

Upvotes: 2

Views: 6626

Answers (1)

Sangbok Lee
Sangbok Lee

Reputation: 2228

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.

Upvotes: 2

Related Questions