In Google Sheets, how to convert numbers to values, in an array, without breaking text?-CodePudding

I need to sort an array taken from an API, by "price" and ascending Col7.

Best way I could find is using formula below, but the text values break, and the headers disappear, because in order for the sorting of Col7 to work well I had to force the whole array to number with VALUE().

=QUERY(SORT(SORT(value(ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz", "/", "")),INDEX(VALUE(ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz", "/", "")),0,7),FALSE),INDEX(SORT(VALUE(ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz", "/", "")),INDEX(VALUE(ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz", "/", "")),0,7),FALSE),0,7),FALSE),"select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15 order by Col7 asc offset 0",0)

Here´s what happens:

Prices are sorted like intended but all text is broken and header is gone.

If I don´t use VALUE() the sorting does not work well and the Headers get thrown down below like this:

This is the formula wthout the VALUE():

=QUERY(SORT(SORT(ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz", "/", ""),INDEX(VALUE(ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz", "/", "")),0,7),FALSE),INDEX(SORT(VALUE(ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz", "/", "")),INDEX(VALUE(ImportJSON("https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz", "/", "")),0,7),FALSE),0,7),FALSE),"select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15 order by Col7 asc offset 0",0)

Here is a dummy file:
https://docs.google.com/spreadsheets/d/1ExXtmQ8nyuV1o_UtabVJ-TifIbORItFMWjtN6ZlruWc/edit?usp=sharing

CodePudding user response：

You can run IFERROR(VALUE to revert everything

=ARRAYFORMULA(
  QUERY(
   IFERROR(
    VALUE(
     IMPORTJSON(
      "https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz",
      "/", "")),
     IMPORTJSON(
      "https://api-apollo.pegaxy.io/v1/pegas/prices/floor?maxBreedCount=7&minBreedCount=0&breedType=Legendary&bloodLine=Hoz",
      "/", "")),
   "order by Col7 asc offset 0",0))