=QUERY syntax when working with data submitted via Google Form

0
votes

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)

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

2
google-sheetsgoogle-forms
are you aware that you may have exposed personal data (names and email addresses of teachers) to public? or are they all made up for the example? (they do not seem to be) – törzsmókus

2 Answers

2
votes

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)))

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))))

0
votes

One way you can get around this is by using Google App Script (GAS).

What is possible is to setup an onEdit function that will add the formula to the required cell when new data is added.

Using a very quick bit of code (below). This will apply your formula when the spreadsheet is updated.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getSheetByName("responses");
  var lastRow = ws.getLastRow();
  var studentID = ws.getRange(lastRow, 4).setFormula('=QUERY(Sheet2!$A$1:K; CONCATENATE("SELECT B, C WHERE A = ", responses!B'+lastRow+'), 0)');
}

I've copied your spreadsheet and set it up here -> LINK You can test it by simply copying a new student ID into the B column and see that the student information is pulled from the second sheet.