Sheets API | Athena | API Reference Docs

This guide shows how to work with Athena Sheets programmatically using the TypeScript SDK. The Sheets API provides comprehensive functionality for reading data, updating cells, managing tables, formatting, and more.

Key features:

Cell Operations - Update individual cells or ranges of cells
Table Management - Create, read, and modify structured tables
Formatting - Apply formatting to cells and ranges
Row & Column Operations - Insert, delete, and manage rows and columns
Sheet Management - Create, duplicate, and manage sheet tabs
Read Tabular Data - Extract data from CSV files and spreadsheets using the Data Frame API
Full TypeScript support - Complete type safety with proper interfaces

Install Package

pnpm

bun

yarn

npm

pnpm add @athenaintel/sdk

Set Up Client

import { AthenaIntelligenceClient } from '@athenaintel/sdk';
const client = new AthenaIntelligenceClient({
  apiKey: process.env.ATHENA_API_KEY,
});
// Access the sheets API
const sheets = client.tools.sheets;

Reading Tabular Data from Files

The Data Frame API allows you to read and extract structured data from CSV files and spreadsheets. This is useful for loading existing data into your application or processing file contents programmatically.

Read Data from Spreadsheets

Extract data from spreadsheet files:

const response = await client.tools.dataFrame({
  asset_id: "your-spreadsheet-asset-id",
  sheet_name: "Sheet1",  // Excel sheet name (optional, defaults to first sheet)
  row_limit: 100,        // Limit number of rows to retrieve (optional)
  columns: ["Name", "Email", "Department"], // Specific columns to extract (optional)
  index_column: 1,       // Column to use as index (optional, 1-based)
});
console.log(response.columns); // ["Name", "Email", "Department"]
console.log(response.data);    // [[row1_values], [row2_values], ...]
console.log(response.index);   // [0, 1, 2, ...] or custom index values

Read Data from CSV Files

Extract data from CSV files with custom separators:

const response = await client.tools.dataFrame({
  asset_id: "your-csv-file-asset-id",
  separator: ",",        // CSV separator (optional, defaults to ",")
  row_limit: 50,         // Limit rows to retrieve (optional)
  columns: [0, 1, 2],    // Column indices to extract (optional)
  index_column: 0,       // Use first column as index (optional)
});
console.log(response.columns); // Column headers
console.log(response.data);    // CSV data rows

Advanced Data Frame Operations

Use column indices or names to select specific data:

// Read specific columns by index
const response = await client.tools.dataFrame({
  asset_id: "your-file-asset-id",
  columns: [0, 2, 4],    // Extract columns at indices 0, 2, 4
  row_limit: 1000,
});
// Read specific columns by name (for files with headers)
const response2 = await client.tools.dataFrame({
  asset_id: "your-file-asset-id",
  columns: ["Product", "Price", "Quantity"],
  row_limit: 500,
});
// Process the returned data
response.columns.forEach((col, idx) => {
  console.log(`Column ${idx}: ${col}`);
});
response.data.forEach((row, rowIdx) => {
  console.log(`Row ${response.index?.[rowIdx] ?? rowIdx}:`, row);
});

Complete Example: Read and Transform CSV Data

Here’s a complete example that reads CSV data and transforms it:

