英文:
How to convert an IMPORTRANGE() array to a string, for use with QUERY()?
问题
我遇到了与此处讨论的相同问题，只是我使用IMPORTRANGE()而不是直接的数组引用（例如D12:I119）。我需要QUERY()将其输入理解为字符串数组。但是，我的尝试在IMPORTRANGE()后面添加一个空字符串（&""），如链接中建议的那样，没有奏效：
QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/...", "Sheet1!A3:1000")&"", "select Col1, Col2")
除此之外，我还有其他方法可以解决这个问题吗？不幸的是，我无法更改IMPORTRANGE()引用的电子表格上的数据。
英文:
I have the same problem which is discussed here, except that I am working with IMPORTRANGE() instead of a direct array reference (e.g., D12:I119). Somehow I need QUERY() to understand its input as an array of strings. But my attempt to addend a null string (&"") to IMPORTRANGE(), as suggested in the link, doesn't work:
QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/...","Sheet1!A3:1000")&"","select Col1, Col2")
How else can I get around this? Unfortunately, I can't change the data on the spreadsheet referenced by IMPORTRANGE().
答案1
得分: 4
=INDEX(QUERY(TO_TEXT(IMPORTRANGE("https://docs.google.com/spreadsheets/d/...",
"Sheet1!A3:1000")),
"select Col1, Col2", ))
英文:
use:
=INDEX(QUERY(TO_TEXT(IMPORTRANGE("https://docs.google.com/spreadsheets/d/...",
"Sheet1!A3:1000")),
"select Col1, Col2", )
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论