---
title: "Automate creating or updating tables in Google Sheets with Apps Script🚀"
author: "Andrea Guerri"
date: 2025-09-15T19:01:24+00:00
canonical_url: https://pulse.appsscript.info/p/2025/09/automate-creating-or-updating-tables-in-google-sheets-with-apps-script%f0%9f%9a%80/
markdown_url: https://pulse.appsscript.info/p/2025/09/automate-creating-or-updating-tables-in-google-sheets-with-apps-script%f0%9f%9a%80.md
categories:
  - "Intermediate"
  - "Sheets"
  - "Solutions"
tags:
  - "Andrea Guerri"
featured_image: https://pulse.appsscript.info/files/2025/09/Imagine.png
description: "Did you know you can fully automate creating and updating tables in Google Sheets? In this article, Andrea Guerri breaks down two powerful Apps Script methods that save you time and ensure data accuracy. The post explores the pros and cons of each, helping you choose the best solution for your needs, including all the code you need to get started."
---

# Automate creating or updating tables in Google Sheets with Apps Script🚀

![Automate creating or updating tables in Google Sheets with Apps Script🚀](https://pulse.appsscript.info/files/2025/09/Imagine.png)

## 

![](https://pulse.appsscript.info/files/2025/09/image-1024x570.png)

**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****](https://docs.google.com/spreadsheets/d/10Hm5zBYaHFK_7_NAy2o-YUfV_uZ74wwOdj9oCy9Q4_g/copy)

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](https://www.youtube.com/watch?v=-w9t9XxfV7w)
- Data aggregation in tables: [View on YouTube](https://www.youtube.com/watch?v=-w9t9XxfV7w)

---

### **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.

---

*Published by [Andrea Guerri](https://pulse.appsscript.info/p/author/andreaguerri/) on [Pulse](https://pulse.appsscript.info/). Original article: [https://pulse.appsscript.info/p/2025/09/automate-creating-or-updating-tables-in-google-sheets-with-apps-script%f0%9f%9a%80/](https://pulse.appsscript.info/p/2025/09/automate-creating-or-updating-tables-in-google-sheets-with-apps-script%f0%9f%9a%80/)*