import { AthenaIntelligenceClient } from '@athenaintel/sdk';
const client = new AthenaIntelligenceClient({
  apiKey: process.env.ATHENA_API_KEY,
});
async function readAndProcessCSV() {
  try {
    // Read CSV file data
    const csvData = await client.tools.dataFrame({
      asset_id: "sales-data-csv-id",
      separator: ",",
      row_limit: 1000,
      columns: ["Product", "Date", "Revenue", "Units"],
    });
    console.log("CSV Columns:", csvData.columns);
    // Transform CSV data into structured objects
    const records = csvData.data.map((row, idx) => {
      return {
        id: csvData.index?.[idx] ?? idx,
        product: row[0],
        date: row[1],
        revenue: parseFloat(row[2]),
        units: parseInt(row[3]),
      };
    });
    // Calculate summary statistics
    const totalRevenue = records.reduce((sum, r) => sum + r.revenue, 0);
    const averageRevenue = totalRevenue / records.length;
    console.log("Total Revenue:", totalRevenue);
    console.log("Average Revenue:", averageRevenue);
    return records;
  } catch (error) {
    console.error("Error reading CSV:", error);
    throw error;
  }
}
// Read and process Excel sheet
async function readAndProcessExcel() {
  try {
    const excelData = await client.tools.dataFrame({
      asset_id: "employee-registry-excel-id",
      sheet_name: "Employees",
      row_limit: 500,
      columns: ["EmployeeID", "Name", "Department", "Salary"],
    });
    console.log("Excel Columns:", excelData.columns);
    // Filter employees by department
    const engineers = excelData.data
      .map((row, idx) => ({
        id: row[0],
        name: row[1],
        department: row[2],
        salary: row[3],
      }))
      .filter(emp => emp.department === "Engineering");
    console.log("Engineers:", engineers);
    return engineers;
  } catch (error) {
    console.error("Error reading Excel:", error);
    throw error;
  }
}
// Run examples
readAndProcessCSV().catch(console.error);
readAndProcessExcel().catch(console.error);

Data Frame Response Structure

The dataFrame method returns a DataFrameResponse with the following structure:

interface DataFrameResponse {
  // Array of column headers or indices
  columns: Array<string | number>;
  
  // 2D array of cell values
  // Each row is an array of values (string, number, or null)
  data: Array<Array<string | number | null>>;
  
  // Optional array of index values for rows
  // Can be numbers, strings, or null
  index: Array<string | number | null> | null;
}

Data Frame Parameters

The dataFrame method accepts the following parameters:

interface DataFrameRequest {
  // Required: Asset ID of the file (CSV or spreadsheet)
  asset_id: string;
  
  // Optional: Maximum number of rows to retrieve
  row_limit?: number;
  
  // Optional: Column index to use as row index (1-based for Excel)
  index_column?: number;
  
  // Optional: Specific columns to extract (array of column names or indices)
  columns?: Array<string | number>;
  
  // Optional: Separator for CSV files (defaults to ",")
  separator?: string;
  
  // Optional: Sheet name for Excel files (defaults to first sheet)
  // Can also be a sheet index (number)
  sheet_name?: string | number;
}

Cell Operations

Update a Single Cell

Update the value of a specific cell in a spreadsheet.

const response = await client.tools.sheets.updateCell({
  asset_id: "your-spreadsheet-asset-id",
  row: 1,        // 1-based row index
  column: 1,     // 1-based column index (1 = column A)
  value: "Hello, World!",
  sheet_id: 1,   // Optional, defaults to 1
});
console.log(response.success); // true
console.log(response.message); // "Cell updated successfully"

Update a Range of Cells

Update multiple cells at once with a 2D array of values.

const response = await client.tools.sheets.updateRange({
  asset_id: "your-spreadsheet-asset-id",
  start_row: 1,
  start_column: 1,
  values: [
    ["Name", "Email", "Age"],
    ["John Doe", "john@example.com", "30"],
    ["Jane Smith", "jane@example.com", "25"],
  ],
  sheet_id: 1, // Optional
});
console.log(response.success);

Update Range with Formatting

Apply formatting while updating cell values.

const response = await client.tools.sheets.updateRange({
  asset_id: "your-spreadsheet-asset-id",
  start_row: 1,
  start_column: 1,
  values: [
    ["Header 1", "Header 2"],
    ["Value 1", "Value 2"],
  ],
  formatting: [
    [
      {
        backgroundColor: "#4285F4",
        textFormat: {
          bold: true,
          foregroundColor: "#FFFFFF",
        },
        horizontalAlignment: "center",
      },
      {
        backgroundColor: "#4285F4",
        textFormat: {
          bold: true,
          foregroundColor: "#FFFFFF",
        },
        horizontalAlignment: "center",
      },
    ],
    [undefined, undefined], // No formatting for data rows
  ],
});

