How to Integrate Replit with Smartsheet

1import os
2import requests
3from typing import Optional, Any
4
5TOKEN = os.environ["SMARTSHEET_TOKEN"]
6SHEET_ID = os.environ["SMARTSHEET_SHEET_ID"]
7BASE_URL = "https://api.smartsheet.com/2.0"
8
9HEADERS = {
10    "Authorization": f"Bearer {TOKEN}",
11    "Content-Type": "application/json",
12    "Accept": "application/json"
13}
14
15def get_sheet(sheet_id: str = None) -> dict:
16    """Fetch sheet data including column definitions and all rows."""
17    sid = sheet_id or SHEET_ID
18    response = requests.get(f"{BASE_URL}/sheets/{sid}", headers=HEADERS)
19    response.raise_for_status()
20    return response.json()
21
22def get_column_map(sheet_data: dict) -> dict:
23    """
24    Build a map of column name -> column ID from sheet data.
25    Use this to look up column IDs by human-readable names.
26    """
27    return {col['title']: col['id'] for col in sheet_data.get('columns', [])}
28
29def add_row(column_map: dict, data: dict, sheet_id: str = None, to_top: bool = True) -> dict:
30    """
31    Add a new row to the sheet.
32    data: {column_name: value, ...} using column titles as keys
33    Returns the new row data.
34    """
35    sid = sheet_id or SHEET_ID
36    cells = [
37        {"columnId": column_map[col_name], "value": value}
38        for col_name, value in data.items()
39        if col_name in column_map
40    ]
41    payload = {"toTop": to_top, "cells": cells}
42    response = requests.post(
43        f"{BASE_URL}/sheets/{sid}/rows",
44        json=[payload],
45        headers=HEADERS
46    )
47    response.raise_for_status()
48    return response.json()
49
50def update_row(row_id: int, column_map: dict, updates: dict, sheet_id: str = None) -> dict:
51    """
52    Update specific cells in an existing row.
53    updates: {column_name: new_value, ...}
54    """
55    sid = sheet_id or SHEET_ID
56    cells = [
57        {"columnId": column_map[col_name], "value": value}
58        for col_name, value in updates.items()
59        if col_name in column_map
60    ]
61    payload = {"id": row_id, "cells": cells}
62    response = requests.put(
63        f"{BASE_URL}/sheets/{sid}/rows",
64        json=[payload],
65        headers=HEADERS
66    )
67    response.raise_for_status()
68    return response.json()
69
70def find_row_by_column_value(
71    rows: list,
72    column_map: dict,
73    search_column: str,
74    search_value: Any
75) -> Optional[dict]:
76    """Find the first row where a column matches a given value."""
77    col_id = column_map.get(search_column)
78    if not col_id:
79        return None
80    for row in rows:
81        for cell in row.get('cells', []):
82            if cell.get('columnId') == col_id and cell.get('value') == search_value:
83                return row
84    return None
85
86def delete_rows(row_ids: list, sheet_id: str = None) -> dict:
87    """Delete one or more rows from the sheet."""
88    sid = sheet_id or SHEET_ID
89    ids_param = ",".join(str(r) for r in row_ids)
90    response = requests.delete(
91        f"{BASE_URL}/sheets/{sid}/rows?ids={ids_param}",
92        headers=HEADERS
93    )
94    response.raise_for_status()
95    return response.json()
96
97# Example usage
98if __name__ == "__main__":
99    # Fetch sheet structure
100    sheet = get_sheet()
101    col_map = get_column_map(sheet)
102    print(f"Sheet: {sheet['name']}")
103    print(f"Columns: {list(col_map.keys())}")
104    print(f"Total rows: {len(sheet.get('rows', []))}")
105
106    # Add a new row
107    result = add_row(col_map, {
108        "Task Name": "Update Q2 roadmap",
109        "Status": "In Progress",
110        "Assigned To": "Jane Doe",
111        "Due Date": "2026-04-30"
112    })
113    print(f"Row added: {result}")
114
115    # Find and update a row
116    rows = sheet.get('rows', [])
117    target_row = find_row_by_column_value(rows, col_map, "Task Name", "Update Q2 roadmap")
118    if target_row:
119        update_row(target_row['id'], col_map, {"Status": "Complete"})
120        print("Row updated to Complete")

