Google Sheets- building Dynamic Array with multiple IMPORTXML requests

Question

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)

Hey Player0, has a lot sensitive info in it let me see what I can do to strip it down a little. — Nowski, Nov 02 '21 at 14:31

score 1 · Accepted Answer · answered Nov 02 '21 at 16:46

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), "♦")), "♠")), "♣ ", "/"))

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), "♦")), "♠")), "♣ ", "/"))

Ah nice, that makes sense.So I would still need to add each client line manually if more are added. Which is probably the least likely to change drastically over time. Thanks again for your help, you are the Don! — Nowski, Nov 02 '21 at 16:58
As a side benefit, it seems to have speeded up the whole loading XML process too as it cuts down the number of requests - thanks again. — Nowski, Nov 02 '21 at 17:17

Google Sheets- building Dynamic Array with multiple IMPORTXML requests

1 Answers1

Linked