> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.athenaintel.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.athenaintel.com/_mcp/server.

# Sheets API

> Programmatically read and modify Athena Sheets using the TypeScript SDK

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

```bash
pnpm add @athenaintel/sdk
```

#### bun

```bash
bun add @athenaintel/sdk
```

#### yarn

```bash
yarn add @athenaintel/sdk
```

#### npm

```bash
npm install @athenaintel/sdk
```

### Set Up Client

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
// 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:

```typescript
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:

```typescript
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:

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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).

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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.

```typescript
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:

```typescript
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:

```typescript
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`:

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

The `getTable` operation returns a `GetTableResponse`:

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

The `createTab` operation returns a `CreateNewSheetTabResponse`:

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

## Error Handling

Handle errors gracefully when working with sheets:

```typescript
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

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

## Next Steps

* Embed the complete spreadsheet experience with [Embed Athena Assets](/type-script-guides/embed-athena-assets)
* Explore the [full API reference](/api-reference) for detailed information
* Learn about [Agents](/typescript-guides/build-with-agents) for AI-powered automation
* Check out [file upload](/typescript-guides/upload-files) to work with local spreadsheets