Google Sheets tutorial: How to use to automatically create a new sheet with data from rows matching your query

Here are instructions on automatically creating separate sheets for each row matching your query.

In this example, we will automatically create separate sheets for each agent in the column “Agent Name” and populate data using Google Sheets’ FILTER function, you can use Google Apps Script. Follow these steps:

  1. Open your Google Sheet with the agent data.
  2. Click on “Extensions” in the top menu, then select Apps Script. If you don’t see Extensions, click on Tools and then Script editor.
  3. In the Apps Script editor, remove any existing code, and paste the following code:

function createSheetsWithFilterFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName('Main'); // Replace 'Main' with the name of your sheet containing the data.
  const data = mainSheet.getDataRange().getValues();
  const header = data.shift();
  const agentNameIndex = header.indexOf('Agent Name'); // Replace 'Agent Name' with the exact header name in your sheet.
  
  if (agentNameIndex === -1) {
    SpreadsheetApp.getUi().alert('Agent Name column not found. Please make sure the header name is correct.');
    return;
  }
  
  const uniqueAgentNames = [...new Set(data.map(row => row[agentNameIndex]))];
  
  uniqueAgentNames.forEach(agentName => {
    const agentSheet = ss.insertSheet(agentName);
    agentSheet.appendRow([agentName]);
    const filterFormula = `=FILTER(Main!A1:Z, Main!${String.fromCharCode(65 + agentNameIndex)}1:${String.fromCharCode(65 + agentNameIndex)} = A1)`;
    agentSheet.getRange('A2').setFormula(filterFormula);
  });
  
  SpreadsheetApp.getUi().alert('Sheets created for each agent with FILTER function.');
}
  1. Replace Main with the name of your sheet containing the data if it’s different.
  2. Replace Agent Name with the exact header name for the agent name column if it’s different.
  3. Save the script by clicking the floppy disk icon or by pressing Ctrl+S (or Cmd+S on Mac).
  4. Click on the Select function dropdown near the top of the script editor, then select createSheetsWithFilterFunction
  5. Click the triangular Run button (play icon) to execute the script.
  6. If prompted, grant the necessary permissions for the script to access your Google Sheet.

The script will create separate sheets for each agent in your main sheet, with the agent’s name as the sheet name. Each sheet will have the agent’s name in cell A1 and a FILTER formula in cell A2, which will display all data related to the agent, including the header row.

This approach is advantageous because any changes made to the data in the ‘Main’ sheet will be automatically reflected in the agent sheets, thanks to the FILTER function.

Here is a sample Google Sheet: here (just create copy of it by clicking here).

how to automatically create sheets with filters

Tagged: