GAS HubDB Data Transmission

Implementation Overview

Use Google Apps Script (GAS) to send spreadsheet data to the HubDB API.

Synchronization executes in 4 steps:

1. Unpublish the table
2. Delete existing rows
3. Create new rows from spreadsheet
4. Republish the table

Prerequisites

Create HubSpot Private App

1. HubSpot Dashboard → Settings → Integrations → Private Apps
2. Click "Create a Private App"
3. Configure required scopes:
   • cms.hubdb.tables.read
   • cms.hubdb.tables.write
   • cms.hubdb.rows.read
   • cms.hubdb.rows.write
4. Copy the access token

Store Token in GAS

For security, store the token in Script Properties. Never hardcode it.

// Token storage function (run once)
function setHubSpotToken() {
  const token = 'YOUR_ACCESS_TOKEN'; // Replace with actual token
  PropertiesService.getScriptProperties().setProperty('HUBSPOT_TOKEN', token);

  // Verify storage
  const saved = PropertiesService.getScriptProperties().getProperty('HUBSPOT_TOKEN');
  Logger.log(saved ? 'Storage successful' : 'Storage failed');
}

Sync Code Implementation

1. Unpublish Function

function unpublishHubDBTable(tableId) {
  const token = PropertiesService.getScriptProperties().getProperty('HUBSPOT_TOKEN');
  if (!token) throw new Error('Token not configured');

  const url = `https://api.hubapi.com/cms/v3/hubdb/tables/${tableId}/unpublish`;

  const res = UrlFetchApp.fetch(url, {
    method: 'POST',
    muteHttpExceptions: true,
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    }
  });

  const code = res.getResponseCode();
  if (code === 204 || code === 200) {
    Logger.log(`Table ${tableId} unpublished successfully`);
  } else {
    Logger.log(`Unpublish failed: ${res.getContentText()}`);
  }
}

2. Delete All Rows Function

The HubDB API requires pagination handling. Fetch and delete 100 rows at a time.

function deleteAllHubDBRows(tableId) {
  const token = PropertiesService.getScriptProperties().getProperty('HUBSPOT_TOKEN');
  if (!token) throw new Error('Token not configured');

  let offset = 0;
  let hasMore = true;

  while (hasMore) {
    // Fetch draft rows
    const url = `https://api.hubapi.com/cms/v3/hubdb/tables/${tableId}/rows?limit=100&offset=${offset}`;
    const res = UrlFetchApp.fetch(url, {
      method: 'GET',
      headers: { 'Authorization': `Bearer ${token}` }
    });
    const json = JSON.parse(res.getContentText());

    // Delete each row
    for (const row of json.results) {
      const deleteUrl = `https://api.hubapi.com/cms/v3/hubdb/tables/${tableId}/rows/${row.id}/draft`;
      UrlFetchApp.fetch(deleteUrl, {
        method: 'DELETE',
        muteHttpExceptions: true,
        headers: { 'Authorization': `Bearer ${token}` }
      });
      Logger.log(`Deleted: rowId=${row.id}`);
    }

    // Check for next page
    hasMore = json.paging && json.paging.next;
    if (hasMore) {
      const nextLink = json.paging.next.link;
      const match = nextLink.match(/offset=(\d+)/);
      offset = match ? parseInt(match[1], 10) : 0;
    }
  }
}

3. Create Rows from Spreadsheet

