TypeScript Guides

Sheets API

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
  • Full TypeScript support - Complete type safety with proper interfaces
1

Install Package

$pnpm add athena-intelligence

Set Up Client

1import { AthenaIntelligenceClient } from 'athena-intelligence';
2
3const client = new AthenaIntelligenceClient({
4  apiKey: process.env.ATHENA_API_KEY,
5});
6
7// Access the sheets API
8const sheets = client.tools.sheets;
2

Cell Operations

Update a Single Cell

Update the value of a specific cell in a spreadsheet.

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

Update a Range of Cells

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

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

Update Range with Formatting

Apply formatting while updating cell values.

1const response = await client.tools.sheets.updateRange({
2  asset_id: "your-spreadsheet-asset-id",
3  start_row: 1,
4  start_column: 1,
5  values: [
6    ["Header 1", "Header 2"],
7    ["Value 1", "Value 2"],
8  ],
9  formatting: [
10    [
11      {
12        backgroundColor: "#4285F4",
13        textFormat: {
14          bold: true,
15          foregroundColor: "#FFFFFF",
16        },
17        horizontalAlignment: "center",
18      },
19      {
20        backgroundColor: "#4285F4",
21        textFormat: {
22          bold: true,
23          foregroundColor: "#FFFFFF",
24        },
25        horizontalAlignment: "center",
26      },
27    ],
28    [undefined, undefined], // No formatting for data rows
29  ],
30});

Delete Cells

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

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

Range Operations

Clear a Range

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

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

Format a Range

Apply formatting to a range of cells.

1const response = await client.tools.sheets.formatRange({
2  asset_id: "your-spreadsheet-asset-id",
3  start_row: 1,
4  start_column: 1,
5  end_row: 1,
6  end_column: 5,
7  formatting: {
8    backgroundColor: "#F4B400",
9    textFormat: {
10      bold: true,
11      fontSize: 12,
12      foregroundColor: "#000000",
13    },
14    horizontalAlignment: "center",
15    verticalAlignment: "middle",
16  },
17  sheet_id: 1, // Optional
18});
19
20console.log(response.success);

Clear Formatting

Remove all formatting from a range of cells.

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

Row and Column Operations

Insert a Row

Insert one or more new rows at a specific position.

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

Insert a Column

Insert a new column at a specific position.

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

Delete Columns

Delete one or more columns.

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

Sheet Management

Create a New Sheet Tab

Add a new sheet tab to the spreadsheet.

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

Duplicate a Sheet

Create a copy of an existing sheet.

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

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.

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

Get Table Data

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

1const response = await client.tools.sheets.getTable({
2  asset_id: "your-spreadsheet-asset-id",
3  table_name: "Employees",
4  table_id: "employees_table", // Optional
5});
6
7console.log(response.success);
8console.log(response.columns); // [{ name: "Name" }, { name: "Email" }, ...]
9console.log(response.rows);    // [{ Name: "John", Email: "john@..." }, ...]
10
11// Access the data
12response.rows.forEach(row => {
13  console.log(`${row.Name}: ${row.Email}`);
14});

Insert Table Row

Add new rows to a table with structured data.

1const response = await client.tools.sheets.insertTableRow({
2  asset_id: "your-spreadsheet-asset-id",
3  table_name: "Employees",
4  row_data: [
5    {
6      Name: "Alice Johnson",
7      Email: "alice@example.com",
8      Department: "Engineering",
9      Salary: "120000",
10    },
11    {
12      Name: "Bob Wilson",
13      Email: "bob@example.com",
14      Department: "Marketing",
15      Salary: "95000",
16    },
17  ],
18  table_id: "employees_table", // Optional
19});
20
21console.log(response.success);

Update Table

Modify the range of an existing table.

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

Insert Table Column

Add a new column to an existing table.

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

Delete Table Column

Remove a column from a table.

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

Complete Example: Managing Employee Data

Here’s a complete example that demonstrates multiple operations:

