2

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".

  • Instead of row(importxml(...)) use something like row(A1:A10) – Rubén Mar 19 '16 at 23:21
  • The number of results is unknown (it may change as the content changes). – user79865 Mar 19 '16 at 23:40
1

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
   )
 )
| improve this answer | |
  • 1E1000 is a lot... – user79865 Mar 20 '16 at 0:54
  • @404 that is the idea :) – Rubén Mar 20 '16 at 0:55
  • Actually it was too large. Just remove one 0. – Rubén Mar 20 '16 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy