# Google Sheets Client

Interact with Google Sheets with full type safety and runtime validation.

## Methods

| Method                                   | Description                       |
| ---------------------------------------- | --------------------------------- |
| `run(action, schema, params, metadata?)` | Execute a Google Sheets operation |

## Actions

| Action                    | Description                        |
| ------------------------- | ---------------------------------- |
| `READ_SPREADSHEET`        | Read all data from a sheet         |
| `READ_SPREADSHEET_RANGE`  | Read data from a range             |
| `APPEND_SPREADSHEET`      | Append rows to a sheet             |
| `CREATE_SPREADSHEET_ROWS` | Create rows at a specific position |
| `CLEAR_SPREADSHEET`       | Clear all data from a sheet        |
| `CREATE_WORKSHEET`        | Add a new sheet to a spreadsheet   |

## Usage

### Read Spreadsheet Data

```typescript
import { api, z, googleSheets } from "@superblocksteam/sdk-api";

// Integration ID from the integrations panel
const PROD_GSHEETS = "a1b2c3d4-5678-90ab-cdef-gsheets00001";

const RowSchema = z.array(
  z.object({
    name: z.string(),
    email: z.string(),
    status: z.string(),
  }),
);

export default api({
  integrations: {
    sheets: googleSheets(PROD_GSHEETS),
  },
  name: "GoogleSheetsExample",
  input: z.object({
    spreadsheetId: z.string(),
  }),
  output: z.object({
    rows: RowSchema,
  }),
  async run(ctx, { spreadsheetId }) {
    const rows = await ctx.integrations.sheets.run(
      "READ_SPREADSHEET_RANGE",
      RowSchema,
      {
        spreadsheetId: spreadsheetId,
        sheetTitle: "Sheet1",
        range: "A1:C100",
        extractFirstRowHeader: true,
      },
    );

    return { rows };
  },
});
```

### Append Rows

```typescript
await ctx.integrations.sheets.run("APPEND_SPREADSHEET", z.any(), {
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheetTitle: "Sheet1",
  data: JSON.stringify([
    { name: "John Doe", email: "john@example.com", status: "active" },
    { name: "Jane Doe", email: "jane@example.com", status: "pending" },
  ]),
});
```

### Create Rows at Position

```typescript
await ctx.integrations.sheets.run("CREATE_SPREADSHEET_ROWS", z.any(), {
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheetTitle: "Sheet1",
  rowNumber: "5",
  data: JSON.stringify([
    { name: "New Row", email: "new@example.com", status: "active" },
  ]),
});
```

### Clear Sheet

```typescript
await ctx.integrations.sheets.run("CLEAR_SPREADSHEET", z.any(), {
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  sheetTitle: "Sheet1",
  preserveHeaderRow: true,
});
```

### Create New Worksheet

```typescript
await ctx.integrations.sheets.run("CREATE_WORKSHEET", z.any(), {
  spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  addSheet: {
    sheetTitle: "New Sheet",
    rowCount: "100",
    columnCount: "26",
  },
});
```

## Parameters

| Parameter               | Type      | Description                           |
| ----------------------- | --------- | ------------------------------------- |
| `spreadsheetId`         | `string`  | The spreadsheet ID (required)         |
| `sheetTitle`            | `string`  | Sheet name/title                      |
| `range`                 | `string`  | Range in A1 notation (e.g., "A1:D10") |
| `data`                  | `string`  | JSON string of data to write          |
| `extractFirstRowHeader` | `boolean` | Treat first row as headers            |
| `headerRowNumber`       | `string`  | Header row number                     |
| `preserveHeaderRow`     | `boolean` | Preserve headers when clearing        |
| `includeHeaderRow`      | `boolean` | Include headers in output             |
| `rowNumber`             | `string`  | Row number for single row operations  |
| `addSheet`              | `object`  | Config for CREATE_WORKSHEET action    |

## Trace Metadata

All methods accept an optional `metadata` parameter as the last argument for diagnostics labeling. See the [root SDK README](../../../README.md#trace-metadata) for details.

## Common Pitfalls

### Use extractFirstRowHeader for Objects

To get data as objects instead of arrays:

```typescript
// With extractFirstRowHeader: true
// Returns: [{ name: "John", email: "john@example.com" }, ...]

// With extractFirstRowHeader: false
// Returns: [["name", "email"], ["John", "john@example.com"], ...]
```

### Data Must Be JSON String

The `data` parameter expects a JSON string:

```typescript
// CORRECT
data: JSON.stringify([{ name: "John", email: "john@example.com" }]);

// WRONG
data: [{ name: "John", email: "john@example.com" }];
```

## Error Handling

```typescript
import {
  RestApiValidationError,
  IntegrationError,
} from "@superblocksteam/sdk-api";

try {
  const rows = await ctx.integrations.sheets.run(
    "READ_SPREADSHEET_RANGE",
    RowSchema,
    {
      spreadsheetId: "...",
      sheetTitle: "Sheet1",
      range: "A1:C100",
    },
  );
} catch (error) {
  if (error instanceof RestApiValidationError) {
    console.error("Validation failed:", error.details.zodError);
  } else if (error instanceof IntegrationError) {
    console.error("Google Sheets error:", error.message);
  }
}
```

## API Reference

- [Google Sheets API](https://developers.google.com/sheets/api)
- [A1 Notation](https://developers.google.com/sheets/api/guides/concepts#a1_notation)