Delete Cells

Delete cells in a specified range (shifts cells up or left).

const response = await client.tools.sheets.deleteCells({
  asset_id: "your-spreadsheet-asset-id",
  start_row_index: 1,
  start_column_index: 1,
  end_row_index: 5,
  end_column_index: 3,
  sheet_id: 1, // Optional
});
console.log(response.success);

Range Operations

Clear a Range

Clear the contents of cells in a range without deleting them.

const response = await client.tools.sheets.clearRange({
  asset_id: "your-spreadsheet-asset-id",
  start_row: 1,
  start_column: 1,
  num_rows: 10,
  num_columns: 5,
  sheet_id: 1, // Optional
});
console.log(response.success);

Format a Range

Apply formatting to a range of cells.

const response = await client.tools.sheets.formatRange({
  asset_id: "your-spreadsheet-asset-id",
  start_row: 1,
  start_column: 1,
  end_row: 1,
  end_column: 5,
  formatting: {
    backgroundColor: "#F4B400",
    textFormat: {
      bold: true,
      fontSize: 12,
      foregroundColor: "#000000",
    },
    horizontalAlignment: "center",
    verticalAlignment: "middle",
  },
  sheet_id: 1, // Optional
});
console.log(response.success);

Clear Formatting

Remove all formatting from a range of cells.

const response = await client.tools.sheets.clearFormatting({
  asset_id: "your-spreadsheet-asset-id",
  start_row_index: 1,
  start_column_index: 1,
  end_row_index: 10,
  end_column_index: 5,
  sheet_id: 1, // Optional
});
console.log(response.success);

Row and Column Operations

Insert a Row

Insert one or more new rows at a specific position.

const response = await client.tools.sheets.insertRow({
  asset_id: "your-spreadsheet-asset-id",
  reference_row_index: 5, // Insert before row 5
  num_rows: 1,            // Optional, defaults to 1
  sheet_id: 1,            // Optional
});
console.log(response.success);

Insert a Column

Insert a new column at a specific position.

const response = await client.tools.sheets.insertColumn({
  asset_id: "your-spreadsheet-asset-id",
  reference_column_index: 3, // Insert before column 3 (column C)
  sheet_id: 1,               // Optional
});
console.log(response.success);

Delete Columns

Delete one or more columns.

const response = await client.tools.sheets.deleteColumn({
  asset_id: "your-spreadsheet-asset-id",
  column_indexes: [2, 3, 4], // Delete columns B, C, and D
  sheet_id: 1,               // Optional
});
console.log(response.success);

Sheet Management

Create a New Sheet Tab

Add a new sheet tab to the spreadsheet.

const response = await client.tools.sheets.createTab({
  asset_id: "your-spreadsheet-asset-id",
  sheet: {
    sheetId: 2,
    title: "Q2 Data",
    index: 1,
    rowCount: 1000,
    columnCount: 26,
    tabColor: "#34A853", // Optional: green color
  },
});
console.log(response.success);
console.log(response.sheet_id); // ID of the newly created sheet

Duplicate a Sheet

Create a copy of an existing sheet.

const response = await client.tools.sheets.duplicateSheet({
  asset_id: "your-spreadsheet-asset-id",
  sheet_id: 1,           // Sheet to duplicate (defaults to 1)
  new_sheet_id: 10,      // Optional: ID for the new sheet
});
console.log(response.success);

Table Operations

Tables in Athena Sheets provide structured data with named columns, making it easier to work with data programmatically.

Create a Table

Create a new table from a range of cells.

const response = await client.tools.sheets.createTable({
  asset_id: "your-spreadsheet-asset-id",
  table_id: "employees_table",
  table_name: "Employees",
  start_row_index: 1,
  start_column_index: 1,
  end_row_index: 100,
  end_column_index: 5,
  sheet_id: 1, // Optional
});
console.log(response.success);

