Smartsheet Hierarchy Integration

The Smartsheet integration keeps your ClickTime Clients and Jobs in sync with the hierarchy you maintain in Smartsheet. It reads the parent/child row structure of your sheets and creates or updates the matching Clients and Jobs in ClickTime automatically. The sync is one‑directional (Smartsheet → ClickTime) and runs on a schedule, with an optional on‑demand sync for a single sheet. This guide covers how the integration behaves, how your Smartsheet data maps to ClickTime, how to configure it, and what to expect during day‑to‑day syncing.

How the Integration Works

The integration synchronizes the hierarchical rows from one or more Smartsheet workspaces into ClickTime Clients and Jobs. Syncing is one‑directional: changes flow from Smartsheet into ClickTime, never the other way. Two things drive a sync:

• A scheduled full sync — runs automatically on a set schedule (every 30 minutes by default) and processes all configured sheets.
• An optional on‑demand sync — lets you re‑sync a single sheet immediately without waiting for the schedule.

As part of setup, you decide:

• Which workspaces (and optionally which single sheet) to sync.
• How your row hierarchy levels map to Clients versus Jobs.
• Which Smartsheet columns, summary fields, or static values populate each ClickTime field.
• Which cell values should mark a record as active (when you map an active/inactive field).
• A default ClickTime Client for any Job that cannot resolve a parent Client from the sheet.

Default Behavior

With the standard configuration in place, the integration will:

• Run a full sync every 30 minutes.
• Sync both Clients and Jobs (each can be turned off independently by ClickTime during setup or testing).
• On its very first run, perform a SEED pass that links existing ClickTime records to their Smartsheet counterparts by Client Number / Job Number without overwriting any existing ClickTime data.
• Use the sheet you select during setup as the template for field mapping and active‑value choices.
• Process all sheets under the selected workspaces when Single Sheet? is off, or process only the one configured sheet when it is on.

How Smartsheet Data Maps to ClickTime

Rows on a Smartsheet sheet are read by their indent level — the parent/child structure you already use in the sheet. During setup you tell the integration which level represents a Client and which represents a Job, and where each ClickTime field gets its value.

Where field values can come from

Each mapped ClickTime field can draw from any of the following sources:

• A row cell — a value in a specific Smartsheet column on that row.
• A sheet summary field — a value stored at the sheet level rather than on a row.
• A hierarchy level — the top level of the row structure, or a specific indent level (level 1 through level 5).
• A static sheet or workspace value — the workspace name or ID, or the sheet name or ID, which do not require a column on every sheet.
• The default ClickTime Client — used to resolve a Job’s parent Client when no parent is mapped or found.

What gets set on a Client

What gets set on a Job

Field Mapping

Field mapping is configured on the Field Mapping page of the setup wizard, using the sheet you selected as the template. At a minimum you map the identifying fields; the rest are optional.

• Client name source — which hierarchy level or column supplies the Client name.
• Client unique identifier — the value used as the Client Number / External ID. This is the key ClickTime uses to match records on future syncs.
• Job name source — which hierarchy level or column supplies the Job name (level 1 by default).
• Job unique identifier — the value used as the Job Number / External ID.
• Job parent / Client link — optional; connects each Job to its parent Client.
• Optional field mappings — map additional Smartsheet sources to ClickTime standard or custom fields for both Clients and Jobs.

Active Status

If you map an active/inactive field, you then use Active Value Selections (on the Value Mapping page) to tell the integration which cell values mean “active.”

• Select one or more cell values that should mark a Client or Job as active. Any other value marks the record inactive.
• If you do not map an active/inactive field at all, every synced row is treated as active.
• If the mapped field uses a type whose values cannot be listed for selection, the setup screen shows an informational message and all synced rows are treated as active.

Setting Up the Integration

Setup is completed through a configuration wizard. The pages, in order, are:

1. Configuration — core connection and notification settings.
2. Workspace Selection — choose the Smartsheet workspaces to include.
3. Sheet Selection — choose the template sheet and set Single Sheet?.
4. Filter — optional sheet filter (see Things to Know).
5. Field Mapping — map hierarchy levels and fields.
6. Value Mapping — set the default Client, name formats, and active values.
7. Finalize — confirm the sync schedule and whether Client and Job sync are enabled.

Settings you control

Client and Job Name Formats

You can control how names appear in ClickTime using the name format settings:

• Client Name Format — Name (default) shows the mapped name only; ClientNumber--Name prefixes the Client Number.
• Job Name Format — Name (default) shows the mapped name only; JobNumber--Name prefixes the Job Number.

How Syncing and Change Detection Work

The integration runs in one of two modes:

During a scheduled sync, the integration compares each Smartsheet row to the matching ClickTime record and only updates a record when something has actually changed. A record is created or updated when:

• No matching ClickTime record exists yet, or
• The Name differs (after applying your name format), or
• A mapped standard field differs (Short Name, active status, Accounting Package ID, dates, and so on), or
• A mapped custom field differs.

If a row is inactive and the matching ClickTime record is already inactive, the integration skips it to avoid unnecessary updates.

Running an On-Demand Sync

When you need a single sheet reconciled right away rather than waiting for the next scheduled run, an on‑demand sync can be triggered for that sheet by its sheet ID. It fetches the sheet and runs a normal sync for just that sheet, bypassing the workspace‑wide scan.

Linking Existing ClickTime Records

If ClickTime already contains Clients and Jobs that should be tied to Smartsheet records, a one‑time reverse seed can backfill those links. It matches existing ClickTime records to Smartsheet using their Client Number and Job Number so the integration recognizes them as the same records going forward. This is typically run once during onboarding.

Sync Behaviors and Troubleshooting

Common situations and how the integration handles them:

Things to Know

• Syncing is scheduled, not real‑time. Changes made in Smartsheet appear in ClickTime on the next scheduled run (or immediately with an on‑demand sync).
• Sheets must share a consistent layout. When syncing all sheets in a workspace, every sheet is expected to use the same hierarchy structure and mappable columns as the template sheet you selected during setup.
• A default Client is needed for Jobs without a mapped parent. Set one on the Value Mapping page to avoid skipped Jobs.
• The Filter Sheets option is not currently applied. Although a sheet filter can be entered in the wizard, sheet selection is currently driven only by your workspace selection and the Single Sheet? setting. Use Single Sheet? to limit syncing to one sheet.