A step-by-step guide to dynamically import, filter, and format your data.

How many times have you found yourself copying data from one sheet to another, applying filters, and then manually formatting everything as a table to make it more readable and functional? It is a sometimes tedious and error-prone process.

What if we could automate all of this with a single click? Welcome to the world of Google Apps Script!

In this article, we will analyze a script that not only imports and filters data between two spreadsheets but also uses the power of the Sheets API to dynamically create a formatted table. Ready? Let’s start! 🚀

What does the script do?

In summary, our script performs these steps in sequence:

• Connects to a source spreadsheet (even one different from where the script is located).
• Imports all the data from the specified sheet.
• Filters the rows based on a specific condition (in our case, a numeric value in a column).
• Clears the destination sheet and writes the filtered data to it.
• Creates (or recreates) a formatted table that perfectly adapts to the size of the new data.

The Sample File

To follow this tutorial, you can create a copy of the sample spreadsheet:

➡️ Click here to create a copy of the sample file

The file contains the source sheet DataSet with the starting data. In your destination sheet, you can, for example, use a sheet named Filtered where the filtered data will be inserted. Nothing prevents you from using this script to filter and create a table in the same file. In any case, for my example, you will need the file I linked and a second file where you import this data.

In the script we will analyze, the filter is applied to the “Billing” column, selecting only rows with a value greater than 2000.

✨Extra: If you want to learn how to use tables:

• Working with Google Sheets tables: View on YouTube
• Data aggregation in tables: View on YouTube

Preparation: Enable the Sheets API

Before starting, there is a fundamental step. Our script uses advanced methods that are not part of the standard SpreadsheetApp service, but of the advanced Sheets API service.

To enable it:

• Open the Apps Script editor.
• In the left menu, click on Services (+).
• Find Google Sheets API in the list, select it, and click Add.

Perfect! Now your script has the “superpowers” to manipulate tables, conditional formatting, and much more.

Script Analysis

Here is the complete script that you will need to copy and paste into your file:

function importFilterCreateTable() {
    const idOrigin = "ID_FILE"; // Source File ID
    const nameOrigin = "DataSet"; // source sheet name
    const nameDest = "Filtered" // destination sheet name
    const indexCol = "Billing" // name of Column to filter
    const nameTable = "TBL_Filtered" // name of the table

    const ss = SpreadsheetApp.getActive();
    const sheetOrigin = SpreadsheetApp.openById(idOrigin).getSheetByName(nameOrigin);
    const sheetDest = ss.getSheetByName(nameDest);
    const destID = sheetDest.getSheetId();

    // Import & Filter the data
    const values = sheetOrigin.getDataRange().getValues();

    const col = values[0].indexOf(indexCol); // Find the column number to filter

    const filterValues = values.filter((rw, h) => h === 0 || rw[col] > 2000); //Filter by condition

    if (!filterValues.length) return;

    // Retrieve the table data via the Sheets API
    const ssObj = Sheets.Spreadsheets.get(ss.getId(), { includeGridData: false });
    const sheetObj = ssObj.sheets.find(s => s.properties.sheetId === destID);

    const requests = [];

    // Checks if the table exists, if so it deletes it
    if (sheetObj.tables) {
        const table = sheetObj.tables[0];
        requests.push({ deleteTable: { tableId: table.tableId } });
    }

    // Adds the new data
    sheetDest.getRange(1, 1, filterValues.length, filterValues[0].length).setValues(filterValues);

    // Adds a new table
    requests.push({
        addTable: {
            table: {
                name: nameTable,
                range: {
                    sheetId: destID,
                    startRowIndex: 0,
                    endRowIndex: filterValues.length,
                    startColumnIndex: 0,
                    endColumnIndex: filterValues[0].length
                }
            }
        }
    });

    if (requests.length) {
        Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
    }
}

💪🏻Let’s analyze the interesting part about the table.

The Creation of the Table