1import { AthenaIntelligenceClient } from 'athena-intelligence';
2
3const client = new AthenaIntelligenceClient({
4  apiKey: process.env.ATHENA_API_KEY,
5});
6
7async function manageEmployeeSheet() {
8  const assetId = "your-spreadsheet-asset-id";
9  
10  // Step 1: Create a header row with formatting
11  await client.tools.sheets.updateRange({
12    asset_id: assetId,
13    start_row: 1,
14    start_column: 1,
15    values: [["Name", "Email", "Department", "Salary", "Start Date"]],
16    formatting: [[
17      {
18        backgroundColor: "#4285F4",
19        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
20        horizontalAlignment: "center",
21      },
22      {
23        backgroundColor: "#4285F4",
24        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
25        horizontalAlignment: "center",
26      },
27      {
28        backgroundColor: "#4285F4",
29        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
30        horizontalAlignment: "center",
31      },
32      {
33        backgroundColor: "#4285F4",
34        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
35        horizontalAlignment: "center",
36      },
37      {
38        backgroundColor: "#4285F4",
39        textFormat: { bold: true, foregroundColor: "#FFFFFF" },
40        horizontalAlignment: "center",
41      },
42    ]],
43  });
44  
45  // Step 2: Create a table
46  await client.tools.sheets.createTable({
47    asset_id: assetId,
48    table_id: "employees_table",
49    table_name: "Employees",
50    start_row_index: 1,
51    start_column_index: 1,
52    end_row_index: 1,
53    end_column_index: 5,
54  });
55  
56  // Step 3: Add employee data
57  await client.tools.sheets.insertTableRow({
58    asset_id: assetId,
59    table_name: "Employees",
60    row_data: [
61      {
62        Name: "John Doe",
63        Email: "john@example.com",
64        Department: "Engineering",
65        Salary: "120000",
66        "Start Date": "2024-01-15",
67      },
68      {
69        Name: "Jane Smith",
70        Email: "jane@example.com",
71        Department: "Marketing",
72        Salary: "95000",
73        "Start Date": "2024-02-01",
74      },
75    ],
76  });
77  
78  // Step 4: Retrieve the data
79  const tableData = await client.tools.sheets.getTable({
80    asset_id: assetId,
81    table_name: "Employees",
82  });
83  
84  console.log("Employee Data:");
85  tableData.rows.forEach(row => {
86    console.log(`${row.Name} - ${row.Department} - $${row.Salary}`);
87  });
88  
89  // Step 5: Add a new column for performance rating
90  await client.tools.sheets.insertColumn({
91    asset_id: assetId,
92    reference_column_index: 6,
93  });
94  
95  await client.tools.sheets.updateCell({
96    asset_id: assetId,
97    row: 1,
98    column: 6,
99    value: "Performance Rating",
100  });
101  
102  console.log("Employee sheet setup complete!");
103}
104
105manageEmployeeSheet().catch(console.error);
8

Type Definitions

Cell Format Options

The CellFormat interface provides extensive formatting options:

1interface CellFormat {
2  // Background color (hex string or theme color)
3  backgroundColor?: string;
4  
5  // Border styling
6  borders?: {
7    top?: BorderStyle;
8    bottom?: BorderStyle;
9    left?: BorderStyle;
10    right?: BorderStyle;
11  };
12  
13  // Horizontal alignment
14  horizontalAlignment?: "left" | "right" | "center";
15  
16  // Vertical alignment
17  verticalAlignment?: "top" | "middle" | "bottom";
18  
19  // Indentation level
20  indent?: number;
21  
22  // Number format pattern
23  numberFormat?: {
24    type: string;
25    pattern?: string;
26  };
27  
28  // Text formatting
29  textFormat?: {
30    bold?: boolean;
31    italic?: boolean;
32    strikethrough?: boolean;
33    underline?: boolean;
34    fontSize?: number;
35    foregroundColor?: string;
36    fontFamily?: string;
37  };
38  
39  // Text rotation (angle in degrees or "vertical")
40  textRotation?: number | "vertical";
41  
42  // Text wrapping strategy
43  wrapStrategy?: "overflow" | "clip" | "wrap";
44}

Response Types

All sheet operations return a SheetOperationResponse:

1interface SheetOperationResponse {
2  success: boolean;
3  message: string;
4  asset_id: string;
5}

The getTable operation returns a GetTableResponse:

1interface GetTableResponse {
2  success: boolean;
3  message: string;
4  asset_id: string;
5  columns: Record<string, string>[];
6  rows: Record<string, unknown>[];
7}

The createTab operation returns a CreateNewSheetTabResponse:

1interface CreateNewSheetTabResponse {
2  success: boolean;
3  message: string;
4  asset_id: string;
5  sheet_id: number;
6}
9

Error Handling

Handle errors gracefully when working with sheets:

1import { AthenaIntelligenceError } from 'athena-intelligence';
2
3try {
4  const response = await client.tools.sheets.updateCell({
5    asset_id: "invalid-asset-id",
6    row: 1,
7    column: 1,
8    value: "Test",
9  });
10  
11  if (!response.success) {
12    console.error("Operation failed:", response.message);
13  }
14} catch (error) {
15  if (error instanceof AthenaIntelligenceError) {
16    console.error("API Error:", error.message);
17    console.error("Status Code:", error.statusCode);
18  } else {
19    console.error("Unexpected error:", error);
20  }
21}
10

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
11

Next Steps