Abstract

Google Sheets API now supports programmatic table management (create, delete, modify) as of April 29, 2025. This eliminates previous workarounds and enables direct control, including with Apps Script.

Introduction

Google Sheets tables can now be managed programmatically via the Sheets API, a significant update officially released on April 29, 2025. Ref I learned about this important development from Martin Hawksey’s Apps Script Pulse newsletter. Ref I am very grateful to Martin for bringing this to light. This update introduces the ability to programmatically manage tables directly through the Sheets API, enabling operations such as creating, deleting, and modifying tables and their properties. Previously, programmatic interaction with Sheets tables was limited and often required using workarounds for even simple management tasks, as explored in my earlier reports Ref and Ref. With this official API support, more robust and direct control is now possible. In this report, I will introduce how to manage tables on Google Sheets using the Sheets API, with examples implemented using Google Apps Script. It is worth noting, of course, that the Sheets API can also be used with other programming languages besides Apps Script.

Sample scripts

To test the following scripts, please create a sample Spreadsheet, open the script editor, and test the scripts using this sample Spreadsheet.

The Sheets API is used to manage data within Google Sheets. Therefore, please enable the Sheets API in Advanced Google services.

Additionally, the following sample scripts use UtlApp, a Google Apps Script library. Please also install it. Ref

1. Create a table

This script creates a new table. Although this script creates a simple table, of course, more complicated management can be achieved. You can see the official document. Ref

function myFunction() {
  // Created table name
  const tableName = "sample table";

  // Sheet name of the sheet that which the table is created.
  const sheetName = "Sheet1";

  // A1Notation that the table is created.
  const insertRange = "B3"; // A1Notation that the table is created.

  // Sample values
  const sampleValues = [
    ["a1", "b1", "c1"],
    ["a2", "b2", "c2"],
    ["a3", "b3", "c3"],
    ["a4", "b4", "c4"],
    ["a5", "b5", "c5"],
  ];

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const startCell = sheet.getRange(insertRange);
  const row = startCell.getRow();
  const col = startCell.getColumn();

  // Put values to the insert range.
  sheet
    .getRange(row, col, sampleValues.length, sampleValues[0].length)
    .setValues(sampleValues);

  // Create a table.
  const requests = [
    {
      addTable: {
        table: {
          name: tableName,
          range: {
            sheetId: sheet.getSheetId(),
            startRowIndex: row - 1,
            endRowIndex: row - 1 + sampleValues.length,
            startColumnIndex: col - 1,
            endColumnIndex: col - 1 + sampleValues[0].length,
          },
        },
      },
    },
  ];
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

When this script is run, the following result is obtained.

2. Get table list

This script retrieves all tables from all sheets in the Google Spreadsheet as a JSON object.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const obj = Sheets.Spreadsheets.get(ss.getId()).sheets.reduce((o, sheet) => {
    if (sheet.tables) {
      sheet.tables.forEach(
        ({ name, tableId, range }) => (o[name] = { tableId, range })
      );
    }
    return o;
  }, {});
  console.log(obj);
}

After the section “1. Create a table” was tested, the following result is shown in the console.

{
  "sample table": {
    "tableId": "###",
    "range": {
      "endRowIndex": 7,
      "startColumnIndex": 1,
      "endColumnIndex": 4,
      "startRowIndex": 2
    }
  }
}

3. Get values from table

This script retrieves values from a table using a table name.

function myFunction() {
  const tableName = "sample table";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const obj = Sheets.Spreadsheets.get(ss.getId()).sheets.reduce((o, sheet) => {
    if (sheet.tables) {
      sheet.tables.forEach(
        ({ name, range }) =>
          (o[name] = { range, sheetName: sheet.properties.title })
      );
    }
    return o;
  }, {});
  if (!obj[tableName]) {
    throw new Error(`No table of "${tableName}"`);
  }
  if (!obj[tableName].range.sheetId) {
    obj[tableName].range.sheetId = 0;
  }
  const a1Notation = UtlApp.convGridRangeToA1Notation(
    obj[tableName].range,
    obj[tableName].sheetName
  );
  const values = ss.getRange(a1Notation).getValues();
  console.log(values);
}

After the section “1. Create a table” was tested, the following result is shown in the console.

[
  ["a1", "b1", "c1"],
  ["a2", "b2", "c2"],
  ["a3", "b3", "c3"],
  ["a4", "b4", "c4"],
  ["a5", "b5", "c5"]
]

4. Delete tables

This script permanently deletes tables. Please be careful about this.