function createHubDBRowsFromSheet(sheetName, tableId) {
  const token = PropertiesService.getScriptProperties().getProperty('HUBSPOT_TOKEN');
  if (!token) throw new Error('Token not configured');

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) throw new Error(`Sheet '${sheetName}' not found`);

  const data = sheet.getDataRange().getValues();
  const headers = data.shift(); // First row as headers
  const url = `https://api.hubapi.com/cms/v3/hubdb/tables/${tableId}/rows`;

  // Get HubDB column names
  const validKeys = getHubDBColumnNames(tableId, token);

  let count = 0;
  for (const row of data) {
    const values = {};
    let name = '';
    let path = '';

    // Map data according to headers
    headers.forEach((key, i) => {
      if (typeof key === 'string' && key.trim() !== '' && row[i] !== '') {
        let value = row[i];
        // Convert numbers to strings
        if (typeof value === 'number') {
          value = value.toString();
        }
        // Handle special fields
        if (key === 'hs_name') name = value;
        else if (key === 'hs_path') path = value;
        // Only add valid columns
        if (validKeys.includes(key)) {
          values[key] = value;
        }
      }
    });

    // Skip empty data
    if (Object.keys(values).length === 0 && !name && !path) continue;

    const payload = { values };
    if (name) payload.name = name;
    if (path) payload.path = path;

    // API request
    const res = UrlFetchApp.fetch(url, {
      method: 'POST',
      muteHttpExceptions: true,
      headers: {
        'Authorization': `Bearer ${token}`,
        'Content-Type': 'application/json'
      },
      payload: JSON.stringify(payload)
    });

    if (res.getResponseCode() === 201) {
      count++;
    } else {
      Logger.log(`Creation failed: ${res.getContentText()}`);
    }
  }

  Logger.log(`${count} rows created in HubDB`);
}

// Get HubDB table column names
function getHubDBColumnNames(tableId, token) {
  const url = `https://api.hubapi.com/cms/v3/hubdb/tables/${tableId}`;
  const res = UrlFetchApp.fetch(url, {
    method: 'GET',
    headers: { 'Authorization': `Bearer ${token}` }
  });
  const json = JSON.parse(res.getContentText());
  return json.columns.map(col => col.name);
}

4. Publish Function

function publishHubDBTable(tableId) {
  const token = PropertiesService.getScriptProperties().getProperty('HUBSPOT_TOKEN');
  if (!token) throw new Error('Token not configured');

  const url = `https://api.hubapi.com/cms/v3/hubdb/tables/${tableId}/draft/publish`;
  const res = UrlFetchApp.fetch(url, {
    method: 'POST',
    muteHttpExceptions: true,
    headers: {
      'Authorization': `Bearer ${token}`,
      'Content-Type': 'application/json'
    }
  });

  const code = res.getResponseCode();
  if (code === 204 || code === 200) {
    Logger.log(`Table ${tableId} published successfully`);
  } else {
    Logger.log(`Publish failed: ${res.getContentText()}`);
  }
}

Main Sync Function

Combine all 4 steps into a main function.

function syncHubDBTable(sheetName, tableId) {
  Logger.log(`Starting sync for [${sheetName}]`);

  try {
    Logger.log('Step 1: Unpublish');
    unpublishHubDBTable(tableId);

    Logger.log('Step 2: Delete existing rows');
    deleteAllHubDBRows(tableId);

    Logger.log('Step 3: Create new rows');
    createHubDBRowsFromSheet(sheetName, tableId);

    Logger.log('Step 4: Publish');
    publishHubDBTable(tableId);

    Logger.log(`Complete: '${sheetName}' → Table ID ${tableId}`);
  } catch (e) {
    Logger.log(`Error: ${e.message}`);
  }
}

Multi-Table Sync

Example for syncing multiple sheets to different HubDB tables:

function syncAllTables() {
  const mappings = [
    { sheet: 'Products', tableId: '12345678' },
    { sheet: 'Stores', tableId: '23456789' },
    { sheet: 'Rankings', tableId: '34567890' },
  ];

  for (const { sheet, tableId } of mappings) {
    syncHubDBTable(sheet, tableId);
    Logger.log('----------------------------------------');
  }
}

Scheduled Execution

Use GAS triggers to run sync automatically.

Recommended Frequencies

Error Handling

For production use, add error notifications:

function syncWithNotification() {
  try {
    syncAllTables();
  } catch (e) {
    // Notify via Slack or email
    MailApp.sendEmail(
      'admin@example.com',
      'HubDB Sync Error',
      `An error occurred: ${e.message}`
    );
  }
}

Spreadsheet Structure Example

Example spreadsheet structure for HubDB integration:

• hs_name: HubDB row name (required)
• hs_path: URL path (for dynamic pages)
• Other columns match your HubDB table definition

Summary

Key points for GAS HubDB sync:

• Security: Store tokens in Script Properties
• Sync flow: Unpublish → Delete → Create → Publish
• Pagination: Process large datasets 100 rows at a time
• Scheduling: Set up triggers for automatic sync
• Error handling: Add notifications for stable operations

With this setup, spreadsheet updates automatically reflect on your HubSpot site.