2

As stated in the title, suppose I have spreadsheet with following values:

B
C
A
Š
Ť
Á

I then import the values from this spreadsheet using QUERY and IMPORTRANGE to different spreadsheet, exact function:

=QUERY(IMPORTRANGE("<spreadsheet_id>"; "List 1!A:B"); "SELECT * WHERE Col1!='' ORDER BY Col1";-1)

The result I get is this:

A
B
C
Á
Š
Ť

So the ORDER BY works on separately on the non-accented letters and on the accented ones but not together, the result I expect is:

A
Á
B
C    
Š
Ť

This makes it useless for me. When I try to order them manually the data gets distorted (move to completely different rows, the ordering is not correct either. I've check, that the language in the settings is set correctly.

When I've googled a little, some links addressing different SQL (like MySQL) COLLATE UNICODE, but this does not work.

Any ideas?

quapka
  • 2,799
  • 4
  • 21
  • 35
  • You need to have a table to convert all accented characters to normal characters. As of now, `ORDER BY` is working as intended. The code for your characters `A B C Á Š Ť` are `65 66 67 193 352 356` Related: https://stackoverflow.com/a/35651656 – TheMaster Oct 09 '17 at 17:25
  • Thanks, but that sounds like a too much work, when you consider the fact, that the regular ordering of the data works. – quapka Oct 09 '17 at 18:30

1 Answers1

2

The issue is that query invokes a completely different language that doesn't necessarily behave like other Sheets functions such as sort. Since sort does what you want, use it instead of order by in query:

=SORT(QUERY(IMPORTRANGE(...); "SELECT * WHERE Col1!=''); 1; True)

sorts the output of query by the first column (1), ascending (True). The output is 

A
Á
B
C
Š
Ť