function myFunction() {
  // Table names. These tables are permanently deleted.
  const tableNames = ["sample table"];

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const obj = Sheets.Spreadsheets.get(ss.getId()).sheets.reduce((o, sheet) => {
    if (sheet.tables) {
      sheet.tables.forEach(({ name, tableId }) => (o[name] = tableId));
    }
    return o;
  }, {});
  const requests = tableNames.reduce((ar, name) => {
    if (obj[name]) {
      ar.push({ deleteTable: { tableId: obj[name] } });
    }
    return ar;
  }, []);
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

After the section “1. Create a table” was tested, when this script is run, the crated table is deleted.

5. Export tables as PDF

This script exports tables as PDF files.

In this script, the following class object is used. This class object updated from my previous report.

/**
 * Class object for converting tables on Spreadsheet to PDF blobs.
 * Author: Kanshi Tanaike
 * version: 1.0.1
 * @class
 */
class ConvertTablesToPDFBlobs {
  /**
   * @param {Object} object Object using this script.
   * @param {String} object.spreadsheetId Spreadsheet ID.
   */
  constructor(object = {}) {
    const { spreadsheetId } = object;

    /** @private */
    this.spreadsheet = spreadsheetId
      ? SpreadsheetApp.openById(spreadsheetId)
      : SpreadsheetApp.getActiveSpreadsheet();
    if (!this.spreadsheet) {
      throw new Error(
        `No Spreadsheet. Please set the spreadsheet ID like 'new ConvertTablesToPDFBlobs({ spreadsheetId: "###" })'`
      );
    }
    this.ssId = this.spreadsheet.getId();

    /** @private */
    this.endpoint = `https://docs.google.com/spreadsheets/d/${this.spreadsheet.getId()}/export`;

    /**
     * Please modify this to your actual situation.
     * This page will be useful. https://gist.github.com/andrewroberts/c37d45619d5661cab078be2a3f2fd2bb
     *
     * @private
     */
    this.query = {
      format: "pdf",
      gridlines: false,
      vertical_alignment: "MIDDLE",
      horizontal_alignment: "CENTER",
    };

    /** @private */
    this.headers = { authorization: "Bearer " + ScriptApp.getOAuthToken() };
  }

  /**
   * ### Description
   * Main method.
   *
   * @return {Array<Blob>} Return PDF blobs.
   */
  run(tableNames) {
    try {
      Sheets;
    } catch ({ message }) {
      if (message.includes("Sheets is not defined")) {
        throw new Error(
          "Please enable Sheets API at Advanced Google services. https://developers.google.com/apps-script/guides/services/advanced#enable_advanced_services"
        );
      }
    }
    try {
      UtlApp;
    } catch ({ message }) {
      if (message.includes("UtlApp is not defined")) {
        throw new Error(
          `Please install UtlApp of Google Apps Script library. The library key is "1idMI9-WtPMbYvbK5D7KH2_GWh62Dny9RG8NzjwjHI5whGIAPXEtTJmeC". https://github.com/tanaikech/UtlApp`
        );
      }
    }
    const a1Notations = this.getA1NotationsFromTables_(tableNames);
    return this.getPDFBlobsFromA1Notations(a1Notations);
  }

  /**
   * ### Description
   * Get tables.
   *
   * @return {Object} JSON object including tables.
   * @private
   */
  getTables_() {
    return Sheets.Spreadsheets.get(this.ssId).sheets.reduce((o, sheet) => {
      if (sheet.tables) {
        sheet.tables.forEach(
          ({ name, range }) =>
            (o[name] = { range, sheetName: sheet.properties.title })
        );
      }
      return o;
    }, {});
  }

  /**
   * ### Description
   * Get A1Notations from the inputted table names.
   *
   * @param {Array<String>} tableNames Array including table names on Spreadsheet.
   * @return {Array<String>} A1Notations.
   * @private
   */
  getA1NotationsFromTables_(tableNames) {
    const obj = this.getTables_();
    return tableNames.map((name) => {
      if (obj[name]) {
        if (!obj[name].range.sheetId) {
          obj[name].range.sheetId = 0;
        }
        return {
          tableName: name,
          a1Notation: UtlApp.convGridRangeToA1Notation(
            obj[name].range,
            obj[name].sheetName
          ),
          ok: true,
        };
      }
      return {
        tableName: name,
        a1Notation: "No table",
        ok: false,
      };
    });
  }

  /**
   * ### Description
   * Get PDF blobs from A1Notations.
   * This method can also be used for just converting A1Notations to PDF blobs.
   *
   * @param {Array<String>} a1Notations Array including a1Notations.
   * @return {Array<Blob>} Blobs.
   */
  getPDFBlobsFromA1Notations(a1Notations) {
    let requests = [];
    if (Array.isArray(a1Notations) && a1Notations.length > 0) {
      if (typeof a1Notations[0] == "object") {
        requests = a1Notations.reduce((ar, { tableName, a1Notation, ok }) => {
          if (ok) {
            const range = this.spreadsheet.getRange(a1Notation);
            const gid = range.getSheet().getSheetId();
            const query = {
              ...this.query,
              gid,
              range: a1Notation.split("!").pop(),
            };
            const url = UtlApp.addQueryParameters(this.endpoint, query);
            ar.push({ url, headers: this.headers, name: tableName });
          }
          return ar;
        }, []);
      } else if (typeof a1Notations[0] == "string") {
        requests = a1Notations.map((r) => {
          const range = this.spreadsheet.getRange(r);
          const gid = range.getSheet().getSheetId();
          const query = { ...this.query, gid, range: r };
          const url = UtlApp.addQueryParameters(this.endpoint, query);
          return { url, headers: this.headers, name: r };
        });
      }
    }
    if (requests.length > 0) {
      const res = UrlFetchApp.fetchAll(requests);
      const blobs = res.reduce((ar, r, i) => {
        if (r.getResponseCode() == 200) {
          const { name } = requests[i];
          ar.push(r.getBlob().setName(name));
        }
        return ar;
      }, []);
      return blobs;
    }
    return [];
  }
}

The main function is as follows.

function myFunction() {
  // Table names. These tables are permanently deleted.
  const tableNames = ["sample table"];

  const pdfBlobs = new ConvertTablesToPDFBlobs().run(tableNames);
  if (pdfBlobs.length == 0) return;
  pdfBlobs.forEach((blob) => DriveApp.createFile(blob));
}

After the section “1. Create a table” was tested, a PDF file including the table is created in the root folder.

Summary

Programmatic management of Google Sheets tables was officially supported by the Sheets API since April 29, 2025.
This new API support eliminated previous workarounds needed for handling table management tasks.
The report demonstrated using the updated Sheets API for managing tables via Google Apps Script examples.
Sample scripts showed how a table was created, listed, retrieved values from, and deleted.
An updated method for exporting Sheets tables as PDF files using the API support was also presented.

Managing Tables on Google Sheets using Google Apps Script