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
  • Read Tabular Data - Extract data from CSV files and spreadsheets using the Data Frame API
  • 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

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:

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

Read Data from CSV Files

Extract data from CSV files with custom separators:

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

Advanced Data Frame Operations

Use column indices or names to select specific data:

1// Read specific columns by index
2const response = await client.tools.dataFrame({
3  asset_id: "your-file-asset-id",
4  columns: [0, 2, 4],    // Extract columns at indices 0, 2, 4
5  row_limit: 1000,
6});
7
8// Read specific columns by name (for files with headers)
9const response2 = await client.tools.dataFrame({
10  asset_id: "your-file-asset-id",
11  columns: ["Product", "Price", "Quantity"],
12  row_limit: 500,
13});
14
15// Process the returned data
16response.columns.forEach((col, idx) => {
17  console.log(`Column ${idx}: ${col}`);
18});
19
20response.data.forEach((row, rowIdx) => {
21  console.log(`Row ${response.index?.[rowIdx] ?? rowIdx}:`, row);
22});

Complete Example: Read and Transform CSV Data

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

1import { AthenaIntelligenceClient } from 'athena-intelligence';
2
3const client = new AthenaIntelligenceClient({
4  apiKey: process.env.ATHENA_API_KEY,
5});
6
7async function readAndProcessCSV() {
8  try {
9    // Read CSV file data
10    const csvData = await client.tools.dataFrame({
11      asset_id: "sales-data-csv-id",
12      separator: ",",
13      row_limit: 1000,
14      columns: ["Product", "Date", "Revenue", "Units"],
15    });
16
17    console.log("CSV Columns:", csvData.columns);
18
19    // Transform CSV data into structured objects
20    const records = csvData.data.map((row, idx) => {
21      return {
22        id: csvData.index?.[idx] ?? idx,
23        product: row[0],
24        date: row[1],
25        revenue: parseFloat(row[2]),
26        units: parseInt(row[3]),
27      };
28    });
29
30    // Calculate summary statistics
31    const totalRevenue = records.reduce((sum, r) => sum + r.revenue, 0);
32    const averageRevenue = totalRevenue / records.length;
33
34    console.log("Total Revenue:", totalRevenue);
35    console.log("Average Revenue:", averageRevenue);
36
37    return records;
38  } catch (error) {
39    console.error("Error reading CSV:", error);
40    throw error;
41  }
42}
43
44// Read and process Excel sheet
45async function readAndProcessExcel() {
46  try {
47    const excelData = await client.tools.dataFrame({
48      asset_id: "employee-registry-excel-id",
49      sheet_name: "Employees",
50      row_limit: 500,
51      columns: ["EmployeeID", "Name", "Department", "Salary"],
52    });
53
54    console.log("Excel Columns:", excelData.columns);
55
56    // Filter employees by department
57    const engineers = excelData.data
58      .map((row, idx) => ({
59        id: row[0],
60        name: row[1],
61        department: row[2],
62        salary: row[3],
63      }))
64      .filter(emp => emp.department === "Engineering");
65
66    console.log("Engineers:", engineers);
67    return engineers;
68  } catch (error) {
69    console.error("Error reading Excel:", error);
70    throw error;
71  }
72}
73
74// Run examples
75readAndProcessCSV().catch(console.error);
76readAndProcessExcel().catch(console.error);

Data Frame Response Structure

The dataFrame method returns a DataFrameResponse with the following structure:

1interface DataFrameResponse {
2  // Array of column headers or indices
3  columns: Array<string | number>;
4  
5  // 2D array of cell values
6  // Each row is an array of values (string, number, or null)
7  data: Array<Array<string | number | null>>;
8  
9  // Optional array of index values for rows
10  // Can be numbers, strings, or null
11  index: Array<string | number | null> | null;
12}

Data Frame Parameters

The dataFrame method accepts the following parameters:

1interface DataFrameRequest {
2  // Required: Asset ID of the file (CSV or spreadsheet)
3  asset_id: string;
4  
5  // Optional: Maximum number of rows to retrieve
6  row_limit?: number;
7  
8  // Optional: Column index to use as row index (1-based for Excel)
9  index_column?: number;
10  
11  // Optional: Specific columns to extract (array of column names or indices)
12  columns?: Array<string | number>;
13  
14  // Optional: Separator for CSV files (defaults to ",")
15  separator?: string;
16  
17  // Optional: Sheet name for Excel files (defaults to first sheet)
18  // Can also be a sheet index (number)
19  sheet_name?: string | number;
20}
3

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);
4

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);
5

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);
6

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);
7

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);
8

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);
9

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}
10

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}
11

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
12

Next Steps