google-sheets - 谷歌表格 - 使用多个 IMPORTXML 请求构建动态数组

Question

我有一个复杂的（至少对我而言）xmlimport 查询——感谢 player0 来自这个问题：（将IMPORTXML Xpath 查询格式化为谷歌表格的可读数据）

=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'!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}

但这似乎是一种非常繁琐的方法，每次我在参考表中添加新行时都需要手动编辑。我看过arrayFormula，但我无法弄清楚语法

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

标签： google-sheetsimportgoogle-sheets-formulaflattentextjoin

Answer 1

以我的知识贫乏，我会这样做......

IMPORTXML下不支持ARRAYFORMULA所以唯一的办法就是构造一个虚拟数组{}。为了让事情变得干净整洁，让我们将第二个参数IMPORTXML放入某个单元格中 - 例如 H6。这样我们可以做到：

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

并将其输入到主公式中：

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

现在要实现复选框，我们可以这样做：

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