Google Sheet - IMPORTDATA - Query filters on Columns starts with

Question

I am trying to get data using the IMPORTDATA function in GS and filter only columns starting with 'TEXT'.

=IMPORTDATA("https://api.coinbase.com/v2/exchange-rates?currency=BTC",",")

From here, I am getting a lot of columns

Then I just want to filter columns starting with 'a string'. When I use the formula below, I got en error;:

=QUERY(IMPORTDATA("https://api.coinbase.com/v2/exchange-rates?currency=MEER%22,%22,%22),%22where Col starts with 'rates'")

Error: NO COLUMN Col

Any ideas ? Thanks

Answer 1

Use filter(), like this:

=let( 
  data, importdata("https://api.coinbase.com/v2/exchange-rates?currency=BTC", ","), 
  filter(data, regexmatch(chooserows(data, 1), "(?i)^rates")) 
)

In the event the data only ever has just one row, you can leave out chooserows():

=let( 
  data, importdata("https://api.coinbase.com/v2/exchange-rates?currency=BTC", ","), 
  filter(data, regexmatch(data, "(?i)^rates")) 
)

See let(), filter() and regexmatch().