Google Sheets API

Connect Google Sheets as live data sources for document generation and email campaigns. OAuth2 integration with automatic token refresh.

---

Base URL

https://app.datapublisher.io/api/google

---

Authentication

All endpoints require JWT authentication via Authorization: Bearer header.

---

Overview

The Google Sheets API allows you to:

Connect Google Sheets via OAuth2

Read spreadsheet data in real-time

List available spreadsheets from Google Drive

Extract sheet data with headers and rows

Auto-refresh expired tokens

Import data for document generation

Supported Google APIs:

Google Sheets API v4

Google Drive API v3

Required Scopes:

https://www.googleapis.com/auth/spreadsheets.readonly - Read sheets

https://www.googleapis.com/auth/drive.readonly - List files

---

OAuth2 Connection Flow

Step 1: Start Authorization

Initiate Google OAuth2 flow to connect Google Sheets.

Endpoint: GET /auth/start

Headers:

Authorization: Bearer

Response (200 OK):

{
  "success": true,

  "authUrl": "https://accounts.google.com/o/oauth2/v2/auth?client_id=...&redirect_uri=...&response_type=code&scope=...&state=xyz789",

  "message": "Redirect user to this URL to authorize Google Sheets access"

}

Usage:

Get authUrl from this endpoint

Redirect user to authUrl (open in browser dialog)

User signs into Google

User grants spreadsheet access

Google redirects to callback URL

---

Step 2: OAuth2 Callback (Automatic)

Google automatically redirects here after authorization. DataPublisher handles this internally.

Endpoint: GET /auth/callback

Process:

Receives authorization code from Google

Validates state parameter (CSRF protection)

Exchanges code for access + refresh tokens

Retrieves user's Google account email

Encrypts and stores tokens in database

Shows success page that auto-closes

Stored Data:

data_sources table:
UserId: User UUID

Name: Google account email

Type: 'google_sheets'

IsActive: true


api_credentials table:

UserId: User UUID

DataSourceId: Reference to data source

CredentialType: 'google_oauth2'

EncryptedCredentials: AES-256 encrypted tokens

TokenExpiry: Token expiration timestamp

---

Spreadsheet Operations

List Spreadsheets

Get all Google Sheets files accessible to the connected account.

Endpoint: GET /spreadsheets

Headers:

Authorization: Bearer

Response (200 OK):

