Google Sheets – Present importXML Output in Reverse Order

google sheetsimportxmlxpath

How to reverse the order of the output of imporxml? For example, =importxml("http://example.com","//p") outputs

+---+---------------------+
| 1 | This domain is ...  |
| 2 | More information... |
+---+---------------------+

and I want

+---+---------------------+
| 1 | More information... |
| 2 | This domain is ...  |
+---+---------------------+

I know various ways to reverse a range already present in a sheet, for example

=query({A:A, arrayformula(row(A:A))}, "select Col1 where Col1 <> '' order by Col2 desc")

outputs nonempty cells in column A in reverse order. However, I'd like to reverse the order without the intermediate step of placing the output in the sheet. Trying arrayformula(row(importxml(...))) throws an error: "Argument must be a range".

Best Answer

Use COUNTA and ARRAY_CONSTRAIN to set the size of the sequential number array to be used as the sort column. Example:

=ARRAYFORMULA(ARRAY_CONSTRAIN(ROW(A:A),COUNTA(IMPORTXML(range,xpath)),1))

The final formula should look like the following:

=ARRAYFORMULA(
   ARRAY_CONSTRAIN(
     SORT(
       {
         IMPORTXML(url,xpath),
         ARRAY_CONSTRAIN(ROW(A:A),COUNTA(IMPORTXML(url,xpath)),1))
       },
       2,
       FALSE
     ),
     1E100,
     1
   )
 )
Related Topic