Google表格查询停止按预期工作 [英] Google Sheet Query stopped working as expected

问题描述

我有一天前的公式，下面的公式非常漂亮......它会给我找到的前两个结果，他们将在另一个细胞中。我不得不重置表单响应目标，但重新创建了它。在昨天晚上这个改变之后，他们开始用不同的格式显示，其中第一个单元格/行中有多个名称显示在1个单元格中，而不是1个名称，然后是第二个单元格/行中的第二个。

= IFERROR（QUERY（'1ON1FORM'！$ O $ 3：$ AX，选择AX其中O包含日期& text（B17，yyyy- （C17，HH：mm：ss）&或P包含日期&文本（B17， （C，HH：mm：ss）&或Q包含日期&文本（B17 ，yyyy-mm-dd）&和Q包含timeofday& text（C17，HH：mm：ss）&或R包含日期&文本（B17，yyyy-mm-dd）&和R包含timeofday& text（C17，HH：mm：ss）&或S包含日期& amp ;文本（B17，yyyy-mm-dd）&和S包含timeofday& text（C17，HH：mm：ss）&或T包含日期 & text（B17，yyyy-mm-dd）&和T包含timeofday& text（C17，HH：mm：ss）& 或U包含日期和文本（B17，yyyy-mm-dd）和并且U包含timeofday和文本（C17，HH：mm：ss）& ;或V包含日期和文本（B17，yyyy-mm-dd）和，V包含timeofday和文本（C17，HH：mm：ss ）&或W包含日期和文本（B17，yyyy-mm-dd）和并且W包含timeofday和文本（C17，HH：mm： （B17，yyyy-mm-dd）&，X包含timeofday&;或X包含日期&文本（C17，HH： （B17，yyyy-mm-dd）&，Y包含timeofday& text（C17，mm：ss）&或Y包含日期 HH：mm：ss）&或Z包含日期和文本（B17，yyyy-mm-dd）&并且Z包含timeofday& text ，HH：mm：ss）&或AA包含日期和文本（B17，yyyy-mm-dd）&，AA包含timeofday&文本（C17，HH：mm：ss）&或AB包含日期和文本（B17，yyyy-mm-dd）&，AB包含timeofday& amp ;文本（C17，HH：mm：ss）和或AC包含日期和文本（B17，yyyy-mm-dd）&并且AC包含timeofday& text（C17，HH：mm：ss）&或AD包含日期& amp ;文本（B17，yyyy-mm-dd）&和AD包含timeofday& text（C17，HH：mm：ss）&或AE包含日期 & text（B17，yyyy-mm-dd）&和AE包含timeofday& text（C17，HH：mm：ss）&或AF包含日期& text（B17，yyyy-mm-dd）&和AF包含timeofday& text（C17，HH：mm：ss）&或AG包含日期和文本（B17，yyyy-mm-dd）和并且AG包含timeofday和文本（C17，HH：mm：ss）&或AH包含日期和文本（B17，yyyy-mm-dd）和并且AH包含timeofday和文本（C17，HH：mm：ss）& limit 2），）

我需要公式来显示前两个。什么可以改变这种显示方式？

请注意....我知道这是一个非常长的公式，并希望如何减少或以某种方式提出任何建议

解决方案

谢谢所有人

在此处找到解决方案 https：//produforforums。 google.com/forum/#!topic/docs/6XNpHLGOOpw

现在所有的工作都很精美。

< 0）的魔力：）

快乐的日子！

I had the formula below working beautifully a day ago.... where it would give me the first two results found and they would be in one cell under the other. I had to reset the form response destination but recreated it virtually as it was. After that change last night they started showing with a different format, where there were multiple names showing in 1 cell instead of 1 name in the first cell/row, then the second in the next cell/row.

"=IFERROR(QUERY('1ON1FORM'!$O$3:$AX, "select AX where O contains date """&text(B17,"yyyy-mm-dd")&""" and O contains timeofday """&text(C17,"HH:mm:ss")&""" or P contains date """&text(B17,"yyyy-mm-dd")&""" and P contains timeofday """&text(C17,"HH:mm:ss")&""" or Q contains date """&text(B17,"yyyy-mm-dd")&""" and Q contains timeofday """&text(C17,"HH:mm:ss")&""" or R contains date """&text(B17,"yyyy-mm-dd")&""" and R contains timeofday """&text(C17,"HH:mm:ss")&""" or S contains date """&text(B17,"yyyy-mm-dd")&""" and S contains timeofday """&text(C17,"HH:mm:ss")&""" or T contains date """&text(B17,"yyyy-mm-dd")&""" and T contains timeofday """&text(C17,"HH:mm:ss")&""" or U contains date """&text(B17,"yyyy-mm-dd")&""" and U contains timeofday """&text(C17,"HH:mm:ss")&""" or V contains date """&text(B17,"yyyy-mm-dd")&""" and V contains timeofday """&text(C17,"HH:mm:ss")&""" or W contains date """&text(B17,"yyyy-mm-dd")&""" and W contains timeofday """&text(C17,"HH:mm:ss")&""" or X contains date """&text(B17,"yyyy-mm-dd")&""" and X contains timeofday """&text(C17,"HH:mm:ss")&""" or Y contains date """&text(B17,"yyyy-mm-dd")&""" and Y contains timeofday """&text(C17,"HH:mm:ss")&""" or Z contains date """&text(B17,"yyyy-mm-dd")&""" and Z contains timeofday """&text(C17,"HH:mm:ss")&""" or AA contains date """&text(B17,"yyyy-mm-dd")&""" and AA contains timeofday """&text(C17,"HH:mm:ss")&""" or AB contains date """&text(B17,"yyyy-mm-dd")&""" and AB contains timeofday """&text(C17,"HH:mm:ss")&""" or AC contains date """&text(B17,"yyyy-mm-dd")&""" and AC contains timeofday """&text(C17,"HH:mm:ss")&""" or AD contains date """&text(B17,"yyyy-mm-dd")&""" and AD contains timeofday """&text(C17,"HH:mm:ss")&""" or AE contains date """&text(B17,"yyyy-mm-dd")&""" and AE contains timeofday """&text(C17,"HH:mm:ss")&""" or AF contains date """&text(B17,"yyyy-mm-dd")&""" and AF contains timeofday """&text(C17,"HH:mm:ss")&""" or AG contains date """&text(B17,"yyyy-mm-dd")&""" and AG contains timeofday """&text(C17,"HH:mm:ss")&""" or AH contains date """&text(B17,"yyyy-mm-dd")&""" and AH contains timeofday """&text(C17,"HH:mm:ss")&"""limit 2"),"")"

I need the formula to show the first two only. What could have changed the way this is displaying?

Note....I know this is an extremely long formula and would appreciate any suggestion on how to reduce it or somehow include it in a script but I gave up after I managed to get it working.

解决方案

Thanks All

Found the solution here https://productforums.google.com/forum/#!topic/docs/6XNpHLGOOpw

All working beautifully now.

The magic of 0 :)

Happy Days!

这篇关于Google表格查询停止按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！