Simplify Google Sheets Tables Management with Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

Abstract

This article introduces “TableApp,” a Google Apps Script library designed to simplify managing Google Sheets Tables. It addresses the complexity of the native Sheets API, providing an intuitive interface for creating, updating, and manipulating tables. Sample scripts and installation guides are included to ensure easy implementation.

Introduction

The introduction of Tables in Google Sheets has significantly enhanced data management capabilities. While these tables can be managed via the Sheets API, the process is often complex and verbose. I previously discussed this in my article, Managing Tables on Google Sheets using Google Apps Script.

Following that publication, I received feedback from users requesting a simpler way to handle the Sheets API for Tables. To address this, I developed TableApp, a Google Apps Script library that wraps the complex API calls into simple, intuitive methods.

This article outlines how to install the library and provides practical sample scripts for common operations.

Repository

The source code and documentation are available on GitHub: https://github.com/tanaikech/TableApp

Library Installation & Setup

1. Prepare Google Sheets

Create a new Google Spreadsheet and open the Script Editor.

2. Install the Library

Install the library using the script ID below. If you are unfamiliar with installing libraries, please refer to the Google Apps Script Library Guide.

Library Script ID:

1G4RVvyLtwPjQl6x_p8j3X65-yYVU3w2dMXxHDuzCgorucjs8P3Clv5Qt

3. Enable Google Sheets API

This library requires the Google Sheets API service.

  1. In the Script Editor, click the + button next to Services (left sidebar).
  2. Select Google Sheets API.
  3. Click Add.

Authorization Scopes

Depending on your usage, you may need to set explicit scopes in your manifest (appsscript.json).

  • Read Only: https://www.googleapis.com/auth/spreadsheets.readonly: Use this if you only use get methods.
  • Read/Write: https://www.googleapis.com/auth/spreadsheets: Use this for all methods, including create, update, and delete.

Practical Examples

1. Create a Table

This script creates a new sheet and converts a data range into a Table.

function sample1() {
  const sampleData = [
    ["ID", "Product", "Price", "Stock"],
    [101, "Apple", 1.5, 100],
    [102, "Banana", 0.8, 200],
    [103, "Cherry", 5.0, 50],
  ];
  const tableName = "sampleTable";
  const sheetName = "sampleSheet";

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = spreadsheet.getId();
  const sheet = spreadsheet.getSheets()[0].setName(sheetName);
  const range = sheet
    .getRange(1, 1, sampleData.length, sampleData[0].length)
    .setValues(sampleData);
  const table = TableApp.openById(ssId)
    .getSheetByName(sheetName)
    .getRange(range.getA1Notation())
    .create(tableName);
  console.log(table.getName()); // sampleSheet
}

Result:

A new table is created as shown below:

2. Get Tables and Values

Note: This script assumes sample1 has already been executed.

This example demonstrates how to retrieve table metadata and values using the library.

function sample2() {
  const tableName = "sampleTable";
  const sheetName = "sampleSheet";

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = spreadsheet.getId();
  const T = TableApp.openById(ssId);

  // Get all tables in this Spreadsheet.
  const tables = T.getTables();
  const tableNames = tables[sheetName].tables.map((table) => table.getName());
  console.log(tableNames); // [ 'sampleTable' ]

  // Get table by table name.
  const table1 = T.getTableByName(tableName);
  const tableId = table1.getId();
  console.log(tableId); // Return your table ID.

  // Get table by table ID.
  const table2 = T.getTableById(tableId);
  console.log(table2.getName()); // sampleTable

  // Get values from table.
  const values = table2.getValues();
  console.log(values); // sampleData in "sample1" is retrieved.
}

3. Rename and Update Tables

Note: This script assumes sample1 has already been executed.

3a. Rename Table and Update Data

You can rename the table object and overwrite its data in one sequence.

function sample3a() {
  const tableName = "sampleTable";
  const renamed = "updateTable";
  const newSampleData = [
    ["ID", "Product", "Price", "Stock"],
    [101, "Apple", 1.5, 1000],
    [102, "Banana", 0.8, 2000],
    [103, "Cherry", 5.0, 500],
  ];

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = spreadsheet.getId();
  const T = TableApp.openById(ssId);
  const table = T.getTableByName(tableName);

  // Rename table
  table.setName(renamed);

  // Update values
  table.setValues(newSampleData);
}

Result:

The table name and cell values are updated:

3b. Resize Table Range

If you need to expand or shrink the table range without changing data values:

function sample3b() {
  const tableName = "updateTable";

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = spreadsheet.getId();
  const T = TableApp.openById(ssId);
  const table = T.getTableByName(tableName);

  // Resize table
  table.setRange("A1:D10");
}

Result:

The table range is expanded:

4. Remove Table

This method completely deletes the table object and clears the cell values within that range.

function sample4() {
  const tableName = "sampleTable";

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = spreadsheet.getId();
  const T = TableApp.openById(ssId);
  const table = T.getTableByName(tableName);

  // Remove table
  table.remove();
}

5. Copy Table

Note: This script assumes sample1 has already been executed.

This copies an existing table to a specific location (e.g., cell “F1”) and assigns it a new name.

function sample5() {
  const tableName = "sampleTable";

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = spreadsheet.getId();
  const T = TableApp.openById(ssId);
  const table = T.getTableByName(tableName);

  // Copy table
  table.copyTo("F1").setName("CopiedTable");
}

Result:

The table is duplicated at the target location:

6. Reverse Table (Convert to Range)

Note: This script assumes sample1 has already been executed.

In the native Sheets API, deleting a table often deletes the data within it. This library provides a reverse() method that removes the “Table” structure but retains the cell values and formatting, effectively converting it back to a standard range.

function sample6() {
  const tableName = "sampleTable";

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = spreadsheet.getId();
  const T = TableApp.openById(ssId);
  const table = T.getTableByName(tableName);

  // Reverse table
  table.reverse();
}

Result:

The table structure is removed, but the data remains.

Before (Table):

After (Standard Range):

Summary

  • Simplified Management: TableApp acts as a wrapper for the Google Sheets API, reducing the complexity of managing Table objects.
  • Easy Installation: The library can be easily added to any Apps Script project using the provided Script ID.
  • API Requirement: Users must enable the “Google Sheets API” service in the Script Editor to utilize the library.
  • Comprehensive Features: It supports creating, retrieving, updating, copying, and deleting tables with minimal code.
  • Safe Reversion: The unique reverse() method allows users to convert Tables back to standard ranges without losing data, addressing a limitation in the native API delete method.

 Share!