This is the most interesting and powerful part of the script. The standard SpreadsheetApp service does not have a direct method like sheet.createTable(). To do this, we need to speak directly with the Google Sheets API.

How does it work?

• Sheets.Spreadsheets.get(ss.getId(), { includeGridData: false }); With this line, we ask the API to provide us with all the properties of our spreadsheet (like the sheets within it, their IDs, and of course, the existing tables), but without downloading the cell data (includeGridData: false), making the call much faster.
• ssObj.sheets.find(s => s.properties.sheetId === destID); From the object we have received, we look for the specific sheet we are working on by comparing its ID (destID).
• Preventive deletion: if (sheetObj.tables) Our script is designed to be run multiple times. For simplicity, we delete the old table before creating a new one. How? This block of code checks if a table already exists in the sheetObj sheet (sheetObj.tables). If so, it adds a deletion request (deleteTable) to an array of “requests” (requests).
• Creation: requests.push({ addTable: { ... } }); We add a second request to our array: the creation of the new table (addTable). Here we define all its properties:
  • name: The unique name of the table.
  • range: The cell range it will occupy. It is crucial to note that the API uses “zero-based” indexes (the first row/column is 0) and the final index (endRowIndex) is excluded. This is why filterValues.length works perfectly to define the last row.

📌 In this example, the table will be placed in cell A1. If you want the table to appear in a different row, for example in row 3 of a column, you must set startRowIndex: 2 and endRowIndex: filterValues.length + 2. If you want it in a different column instead, for example in column D, you will have to put startColumnIndex: 3 and endColumnIndex: filterValues[0].length + 3.

• Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId()); This is the final call. Instead of making one API call to delete and another to create, we group them into a single batchUpdate. It is more efficient and ensures that the operations are performed in sequence.

This “delete and recreate” approach is extremely robust and ensures that the table always has the exact dimensions of the filtered data. The previous data? It will be removed when we delete the table. In fact, it is only after possibly deleting the table that the new data will be inserted.

Updating an Existing Table

What if we just wanted to update the dimensions of an existing table instead of deleting and recreating it? In this case, the import and filter logic remains the same, but the table manipulation part changes.

This method becomes useful when your table already has a structure that you want to preserve (special formatting, data validation, drop-down menus, etc.) and you just want to update its data and dimensions.

Here is what the final part of the script would look like in this case:

    // Delete the values
    sheetDest.getRange(2, 1, sheetDest.getMaxRows(), sheetDest.getMaxColumns()).clearContent()

    // Insert new values
    sheetDest.getRange(2, 1, filterValues.length, filterValues[0].length).setValues(filterValues);

    // Retrieve the table data via the Sheets API
    const obj = Sheets.Spreadsheets.get(ss.getId(), { includeGridData: false });
    const sheetObj = obj.sheets.find(s => s.properties.sheetId === destID)

    const tableID = sheetObj.tables[0].tableId

    const requests = [{
        updateTable: {
            table: {
                tableId: tableID,
                range: {
                    sheetId: destID,
                    startRowIndex: 0,
                    endRowIndex: sheetDest.getLastRow(),
                    startColumnIndex: 0,
                    endColumnIndex: sheetDest.getLastColumn()
                }
            },
            fields: "range"
        }
    }];

    Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());

What changes?

• Instead of deleting and creating, we use an updateTable request.
• We must specify the ID of the table to be modified (tableId).
• The fields: "range" field is crucial: it tells the API to update only and exclusively the range property of the table, leaving everything else (like the name) unchanged.

Conclusion

Which method to choose? It always depends on the use you want to make of it, so it depends a lot on your needs. Remembering that the “delete and recreate” method of the first script is generally used if we always want to have a new and clean table, while the second case is useful when we have already structured our table.

Automating tasks in Google Sheets with Apps Script can save you hours of work. Learning to use the Sheets API opens up a world of possibilities that go far beyond simple data manipulation, allowing you to programmatically control almost every aspect of your spreadsheets.