Get Table Data

Retrieve all data from a table including column names and rows.

const response = await client.tools.sheets.getTable({
  asset_id: "your-spreadsheet-asset-id",
  table_name: "Employees",
  table_id: "employees_table", // Optional
});
console.log(response.success);
console.log(response.columns); // [{ name: "Name" }, { name: "Email" }, ...]
console.log(response.rows);    // [{ Name: "John", Email: "john@..." }, ...]
// Access the data
response.rows.forEach(row => {
  console.log(`${row.Name}: ${row.Email}`);
});

Insert Table Row

Add new rows to a table with structured data.

const response = await client.tools.sheets.insertTableRow({
  asset_id: "your-spreadsheet-asset-id",
  table_name: "Employees",
  row_data: [
    {
      Name: "Alice Johnson",
      Email: "alice@example.com",
      Department: "Engineering",
      Salary: "120000",
    },
    {
      Name: "Bob Wilson",
      Email: "bob@example.com",
      Department: "Marketing",
      Salary: "95000",
    },
  ],
  table_id: "employees_table", // Optional
});
console.log(response.success);

Update Table

Modify the range of an existing table.

const response = await client.tools.sheets.updateTable({
  asset_id: "your-spreadsheet-asset-id",
  table_id: "employees_table",
  table_name: "Employees",
  start_row_index: 1,
  start_column_index: 1,
  end_row_index: 200,    // Expand table to 200 rows
  end_column_index: 7,    // Expand to 7 columns
  sheet_id: 1,           // Optional
});
console.log(response.success);

Insert Table Column

Add a new column to an existing table.

const response = await client.tools.sheets.insertTableColumn({
  asset_id: "your-spreadsheet-asset-id",
  table_id: "employees_table",
  dimension_index: 2,     // 0-based index within the table
  direction: "right",     // Insert to the right of the reference column
  sheet_id: 1,           // Optional
});
console.log(response.success);

Delete Table Column

Remove a column from a table.

const response = await client.tools.sheets.deleteTableColumn({
  asset_id: "your-spreadsheet-asset-id",
  table_id: "employees_table",
  dimension_index: 3,     // 0-based index within the table
  sheet_id: 1,           // Optional
});
console.log(response.success);

Complete Example: Managing Employee Data

Here’s a complete example that demonstrates multiple operations:

import { AthenaIntelligenceClient } from '@athenaintel/sdk';
const client = new AthenaIntelligenceClient({
  apiKey: process.env.ATHENA_API_KEY,
});
async function manageEmployeeSheet() {
  const assetId = "your-spreadsheet-asset-id";
  
  // Step 1: Create a header row with formatting
  await client.tools.sheets.updateRange({
    asset_id: assetId,
    start_row: 1,
    start_column: 1,
    values: [["Name", "Email", "Department", "Salary", "Start Date"]],
    formatting: [[
      {
        backgroundColor: "#4285F4",
        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
        horizontalAlignment: "center",
      },
      {
        backgroundColor: "#4285F4",
        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
        horizontalAlignment: "center",
      },
      {
        backgroundColor: "#4285F4",
        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
        horizontalAlignment: "center",
      },
      {
        backgroundColor: "#4285F4",
        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
        horizontalAlignment: "center",
      },
      {
        backgroundColor: "#4285F4",
        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
        horizontalAlignment: "center",
      },
    ]],
  });
  
  // Step 2: Create a table
  await client.tools.sheets.createTable({
    asset_id: assetId,
    table_id: "employees_table",
    table_name: "Employees",
    start_row_index: 1,
    start_column_index: 1,
    end_row_index: 1,
    end_column_index: 5,
  });
  
  // Step 3: Add employee data
  await client.tools.sheets.insertTableRow({
    asset_id: assetId,
    table_name: "Employees",
    row_data: [
      {
        Name: "John Doe",
        Email: "john@example.com",
        Department: "Engineering",
        Salary: "120000",
        "Start Date": "2024-01-15",
      },
      {
        Name: "Jane Smith",
        Email: "jane@example.com",
        Department: "Marketing",
        Salary: "95000",
        "Start Date": "2024-02-01",
      },
    ],
  });
  
  // Step 4: Retrieve the data
  const tableData = await client.tools.sheets.getTable({
    asset_id: assetId,
    table_name: "Employees",
  });
  
  console.log("Employee Data:");
  tableData.rows.forEach(row => {
    console.log(`${row.Name} - ${row.Department} - $${row.Salary}`);
  });
  
  // Step 5: Add a new column for performance rating
  await client.tools.sheets.insertColumn({
    asset_id: assetId,
    reference_column_index: 6,
  });
  
  await client.tools.sheets.updateCell({
    asset_id: assetId,
    row: 1,
    column: 6,
    value: "Performance Rating",
  });
  
  console.log("Employee sheet setup complete!");
}
manageEmployeeSheet().catch(console.error);

