CODEWITHSUNDEEP
google-sheetsimportgoogle-sheets-formulaflattentextjoin
Nowski
Nowski

Reputation: 183

Google Sheets- building Dynamic Array with multiple IMPORTXML requests

I've got a complex (at least to me) xmlimport query - thanks to player0 from this question: (Formatting IMPORTXML Xpath query into readable data for Google Sheets)

=INDEX(SUBSTITUTE(TRIM(SPLIT(FLATTEN(SPLIT(QUERY(IFNA(
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), {0, 1}), "♦", )&
 IMPORTXML('reference sheet'!H2, 
"//data/advertiserId|//data/campaignName|//data/impressions|//data/startDate/year|
//data/startDate/month|//data/startDate/day|//data/endDate/year|
//data/endDate/month|//data/endDate/day")&
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), {0, 1, 2, 3, 4, 5,6,7,8}), 
"♠","♠","♣","♣","♠","♣","♣","♠","♠")),,9^9), "♦")), "♠")), "♣ ", "/"))

Which works a treat returning what I need. However I want to put a dynamic number of these into an array so I can have a continuous block of data I can use in a query. Currently I have to do this manually. I would like to build that array dynamically from a checkbox in another sheet: Something like:

={IF('reference sheet'!A2=True,INDEX(SUBSTITUTE(TRIM(SPLIT(FLATTEN(SPLIT(QUERY(IFNA(
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), {0, 1}), "♦", )&
 IMPORTXML('reference sheet'!H2, "//data/advertiserId|//data/campaignName|//data/impressions|
//data/startDate/year|//data/startDate/month|//data/startDate/day|
//data/endDate/year|//data/endDate/month|//data/endDate/day")&
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), {0, 1, 2, 3, 4, 5,6,7,8}),
"♠","♠","♣","♣","♠","♣","♣","♠","♠")),,9^9), "♦")), "♠")), "♣ ", "/"));,"")
IF('reference sheet!A3=True, INDEX(SUBSTITUTE(TRIM(SPLIT(FLATTEN(SPLIT(QUERY(IFNA(
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), {0, 1}), "♦", )&
 IMPORTXML('reference sheet'!H2, "//data/advertiserId|//data/campaignName|//data/impressions|
//data/startDate/year|//data/startDate/month|//data/startDate/day|
//data/endDate/year|//data/endDate/month|//data/endDate/day")&
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), {0, 1, 2, 3, 4, 5,6,7,8}),
"♠","♠","♣","♣","♠","♣","♣","♠","♠")),,9^9), "♦")), "♠")), "♣ ", "/"));,"");
etc; etc}

But this seems like a very cumbersome way to do it, and it would need to be manually edited each time I add a new row in the reference sheet. I have looked at arrayFormula but I cannot figure out the syntax

(https://docs.google.com/spreadsheets/d/1JstchCnBNHIE12DipH_9m_15HveMyD8K8LWzHuMqpT8/edit?usp=sharing)

Upvotes: 1

Views: 542

Answers (1)

player0
player0

Reputation: 1

with my poor knowledge, I would do it like this...

IMPORTXML is not supported under ARRAYFORMULA so the only way is to construct a virtual array {}. to make things clean and neat let's put 2nd argument of IMPORTXML into some cell - for example H6. this way we can do:

={IMPORTXML('Client Ads Served'!H2, H6);
  IMPORTXML('Client Ads Served'!H3, H6);
  IMPORTXML('Client Ads Served'!H4, H6);
  IMPORTXML('Client Ads Served'!H5, H6)}

and just input it into the main formula:

=INDEX(SUBSTITUTE(TRIM(SPLIT(FLATTEN(SPLIT(QUERY(IFNA(
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), {0, 1}), "♦", )&
 {IMPORTXML('Client Ads Served'!H2, H6);
  IMPORTXML('Client Ads Served'!H3, H6);
  IMPORTXML('Client Ads Served'!H4, H6);
  IMPORTXML('Client Ads Served'!H5, H6)}&
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), SEQUENCE(9)-1), 
 "♠","♠","♣","♣","♠","♣","♣","♠","♠")),,9^9), "♦")), "♠")), "♣ ", "/"))

enter image description here

now to implement checkboxes we can do:

=INDEX(SUBSTITUTE(TRIM(SPLIT(FLATTEN(SPLIT(QUERY(IFNA(
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), {0, 1}), "♦", )&QUERY(TO_TEXT(
 {IF('Client Ads Served'!A2=TRUE, IMPORTXML('Client Ads Served'!H2, H6), );
  IF('Client Ads Served'!A3=TRUE, IMPORTXML('Client Ads Served'!H3, H6), );
  IF('Client Ads Served'!A4=TRUE, IMPORTXML('Client Ads Served'!H4, H6), );
  IF('Client Ads Served'!A5=TRUE, IMPORTXML('Client Ads Served'!H5, H6), );
  IF('Client Ads Served'!A6=TRUE, IMPORTXML('Client Ads Served'!H6, H6), )}),
 "where Col1 is not null", )&
 CHOOSE(MATCH(MOD(SEQUENCE(999)-1, 9), SEQUENCE(9)-1), 
 "♠","♠","♣","♣","♠","♣","♣","♠","♠")),,9^9), "♦")), "♠")), "♣ ", "/"))

enter image description here

Upvotes: 1

Related Questions