I am using the IMPORTRANGE in Google Sheets combined with the QUERY function to import columns from another file, with ORDER BY. This sorting then needs to be matched with the normal column-sort using filters. The problem is that the imported file contains umlauts, and these don't sort correctly, but puts Ü after Z, for example.
Here is the formula that I am using that works fine, except for the sorting problem:
=QUERY(IMPORTRANGE("aBc....xYz"; "SheetX!E4:M"); "SELECT Col1, Col2, Col3, Col9, Col10 WHERE Col2 IS NOT NULL ORDER BY Col1, Col2, Col3 ASC")
Where aBc....xYz is the file identifier of the file whose data is being imported. The problem is, the original sheet contains umlauts (could also be letters with accents, as in French, Swedish, Spanish, etc.) So the Ü, for example, is sorted to come after Z, which does not match the normal sort.
Then I tried: =QUERY(IMPORTRANGE("aBc....xYz"; "SheetX!E4:M"); "SELECT Col1, Col2, Col3, Col9, Col10 WHERE Col2 IS NOT NULL ORDER BY REGEXREPLACE(Col1, '[ÄÖÜäöü]', '[AOUaou]'), REGEXREPLACE(Col2, '[ÄÖÜäöü]', '[AOUaou]'), REGEXREPLACE(Col1, '[ÄÖÜäöü]', '[AOUaou]') ASC")
Then: =QUERY( ARRAYFORMULA(REGEXREPLACE(IMPORTRANGE("aBc....xYz"; "SheetX!E4:M"); "[ÄÖÜäöü]"; "[AOUaou]")), "SELECT Col1, Col2, Col3, Col9, Col10 WHERE Col2 IS NOT NULL ORDER BY Col1, Col2, Col3 ASC"
But these all give me syntax errors. (Probably the REGEXREPLACE doesn't fit with Query syntax, but both ChatGPT and Google Bard suggested it.) I need the sorted imported data to match the sorting of the normal Google Sheets filter sort, which seems to handle the umlauts correctly. Does anyone know what the syntax error is here, or know of a simpler way to get the ORDER BY sort to work correctly with accents and umlauts, or can think of some simpler solution to the problem?
You can try wrapping sort function around your query as an alternative & totally skipping the Order by part:
=sort(your_query,1,1,2,1,3,1)