{ "success": true, "spreadsheets": [ { "id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "name": "Customer Database", "url": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "createdTime": "2026-01-15T10:00:00Z", "modifiedTime": "2026-02-12T09:30:00Z", "owner": "user@gmail.com" }, { "id": "1Ab2Cd3Ef4Gh5Ij6Kl7Mn8Op9Qr0St", "name": "Sales Report 2026", "url": "https://docs.google.com/spreadsheets/d/1Ab2Cd3Ef4Gh5Ij6Kl7Mn8Op9Qr0St", "createdTime": "2026-02-01T08:00:00Z", "modifiedTime": "2026-02-12T10:15:00Z", "owner": "team@company.com" } ], "count": 2

}

Query Parameters:

search (optional): Filter by file name

---

Get Spreadsheet Metadata

Retrieve detailed information about a specific spreadsheet.

Endpoint: GET /spreadsheets/:spreadsheetId

Headers:

Authorization: Bearer

Path Parameters:

spreadsheetId: Google Sheets ID (from URL)

Response (200 OK):

{ "success": true, "spreadsheet": { "id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "name": "Customer Database", "url": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "sheets": [ { "sheetId": 0, "title": "Customers", "index": 0, "rowCount": 1500, "columnCount": 12 }, { "sheetId": 1, "title": "Orders", "index": 1, "rowCount": 3200, "columnCount": 8 } ], "locale": "en_US", "timeZone": "America/New_York" }

}

---

Get Sheet Data

Retrieve data from a specific sheet within a spreadsheet.

Endpoint: GET /spreadsheets/:spreadsheetId/sheets/:sheetName/data

Headers:

Authorization: Bearer

Path Parameters:

spreadsheetId: Google Sheets ID

sheetName: Sheet tab name (e.g., "Customers", "Sheet1")

Query Parameters:

range (optional): A1 notation range (e.g., "A1:E100")

limit (optional): Max rows to return (default: 1000)

offset (optional): Skip rows (default: 0)

Response (200 OK):

{ "success": true, "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms", "sheetName": "Customers", "range": "A1:L1500", "headers": [ "CustomerID", "CompanyName", "ContactName", "Email", "Phone", "Address", "City", "State", "ZipCode", "Country", "TotalSales", "SignupDate" ], "data": [ { "CustomerID": "1001", "CompanyName": "Acme Corp", "ContactName": "John Doe", "Email": "john@acme.com", "Phone": "555-0100", "Address": "123 Main St", "City": "New York", "State": "NY", "ZipCode": "10001", "Country": "USA", "TotalSales": "45000", "SignupDate": "2025-06-15" } ], "rowCount": 1500, "columnCount": 12

}

---

Import Sheet to CSV

Import Google Sheet data as a CSV file in DataPublisher.

Endpoint: POST /spreadsheets/:spreadsheetId/sheets/:sheetName/import

Headers:

Authorization: Bearer

Content-Type: application/json

Path Parameters:

spreadsheetId: Google Sheets ID

sheetName: Sheet tab name

Request Body:

{
  "fileName": "customer-data.csv",

  "description": "Imported from Google Sheets",

  "tags": ["customers", "google-sheets"],

  "range": "A1:L1500"

}

Response (200 OK):

{ "success": true, "fileId": "csv-file-uuid", "fileName": "customer-data.csv", "rowCount": 1499, "columnCount": 12, "message": "Google Sheet imported successfully"

}

Notes:

First row is treated as headers

Empty rows are skipped

Data converted to CSV format

Stored as regular CSV file in DataPublisher

Can be used in email campaigns and document generation

---

Connection Management

Get Connection Status

Check if Google Sheets is connected and token status.

Endpoint: GET /connection/status

Headers:

Authorization: Bearer

Response (200 OK):

{ "success": true, "connected": true, "email": "user@gmail.com", "tokenExpiresAt": "2026-02-13T10:00:00Z", "daysUntilExpiry": 30, "connectedAt": "2026-01-15T09:00:00Z"

}

OR if not connected:

{ "success": true, "connected": false, "message": "Google Sheets not connected"

}

---

Disconnect Google Sheets

Remove Google Sheets connection and delete stored tokens.

Endpoint: DELETE /connection

Headers:

Authorization: Bearer

Response (200 OK):

{
  "success": true,

  "message": "Google Sheets disconnected successfully"

}

Notes:

Removes access and refresh tokens from database

Does NOT revoke tokens on Google's side

User can revoke app access from Google Account settings

---

Extracting Spreadsheet ID from URL

Google Sheets URL format:

https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit?gid=SHEET_ID

Example:

URL: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit

Spreadsheet ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms

---

Token Management

Automatic Token Refresh

Access tokens expire after 1 hour. DataPublisher automatically refreshes them using the refresh token.

Process:

Before API call, check if token expired

If expired, call Google OAuth token endpoint with refresh_token

Get new access_token

Update database with new token

Proceed with original API call

No manual intervention needed!

---

Rate Limiting

Google API Requests: 100 requests per 100 seconds (Google's limit)

DataPublisher API: 1000 requests per 15 minutes

---

Common Error Codes

| Status Code | Description |

|-------------|-------------|

| 400 | Invalid spreadsheet ID or sheet name |

| 401 | Unauthorized - not connected or token expired |

| 403 | Google API access denied (insufficient permissions) |

| 404 | Spreadsheet or sheet not found |

| 429 | Rate limit exceeded (Google API) |

| 500 | Google API error or internal server error |

---

Usage Examples

JavaScript (Office Add-in)

// Connect Google Sheets
async function connectGoogleSheets() {

  const response = await fetch('https://app.datapublisher.io/api/google/auth/start', {

    headers: {

      'Authorization': Bearer ${jwtToken}

    }

  });


  const { authUrl } = await response.json();


  // Open OAuth dialog

  window.open(authUrl, '_blank', 'width=600,height=700');

}


// List spreadsheets

async function listSpreadsheets() {

  const response = await fetch('https://app.datapublisher.io/api/google/spreadsheets', {

    headers: {

      'Authorization': Bearer ${jwtToken}

    }

  });


  const { spreadsheets } = await response.json();

  console.log('Spreadsheets:', spreadsheets);

}


// Get sheet data

async function getSheetData(spreadsheetId, sheetName) {

  const response = await fetch(

    https://app.datapublisher.io/api/google/spreadsheets/${spreadsheetId}/sheets/${encodeURIComponent(sheetName)}/data,
    {

      headers: {

        'Authorization': Bearer ${jwtToken}

      }

    }

  );


  const data = await response.json();

  console.log('Sheet data:', data);

  return data;

}


// Import sheet as CSV

async function importSheet(spreadsheetId, sheetName) {

  const response = await fetch(

    https://app.datapublisher.io/api/google/spreadsheets/${spreadsheetId}/sheets/${encodeURIComponent(sheetName)}/import,
    {

      method: 'POST',

      headers: {

        'Authorization': Bearer ${jwtToken},

        'Content-Type': 'application/json'

      },

      body: JSON.stringify({

        fileName: ${sheetName}.csv,

        description: 'Imported from Google Sheets',

        tags: ['google-sheets']

      })

    }

  );


  const result = await response.json();

  console.log('Import result:', result);

  return result.fileId;

}

Python (requests)

import requests

API_URL = 'https://app.datapublisher.io/api/google'

headers = {'Authorization': f'Bearer {jwt_token}'}


Get spreadsheets

response = requests.get(f'{API_URL}/spreadsheets', headers=headers)

spreadsheets = response.json()['spreadsheets']


Get sheet data

spreadsheet_id = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'

sheet_name = 'Customers'

response = requests.get(

    f'{API_URL}/spreadsheets/{spreadsheet_id}/sheets/{sheet_name}/data',

    headers=headers

)

data = response.json()

print(f"Retrieved {data['rowCount']} rows")


Import as CSV

response = requests.post(

    f'{API_URL}/spreadsheets/{spreadsheet_id}/sheets/{sheet_name}/import',

    json={

        'fileName': 'customers.csv',

        'description': 'Customer data from Google Sheets'

    },

    headers=headers

)

file_id = response.json()['fileId']

print(f"Imported as file ID: {file_id}")

---

Best Practices

Token Refresh: Tokens auto-refresh - no manual action needed

Rate Limits: Cache frequently accessed data to avoid API limits

Sheet Names: URL-encode sheet names with spaces

Large Sheets: Use limit and offset for pagination

Data Types: All data returned as strings - convert as needed

Error Handling: Handle 401 errors by prompting reconnection

Permissions: Ensure user has read access to requested sheets

---

Google Sheets API

Base URL

Authentication

Overview

OAuth2 Connection Flow

Step 1: Start Authorization

Step 2: OAuth2 Callback (Automatic)

Spreadsheet Operations

List Spreadsheets

Get Spreadsheet Metadata

Get Sheet Data

Import Sheet to CSV

Connection Management

Get Connection Status

Disconnect Google Sheets

Extracting Spreadsheet ID from URL

Token Management

Automatic Token Refresh

Rate Limiting

Common Error Codes

Usage Examples

JavaScript (Office Add-in)

Python (requests)

Get spreadsheets

Get sheet data

Import as CSV

Best Practices

Related Documentation