Google-sheets – How to automatically expand a =QUERY when receiving submissions from Google Forms

google sheetsgoogle-apps-scriptgoogle-forms

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)

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.

Best Answer

You would need a Google Apps Script to automatically add your QUERY to new rows.

The following script runs when a form is submitted, and populates the D column with the formula from the previous row. That is, before running the script, you need to enter your formula in the first row.

function onFormSubmit(event) {
  Logger.log("Event: " + event.changeType);
  Logger.log("New row added");
  var sheet = event.range.getSheet();
  var addedRowIdx = event.range.getRow();
  Logger.log("Index of new row: " + addedRowIdx);
  var range = sheet.getRange(1, 4, sheet.getMaxRows(), 1); // 4 == fourth column == column D
  var queryColumnFormulas = range.getFormulasR1C1();
  for (var i = 1; i < queryColumnFormulas.length; i++) {
    if (queryColumnFormulas[i][0] == "") {
      Logger.log("Inserting formula in row " + i);
      queryColumnFormulas[i][0] = queryColumnFormulas[i - 1][0]; // Copy formula from previous row
    }
  range.setFormulasR1C1(queryColumnFormulas);
  }
}

To install this script, in your spreadsheet click Tools -> Script editor, and paste the code above. In the script editor, click Resources -> Current project's triggers. select the function onFormSubmit, and the event From spreadsheet: On form submit.

Feel free to look at the example spreadsheet and corresponding form I setup.

Related Topic