Google-sheets – How to automatically refresh a filter view in Google Sheets when new data comes in via Google Forms

You could concatenate the strings, so that the row Male | City A is represented as MaleCity A, and count the occurrences of those strings.

Given that column B contains either Male or Female, and column C contains either City A or City B, you could count males/females in each city:

• Number of males in City A: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "MaleCity A")
• Number of females in City A: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "FemaleCity A")
• Number of males in City B: =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "MaleCity B")
• Number of females in City B =COUNTIF(ARRAYFORMULA(CONCAT(B2:B;C2:C)); "FemaleCity B")

The percentage of males/females in each city can then easily be calculated.

See the example spreadsheet I set up.

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.