CODEWITHSUNDEEP
arraysgoogle-sheetsimport
user3060061
user3060061

Reputation: 3

How to extend importHTML functions further

Is it possible to join two query to shape data from a importHTML function?

Currently I have imported one column with 40 rows each cell has 3 lines of text, I need them to have their own columns but the 3 text line is not required. (Record Label)

  1. To split 3 lines of text within a cell and transpose them into their own columns. The third line of text is not required therefore Could be deleted. The following formula I have used locally on GS.
=arrayformula(if(len(A1:A),split(A1:A,char(10)),))

With success.Unable to work out how to translate this array into the importHTML

Shows my GS layout with importHTML formula divided into 4 segments

  1. There are also blanks generated and is there a formula to join with the above array?

Shows the array that splits the three lines of text

Upvotes: 0

Views: 86

Answers (1)

Aresvik
Aresvik

Reputation: 4620

Is this the sort of thing you need?

Try this in cell B5.

=arrayformula(query(split(query(importhtml(B1,B2,B3),"select Col3 where Col3 is not null",1),char(10)),"select Col1,Col2 order by Col1,Col2 label Col1 'Song', Col2 'Artist'",1))

Method:

importhtml(B1,B2,B3) brings in the data where B1, B2 and B3 are your cells containing URL, query item and index.

query(importhtml(B1,B2,B3),"select Col3 where Col3 is not null",1) is a QUERY function used to only select col 3 of the data.

SPLIT then splits each cell value into separate columns, based on a line return char(10). There are 3 columns.

Another QUERY around this selects only Col1 and Col2, and orders the result by Col1 then Col2, and then names the head of Col1 'Song' and Col2 'Artist'.

ARRAYFORMULA sits on the outside and ensures that all subsequent row data is processed, provided that the functions inside are compatible (mainly for the SPLIT function).

For more info on QUERY see Google's reference here: https://developers.google.com/chart/interactive/docs/querylanguage#language-clauses

enter image description here

Upvotes: 1

Related Questions