使用通过Google表单提交的数据时= QUERY语法 [英] =QUERY syntax when working with data submitted via Google Form

问题描述

逐行"(手动填写时)下面的查询"下的查询，但是将新数据添加到响应表"时不会向下"查询.添加到响应表"中的数据来自Google表单.

The Query below 'works' on a line by line basis (when manually filled down), but it does not 'autofill' down when new data is added to the 'responses sheet'. The data being added to the 'responses sheet' comes from a Google form.

=QUERY(Sheet2!$A$1:K; CONCATENATE("SELECT B, C WHERE A = ", responses!B1), 0)

问题
一个-如何调整此查询，以便在Google表单将新数据添加到响应表"时将其自动填充"?

Question
One - How do I adjust this Query so it will 'autofill' down when new data is added to the 'responses sheet' by a Google form?

如果这不可能，那么有什么建议吗?

If this is not possible, any suggestions?

Here is a link to the Google Spreadsheet I'm working with

感谢您的时间和帮助，
托德
高中老师

Thank you for your time and assistance,
Todd
High School Teacher

推荐答案

另一种选择是使用

Another option is to use VLOOKUP applied as an array formula. This will auto-populate down the column as forms are submitted. So in row 2:

=ArrayFormula(IFERROR(VLOOKUP(B2:B;Sheet2!A2:C;SIGN(ROW(B2:B))*{2,3};0)))

或者您可以在第1行中输入此内容，该行也将填充标题，并且在某些情况下插入/删除行时会更加防水:

or you can enter this in row 1, which will also populate headers, and is slightly more watertight when rows are inserted/deleted in certain situations:

=ArrayFormula(IF(ROW(A:A)=1;{"First Name","Last Name"};IFERROR(VLOOKUP(B:B;Sheet2!A2:C;SIGN(ROW(B:B))*{2,3};0))))

这篇关于使用通过Google表单提交的数据时= QUERY语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！