Sheets API

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

1
const 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
9
console.log(response.columns); // ["Name", "Email", "Department"]
10
console.log(response.data);    // [[row1_values], [row2_values], ...]
11
console.log(response.index);   // [0, 1, 2, ...] or custom index values

1
const 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
9
console.log(response.columns); // Column headers
10
console.log(response.data);    // CSV data rows

1
// Read specific columns by index
2
const 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)
9
const 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
16
response.columns.forEach((col, idx) => {
17
  console.log(`Column ${idx}: ${col}`);
18
});
19
20
response.data.forEach((row, rowIdx) => {
21
  console.log(`Row ${response.index?.[rowIdx] ?? rowIdx}:`, row);
22
});

1
import { AthenaIntelligenceClient } from '@athenaintel/sdk';
2
3
const client = new AthenaIntelligenceClient({
4
  apiKey: process.env.ATHENA_API_KEY,
5
});
6
7
async 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
45
async 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
75
readAndProcessCSV().catch(console.error);
76
readAndProcessExcel().catch(console.error);

1
interface 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
}

1
interface 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
}

1
const 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
9
console.log(response.success); // true
10
console.log(response.message); // "Cell updated successfully"

1
const 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
13
console.log(response.success);

1
const 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
});

1
const 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
10
console.log(response.success);

1
const 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
10
console.log(response.success);

1
const 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
20
console.log(response.success);

1
const 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
10
console.log(response.success);

1
const 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
8
console.log(response.success);

1
const 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
7
console.log(response.success);

1
const 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
7
console.log(response.success);

1
const 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
13
console.log(response.success);
14
console.log(response.sheet_id); // ID of the newly created sheet

1
const 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
7
console.log(response.success);

1
const 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
12
console.log(response.success);

1
const 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
7
console.log(response.success);
8
console.log(response.columns); // [{ name: "Name" }, { name: "Email" }, ...]
9
console.log(response.rows);    // [{ Name: "John", Email: "john@..." }, ...]
10
11
// Access the data
12
response.rows.forEach(row => {
13
  console.log(`${row.Name}: ${row.Email}`);
14
});

1
const 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
21
console.log(response.success);

1
const 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
12
console.log(response.success);

1
const 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
9
console.log(response.success);

1
const 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
8
console.log(response.success);

1
import { AthenaIntelligenceClient } from '@athenaintel/sdk';
2
3
const client = new AthenaIntelligenceClient({
4
  apiKey: process.env.ATHENA_API_KEY,
5
});
6
7
async 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
105
manageEmployeeSheet().catch(console.error);

1
interface 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
}

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

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

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

1
import { AthenaIntelligenceError } from '@athenaintel/sdk';
2
3
try {
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
}