首页 文章

从另一个工作表调用查询并将其更改为更低和更正

提问于
浏览 次
0

我有一张 table ：

https://docs.google.com/spreadsheets/d/1EYRH0qlSUMXwqc2pUFKnwrsQ_a4nPO9Xs-CjEgBfx9Q/edit#gid=0

我想显示具有以下条件的其他dare sheet数据：如果列 C 中的数据具有Mosque，Temple，Church，则 C 列中的数据将更改为Proper，否则， C 列中的数据将更改为Lower .

我已经制定了配方，但结果是错误的，

https://docs.google.com/spreadsheets/d/1fLmq8dxqPgDUEHDsJ42qHsviCvIeQGNGWDQ-Z6GXURY/edit#gid=0

=Arrayformula(if(or(importrange(H2,"Sheet1!C2:C")="Mosque",importrange(H2,"Sheet1!C2:C")="Church",importrange(H2,"Sheet1!C2:C")="Temple"),
proper(query(importrange(H2,"Sheet1!C2:C"),"Select C Where A = 'New' And D = 'Done'"),lower(query(importrange(H2,"Sheet1!C2:C"),
"Select C Where A = 'New' And D = 'Done'")))))

所以我要求解决方案先生？

google-apps-script google-sheets

1 回答

  • 0

    首先你需要导入全范围查询 importRange(H2,"Sheet1!A2:D") 不仅列C.我不知道为什么但是 importrange 看起来像 query 只接受使用 Col1 而不是 A ， Col2 而不是 B 等等所以这样应该管用

    =Arrayformula(if(or(importrange(H2,"Sheet1!C2:C")="Mosque",
importrange(H2,"Sheet1!C2:C")="Church",
importrange(H2,"Sheet1!C2:C")="Temple"),
proper(query(importrange(H2,"Sheet1!A2:D"), "Select Col3 Where Col1 = 'New' And Col4 = 'Done'")),
lower(query(importrange(H2,"Sheet1!A2:D"),"Select Col3 Where Col1 = 'New' And Col4 = 'Done'"))))
    回复于

相关问题