1const express = require('express');
2const axios = require('axios');
3
4const app = express();
5app.use(express.json());
6
7const TOKEN = process.env.SMARTSHEET_TOKEN;
8const SHEET_ID = process.env.SMARTSHEET_SHEET_ID;
9const BASE_URL = 'https://api.smartsheet.com/2.0';
10
11const ssHeaders = {
12  'Authorization': `Bearer ${TOKEN}`,
13  'Content-Type': 'application/json',
14  'Accept': 'application/json'
15};
16
17// Register a webhook with Smartsheet (run once)
18async function registerWebhook(callbackUrl, webhookName) {
19  const response = await axios.post(`${BASE_URL}/webhooks`, {
20    name: webhookName,
21    callbackUrl,
22    scope: 'sheet',
23    scopeObjectId: parseInt(SHEET_ID),
24    events: ['*.*'],
25    version: 1
26  }, { headers: ssHeaders });
27  return response.data;
28}
29
30// Enable a webhook after registration
31async function enableWebhook(webhookId) {
32  const response = await axios.put(`${BASE_URL}/webhooks/${webhookId}`, {
33    enabled: true
34  }, { headers: ssHeaders });
35  return response.data;
36}
37
38// GET /register-webhook?url=https://... — register and enable a new webhook
39app.get('/register-webhook', async (req, res) => {
40  const { url } = req.query;
41  if (!url) return res.status(400).json({ error: 'url query parameter required' });
42  try {
43    const webhook = await registerWebhook(url, 'Replit Integration Webhook');
44    await enableWebhook(webhook.result.id);
45    res.json({ success: true, webhookId: webhook.result.id });
46  } catch (error) {
47    res.status(500).json({ error: error.response?.data || error.message });
48  }
49});
50
51// POST /webhook — receive Smartsheet events
52app.post('/webhook', (req, res) => {
53  // Smartsheet sends a verification challenge on registration
54  const challenge = req.headers['smartsheet-hook-challenge'];
55  if (challenge) {
56    // Must respond with the challenge header to verify ownership
57    res.set('Smartsheet-Hook-Response', challenge);
58    return res.status(200).send();
59  }
60
61  // Process actual webhook events
62  const { events, scopeObjectId } = req.body;
63  console.log(`Webhook received for sheet ${scopeObjectId}: ${events?.length} events`);
64
65  if (events) {
66    events.forEach(event => {
67      const { objectType, eventType, id, columnId, rowId } = event;
68      console.log(`Event: ${objectType} ${eventType} | Row: ${rowId} | Column: ${columnId}`);
69      // Add your business logic here:
70      // - Sync to CRM when Status column changes
71      // - Send Slack notification when Priority is set to High
72      // - Trigger approval workflow when row is added
73    });
74  }
75
76  res.json({ received: true });
77});
78
79// GET /sheet — read current sheet data
80app.get('/sheet', async (req, res) => {
81  try {
82    const response = await axios.get(`${BASE_URL}/sheets/${SHEET_ID}`, {
83      headers: ssHeaders
84    });
85    const sheet = response.data;
86    const columnMap = {};
87    sheet.columns.forEach(col => { columnMap[col.id] = col.title; });
88
89    const rows = sheet.rows.map(row => {
90      const rowData = { id: row.id };
91      row.cells.forEach(cell => {
92        const colName = columnMap[cell.columnId];
93        if (colName) rowData[colName] = cell.value || cell.displayValue || null;
94      });
95      return rowData;
96    });
97
98    res.json({ sheetName: sheet.name, rowCount: rows.length, rows });
99  } catch (error) {
100    res.status(500).json({ error: error.response?.data || error.message });
101  }
102});
103
104app.listen(3000, '0.0.0.0', () => {
105  console.log('Smartsheet integration server running on port 3000');
106});