Type Definitions

Cell Format Options

The CellFormat interface provides extensive formatting options:

interface CellFormat {
  // Background color (hex string or theme color)
  backgroundColor?: string;
  
  // Border styling
  borders?: {
    top?: BorderStyle;
    bottom?: BorderStyle;
    left?: BorderStyle;
    right?: BorderStyle;
  };
  
  // Horizontal alignment
  horizontalAlignment?: "left" | "right" | "center";
  
  // Vertical alignment
  verticalAlignment?: "top" | "middle" | "bottom";
  
  // Indentation level
  indent?: number;
  
  // Number format pattern
  numberFormat?: {
    type: string;
    pattern?: string;
  };
  
  // Text formatting
  textFormat?: {
    bold?: boolean;
    italic?: boolean;
    strikethrough?: boolean;
    underline?: boolean;
    fontSize?: number;
    foregroundColor?: string;
    fontFamily?: string;
  };
  
  // Text rotation (angle in degrees or "vertical")
  textRotation?: number | "vertical";
  
  // Text wrapping strategy
  wrapStrategy?: "overflow" | "clip" | "wrap";
}

Response Types

All sheet operations return a SheetOperationResponse:

interface SheetOperationResponse {
  success: boolean;
  message: string;
  asset_id: string;
}

The getTable operation returns a GetTableResponse:

interface GetTableResponse {
  success: boolean;
  message: string;
  asset_id: string;
  columns: Record<string, string>[];
  rows: Record<string, unknown>[];
}

The createTab operation returns a CreateNewSheetTabResponse:

interface CreateNewSheetTabResponse {
  success: boolean;
  message: string;
  asset_id: string;
  sheet_id: number;
}

Error Handling

Handle errors gracefully when working with sheets:

import { AthenaIntelligenceError } from '@athenaintel/sdk';
try {
  const response = await client.tools.sheets.updateCell({
    asset_id: "invalid-asset-id",
    row: 1,
    column: 1,
    value: "Test",
  });
  
  if (!response.success) {
    console.error("Operation failed:", response.message);
  }
} catch (error) {
  if (error instanceof AthenaIntelligenceError) {
    console.error("API Error:", error.message);
    console.error("Status Code:", error.statusCode);
  } else {
    console.error("Unexpected error:", error);
  }
}

Best Practices

Use Tables for Structured Data: Tables provide better data management with named columns
Batch Operations: Use updateRange instead of multiple updateCell calls for better performance
Format Headers: Apply consistent formatting to header rows for better readability
Error Handling: Always check the success field in responses
Asset IDs: Store spreadsheet asset IDs securely and reuse them
Sheet IDs: Keep track of sheet IDs when working with multi-sheet spreadsheets
1-Based Indexing: Remember that rows and columns use 1-based indexing (row 1, column 1 = A1)
Table Operations: Use 0-based dimension_index for table column operations

Next Steps

Embed the complete spreadsheet experience with Embed Athena Assets
Explore the full API reference for detailed information
Learn about Agents for AI-powered automation
Check out file upload to work with local spreadsheets