How to Integrate Retool with OmniFocus

Before building anything in Retool, select the integration pattern that best matches your use case and OmniFocus tier. There are three viable approaches, each with different trade-offs in terms of setup complexity, data freshness, and direction of data flow. Pattern 1 — Omni Automation Script Export (recommended for data visualization): Write a JavaScript automation script in OmniFocus (Scripts menu → Script Editor in OmniFocus 3 Pro) that reads all projects and tasks from the OmniFocus database, formats them as JSON, and saves the JSON file to a known location (e.g., iCloud Drive or Dropbox). Create a recurring macOS Shortcuts shortcut that runs this script on a schedule (daily or weekly) using the OmniFocus Automation action. Separately, a simple Python or Node.js script can read this JSON file and POST it to a Retool Workflow endpoint — creating a periodic data refresh pipeline. Pattern 2 — CSV/TaskPaper Export Import (simplest, periodic): Export directly from OmniFocus via File → Export → CSV or TaskPaper format. Import the CSV into Retool Database using the built-in CSV import feature. This is entirely manual but requires no scripting knowledge. Pattern 3 — URL Scheme Bridge (for creating tasks in OmniFocus from Retool): Construct omnifocus:///add URLs in Retool using JavaScript to encode task parameters. When clicked, these URLs open OmniFocus on the local device and pre-populate a new task. This is a one-way push — Retool to OmniFocus — with no data read back. For most team dashboard use cases, Pattern 1 (Omni Automation export + Retool Database) provides the best balance of automation and data richness.

1// Omni Automation script to export OmniFocus tasks as JSON
2// Run this in OmniFocus Script Editor (Automation menu → Script Editor)
3// Requires OmniFocus 3 Pro
4
5(async () => {
6  const tasks = flattenedTasks.filter(t => !t.completed && !t.dropped);
7  const exportData = tasks.map(task => ({
8    id: task.id.primaryKey,
9    name: task.name,
10    project: task.containingProject?.name || '(No Project)',
11    due_date: task.dueDate ? task.dueDate.toISOString() : null,
12    defer_date: task.deferDate ? task.deferDate.toISOString() : null,
13    flagged: task.flagged,
14    tags: task.tags.map(t => t.name),
15    note: task.note,
16    completed: task.completed,
17    completion_date: task.completionDate ? task.completionDate.toISOString() : null
18  }));
19  return JSON.stringify(exportData);
20})();

Create a database structure in Retool Database (or connected PostgreSQL) to store OmniFocus task data. Navigate to the Retool Database tab and click Create Table. Name it 'omnifocus_tasks'. Define these columns: id (text, primary key — OmniFocus task primary keys are alphanumeric strings), name (text not null), project (text), due_date (timestamp), defer_date (timestamp), flagged (boolean default false), tags (text — store as comma-separated string or use a JSON column if PostgreSQL), note (text), completed (boolean default false), completion_date (timestamp), imported_at (timestamp default now()). Click Save. For the CSV import path: export from OmniFocus (File → Export → CSV), ensure the CSV columns match your table, and use Retool Database's Import CSV feature to load the data. For the Omni Automation script path: create a Retool Workflow with a Webhook trigger (Workflows → Create new Workflow → select Webhook trigger). The Workflow receives the JSON payload from the Omni Automation script (posted via a local script or Shortcuts shortcut), iterates over the tasks array, and upserts each task into the omnifocus_tasks table using INSERT ... ON CONFLICT (id) DO UPDATE SET. Note the Workflow's webhook URL — you will configure the local export script to POST to this URL. For ongoing refreshes, the Omni Automation script should be scheduled to run daily via macOS Shortcuts' Automation feature (Shortcuts → Automation → New Automation → Time of Day).

