Skip to main content
The Table class provides a database-like interface for managing Google Sheets, allowing you to create, modify, and manage sheet structures as if they were SQL tables.

​
Constructor

const table = new Table(name, columns, driver)
​
name
string
required
Name of the sheet/table
​
columns
array
Array of column names (headers) for the table
​
driver
SheetDriver
SheetDriver instance for sheet operations

​
Table Structure Methods

​
create()

Create a new sheet with headers or update existing headers. 
table.create(headers, overwrite)
​
headers
array
Array of header names. Defaults to this.columns if not provided
​
overwrite
boolean
default:false
If true, clears all data and rewrites headers. Use with caution
Example: 
const db = APPSQL.init({ spreadsheetId: "YOUR_ID" });
const users = db.table("Users");

// Create table with headers
users.create(["id", "name", "email", "age", "created_at"]);

​
drop()

Delete the sheet entirely. 
table.drop()
This operation is irreversible. The entire sheet will be permanently deleted.
Example: 
users.drop(); // Deletes the entire "Users" sheet

​
truncate()

Remove all data rows but keep the header row. 
table.truncate()
Example: 
users.truncate(); // Keeps headers, removes all data

​
ensureHeaders()

Ensure headers exist, creating the sheet if missing or updating headers while preserving data. 
table.ensureHeaders(headers)
​
headers
array
required
Array of header names to ensure
Example: 
// Safe operation - creates only if needed
users.ensureHeaders(["id", "name", "email"]);

​
Header Management

​
getHeaders()

Retrieve current headers as an array of strings. 
const headers = table.getHeaders()
Example: 
const headers = users.getHeaders();
Logger.log(headers); // ["id", "name", "email", "age"]

​
setHeaders()

Set new headers, optionally preserving or clearing existing data. 
table.setHeaders(headers, overwrite)
​
headers
array
required
New array of header names
​
overwrite
boolean
default:false
If true, clears all data. If false, preserves and remaps data to new headers
Example: 
// Preserve data, remap to new headers
users.setHeaders(["id", "full_name", "email_address", "age"]);

// Clear all data and set new headers
users.setHeaders(["new_id", "new_name"], true);

​
Column Management

​
addColumn()

Add a new column to the table. 
table.addColumn(name, index)
​
name
string
required
Name of the new column
​
index
number
Position to insert the column. If null or out of range, appends to the end
Example: 
// Add to the end
users.addColumn("phone");

// Add at specific position
users.addColumn("country", 2);

​
removeColumn()

Remove a column by name. 
table.removeColumn(name)
​
name
string
required
Name of the column to remove
Example: 
users.removeColumn("phone");

​
renameColumn()

Rename an existing column while preserving data. 
table.renameColumn(oldName, newName)
​
oldName
string
required
Current column name
​
newName
string
required
New column name
Example: 
users.renameColumn("name", "full_name");

​
Primary Key

​
setPrimaryKey()

Define the primary key column for the table. 
table.setPrimaryKey(name)
​
name
string
required
Name of the column to use as primary key
The default primary key is “id”. Change this if your table uses a different identifier column.
Example: 
users.setPrimaryKey("id"); // Default
users.setPrimaryKey("uuid"); // Custom primary key

// Now methods like findById use the "uuid" column
const user = users.findById("abc-123-def");

​
Configuration Properties

​
normalizeHeadersFlag

When true, headers are trimmed of whitespace. Default: true 
table.normalizeHeadersFlag = true;

​
lowercaseHeadersFlag

When true, headers are converted to lowercase. Default: false 
table.lowercaseHeadersFlag = false;

​
Complete Example

function setupDatabase() {
  const db = APPSQL.init({ spreadsheetId: "YOUR_ID" });
  const users = db.table("Users");

  // Create table structure
  users.create(["id", "name", "email"]);

  // Add new columns
  users.addColumn("age");
  users.addColumn("phone");
  users.addColumn("country", 3); // Insert at position 3

  // Rename columns
  users.renameColumn("name", "full_name");

  // Check structure
  const headers = users.getHeaders();
  Logger.log(headers);
  // ["id", "full_name", "email", "country", "age", "phone"]

  // Set primary key
  users.setPrimaryKey("id");

  // Remove unnecessary column
  users.removeColumn("phone");
}

​
Migration Example

function migrateTableStructure() {
  const db = APPSQL.init({ spreadsheetId: "YOUR_ID" });
  const oldTable = db.table("UsersOld");
  const oldData = oldTable.get();

  // Create new table with improved structure
  const newTable = db.table("Users");
  newTable.create(["id", "full_name", "email", "age", "status", "created_at"]);

  // Migrate and transform data
  oldData.forEach(oldRow => {
    newTable.insert({
      id: oldRow.id,
      full_name: `${oldRow.first_name} ${oldRow.last_name}`,
      email: oldRow.email.toLowerCase(),
      age: parseInt(oldRow.age) || 0,
      status: "active",
      created_at: new Date().toISOString()
    });
  });

  Logger.log(`Migrated ${oldData.length} records`);
}

​
Best Practices

DO: Define a clear structure from the start
users.create(["id", "name", "email", "age", "status", "created_at"]);
DO: Use ensureHeaders() for safe, idempotent operations
users.ensureHeaders(["id", "name", "email"]);
DON’T: Avoid using overwrite=true unless absolutely necessary
// ❌ Dangerous - loses all data
users.setHeaders(["new1", "new2"], true);

// ✅ Better - preserve data
users.renameColumn("old1", "new1");
users.renameColumn("old2", "new2");
DON’T: Never call drop() without user confirmation
const confirmed = Browser.msgBox(
  "Delete table?", 
  Browser.Buttons.YES_NO
);
if (confirmed === "yes") {
  users.drop();
}

​
Method Summary

CategoryMethods
Structurecreate(), ensureHeaders(), drop(), truncate()
HeadersgetHeaders(), setHeaders()
ColumnsaddColumn(), removeColumn(), renameColumn()
ConfigsetPrimaryKey(), setDriver()
PropertiesnormalizeHeadersFlag, lowercaseHeadersFlag

Build docs developers (and LLMs) love

Get started for freeTalk to us