1-- Create omnifocus_tasks table (run in Retool's SQL editor)
2CREATE TABLE IF NOT EXISTS omnifocus_tasks (
3  id TEXT PRIMARY KEY,
4  name TEXT NOT NULL,
5  project TEXT,
6  folder TEXT,
7  due_date TIMESTAMP,
8  defer_date TIMESTAMP,
9  flagged BOOLEAN DEFAULT FALSE,
10  tags TEXT,
11  note TEXT,
12  completed BOOLEAN DEFAULT FALSE,
13  completion_date TIMESTAMP,
14  imported_at TIMESTAMP DEFAULT NOW()
15);
16
17-- Upsert query for Workflow import
18INSERT INTO omnifocus_tasks
19  (id, name, project, due_date, defer_date, flagged, tags, note, completed, completion_date, imported_at)
20VALUES
21  ({{ task.id }}, {{ task.name }}, {{ task.project }},
22   {{ task.due_date }}, {{ task.defer_date }}, {{ task.flagged }},
23   {{ task.tags.join(',') }}, {{ task.note }}, {{ task.completed }},
24   {{ task.completion_date }}, NOW())
25ON CONFLICT (id) DO UPDATE SET
26  name = EXCLUDED.name, project = EXCLUDED.project,
27  due_date = EXCLUDED.due_date, flagged = EXCLUDED.flagged,
28  completed = EXCLUDED.completed, completion_date = EXCLUDED.completion_date,
29  imported_at = NOW();

With OmniFocus data in the database, create the queries that power the dashboard. Create 'getOverdueTasks': SELECT id, name, project, due_date, flagged, tags FROM omnifocus_tasks WHERE completed = false AND due_date < NOW() ORDER BY due_date ASC. Create 'getDueThisWeek': SELECT id, name, project, due_date, flagged FROM omnifocus_tasks WHERE completed = false AND due_date BETWEEN NOW() AND NOW() + INTERVAL '7 days' ORDER BY due_date. Create 'getProjectSummary': SELECT project, COUNT(*) FILTER (WHERE NOT completed) AS active_tasks, COUNT(*) FILTER (WHERE NOT completed AND due_date < NOW()) AS overdue_tasks, COUNT(*) FILTER (WHERE completed AND completion_date > NOW() - INTERVAL '7 days') AS completed_this_week, MAX(completion_date) AS last_completion FROM omnifocus_tasks GROUP BY project ORDER BY overdue_tasks DESC, active_tasks DESC. Create 'getDailyCompletions': SELECT DATE(completion_date) AS completion_day, COUNT(*) AS completed FROM omnifocus_tasks WHERE completed = true AND completion_date > NOW() - INTERVAL '30 days' GROUP BY DATE(completion_date) ORDER BY completion_day. Add a JavaScript transformer to getProjectSummary that flags projects as 'Stalled' when last_completion is older than 14 days and active_tasks > 0.

1-- Project health summary with stall detection
2SELECT
3  project,
4  COUNT(*) FILTER (WHERE NOT completed) AS active_tasks,
5  COUNT(*) FILTER (WHERE NOT completed AND due_date < NOW()) AS overdue_tasks,
6  COUNT(*) FILTER (WHERE NOT completed AND flagged) AS flagged_tasks,
7  COUNT(*) FILTER (
8    WHERE completed AND completion_date > NOW() - INTERVAL '7 days'
9  ) AS completed_this_week,
10  MAX(completion_date) AS last_completion,
11  CASE
12    WHEN MAX(completion_date) < NOW() - INTERVAL '14 days'
13      AND COUNT(*) FILTER (WHERE NOT completed) > 0
14    THEN true ELSE false
15  END AS is_stalled
16FROM omnifocus_tasks
17WHERE project IS NOT NULL
18GROUP BY project
19ORDER BY overdue_tasks DESC, active_tasks DESC;

Assemble the review dashboard in Retool. At the top of the canvas, add four Stat components: 'Overdue Tasks' ({{ getOverdueTasks.data.length }}, styled red when > 0), 'Due This Week' ({{ getDueThisWeek.data.length }}, styled orange), 'Stalled Projects' ({{ getProjectSummary.data.filter(p => p.is_stalled).length }}, styled yellow), and 'Last Import' ({{ new Date(lastImportTime).toLocaleString() }} where lastImportTime is a MAX query on imported_at). Add a Tab panel below the stats with three tabs: 'Projects', 'Due Soon', and 'Overdue'. In the Projects tab, add a Table bound to getProjectSummary.data showing: project name, active_tasks, overdue_tasks (red badge when > 0), flagged_tasks, completed_this_week, last_completion (formatted as relative time: '3 days ago'), and is_stalled (Tag: 'Stalled' in orange vs 'Active' in green). Add a Chart in the Projects tab showing project sizes (active task count) as a horizontal Bar Chart. In the Due Soon tab, add a Table of getDueThisWeek.data with columns: task name, project, due_date (formatted as day and time), and a flagged indicator. In the Overdue tab, add a similar Table of getOverdueTasks.data with an age column (days since due_date using DATE_PART('day', NOW() - due_date)). At the bottom of the dashboard, add the 30-day Completion Trend as a Line Chart using getDailyCompletions.data.

For the task creation direction (Retool → OmniFocus), implement the URL scheme bridge. In Retool, add a new section or separate Tab called 'Create OmniFocus Task'. Add a Form with: Task Name (Text Input named 'taskNameInput', required), Project (Text Input named 'taskProjectInput'), Due Date (Date Picker named 'taskDuePicker'), Tags (Text Input named 'taskTagsInput', comma-separated), and Notes (Text Area named 'taskNotesInput'). Add a JavaScript query named 'buildOmniFocusUrl' that runs when the form is submitted and constructs the URL scheme URL. The OmniFocus URL scheme supports: omnifocus:///add?name={title}&note={note}&project={project}&tags={tags}&due={dueDate}&autosave=true. All values must be URL-encoded. The &autosave=true parameter creates the task without showing the Quick Entry panel. Display the generated URL in a Text component below the form with the label 'Click to open in OmniFocus (requires OmniFocus installed on this device)' — the URL should be an HTML Link component that opens in a new tab. Add a 'Copy Link' button that copies the URL to clipboard using Retool's copyToClipboard() utility. Log each generated URL in a task_link_log database table with the task name, project, due date, generated URL, and created_by user email.

1// JavaScript query: build OmniFocus URL scheme URL
2const name = encodeURIComponent(taskNameInput.value || '');
3const note = encodeURIComponent(taskNotesInput.value || '');
4const project = encodeURIComponent(taskProjectInput.value || '');
5const tags = encodeURIComponent(taskTagsInput.value || '');
6
7let dueParam = '';
8if (taskDuePicker.value) {
9  // OmniFocus accepts ISO 8601 date format
10  const dueDate = new Date(taskDuePicker.value);
11  dueParam = `&due=${encodeURIComponent(dueDate.toISOString())}`;
12}
13
14const url = `omnifocus:///add?name=${name}&note=${note}&project=${project}&tags=${tags}${dueParam}&autosave=true`;
15
16return { url, display: `omnifocus:///add?name=${taskNameInput.value}...` };