Using API Endpoints in Excel for Mac

This guide explains how to connect Excel for Mac to pulse API endpoints to import live data directly into your spreadsheets.

Important Note for Excel for Mac Users

Excel for Mac has limited web query and authentication support. The recommended approach is to use CSV export which is simple and reliable.

Prerequisites

• Excel for Mac (Microsoft 365 or Excel 2016+)
• Access to pulse while logged in
• (Optional) Valid API token from pulse for VBA method (get yours at /core/token/)

Method 1: CSV Export (Recommended - Simplest)

The easiest and most reliable method for Excel for Mac:

Steps:

1. While logged into pulse in your browser, navigate to any API endpoint with CSV format: http://your-server:8000/api/questions/?format=csv&page_size=1000

2. The browser will download a CSV file automatically (authentication is handled by your browser session)

3. Open the CSV file in Excel

4. Save as Excel workbook (.xlsx) if needed

Advantages: - No configuration needed - Works reliably on all platforms - Authentication handled by browser session - Simple one-click download

Note: Requires manual refresh by re-downloading the CSV.

Available CSV Endpoints:

• Questions: http://your-server:8000/api/questions/?format=csv&page_size=1000
• Question Categories: http://your-server:8000/api/question-categories/?format=csv&page_size=1000
• Question Types: http://your-server:8000/api/question-types/?format=csv&page_size=1000
• Question Roles: http://your-server:8000/api/question-roles/?format=csv&page_size=1000
• Companies: http://your-server:8000/api/companies/?format=csv&page_size=1000
• Projects: http://your-server:8000/api/projects/?format=csv&page_size=1000

Method 2: AppleScript + curl (Mac Alternative)

Note: VBA's XMLHTTP object is not available on Excel for Mac. Use AppleScript with curl instead:

Using AppleScript to Fetch and Import CSV

1. Open Script Editor (Applications > Utilities > Script Editor)
2. Create a new script:

set apiUrl to "http://your-server:8000/api/questions/?format=csv&page_size=1000"
set apiToken to "YOUR_API_TOKEN_HERE"
set downloadPath to (path to downloads folder as text) & "questions.csv"

-- Fetch data using curl
set curlCommand to "curl -H 'Authorization: Token " & apiToken & "' '" & apiUrl & "' -o " & quoted form of POSIX path of downloadPath

do shell script curlCommand

-- Open in Excel
tell application "Microsoft Excel"
    activate
    open downloadPath
end tell

display dialog "Data imported successfully!" buttons {"OK"} default button 1

1. Replace YOUR_API_TOKEN_HERE with your token from /core/token/
2. Save the script as an Application
3. Run it whenever you need to refresh data

Advantages: - Works reliably on Mac - Authenticates with API token - Can be automated with launchd or cron - Downloads fresh CSV automatically

Method 3: Terminal + curl (Command Line)

For users comfortable with Terminal:

# Set your token
TOKEN="YOUR_API_TOKEN_HERE"

# Download questions to desktop
curl -H "Authorization: Token $TOKEN" \
  "http://your-server:8000/api/questions/?format=csv&page_size=1000" \
  -o ~/Desktop/questions.csv

# Open in Excel
open ~/Desktop/questions.csv

Save this as a shell script and run it whenever you need updated data.

Filtering Data

You can filter data by adding query parameters to any CSV or API URL:

Filter by Active Status (CSV)

http://your-server:8000/api/questions/?is_active=true&format=csv&page_size=1000

Filter by Category (CSV)

http://your-server:8000/api/questions/?category=5&format=csv&page_size=1000

Search (CSV)

http://your-server:8000/api/questions/?search=compliance&format=csv&page_size=1000

Combine Filters (CSV)

http://your-server:8000/api/questions/?is_active=true&category=5&format=csv&page_size=1000

Refreshing Data

To refresh your data:

1. Right-click on the table in Excel
2. Select Refresh

Or set up automatic refresh:

1. Click on your data table
2. Go to Data tab > Queries & Connections
3. Right-click your query > Properties
4. Under Refresh control, check Refresh every and set minutes
5. Optionally check Refresh data when opening the file

Troubleshooting

"Could not connect" Error

• Verify the server URL is correct
• Ensure you're on the same network or have VPN access
• Check if the server is running

Authentication Failed

• Verify your API token is current (check /core/token/)
• For AppleScript/curl methods, ensure the Authorization header format is correct
• Format must be: Token YOUR_API_TOKEN_HERE (with "Token" prefix)

No Data Appears

• Check the page_size parameter (increase if needed)
• Verify the endpoint returns data by testing in a browser
• For CSV method, ensure you're logged into pulse in the same browser

Data Not Refreshing

• Check your network connection
• Verify the API endpoint is still accessible
• Right-click the table and manually select Refresh

Tips

• Use page_size parameter: Add ?page_size=1000 to get more records (default is 20)
• Use CSV format: Always add &format=csv for Excel-compatible downloads
• Automate with AppleScript: Save the AppleScript as an app and run it daily
• Create relationships: If importing multiple endpoints, create relationships between tables (e.g., Questions → Categories)
• Document your token: Store your API token securely (consider using a password manager)

Advanced: Automating CSV Downloads

Using AppleScript with Calendar/Automator

1. Save your AppleScript (from Method 2) as an Application
2. Open Automator and create a new Calendar Alarm
3. Add "Run AppleScript" action with your script
4. Save and add to Calendar with desired schedule

Using cron (Terminal)

Create a shell script ~/bin/fetch-pulse-data.sh:

#!/bin/bash
TOKEN="YOUR_API_TOKEN_HERE"
OUTPUT_DIR="$HOME/Documents/BNSD1_Data"

mkdir -p "$OUTPUT_DIR"

# Fetch all data
curl -H "Authorization: Token $TOKEN" \
  "http://your-server:8000/api/questions/?format=csv&page_size=1000" \
  -o "$OUTPUT_DIR/questions_$(date +%Y%m%d).csv"

curl -H "Authorization: Token $TOKEN" \
  "http://your-server:8000/api/question-categories/?format=csv&page_size=1000" \
  -o "$OUTPUT_DIR/categories_$(date +%Y%m%d).csv"

Make executable and add to cron:

chmod +x ~/bin/fetch-pulse-data.sh
crontab -e
# Add: 0 8 * * * ~/bin/fetch-pulse-data.sh

Security Note

⚠️ Important: Your API token grants access to your pulse data. Keep it secure: - Don't share your Excel files with the token embedded - Regenerate your token if compromised - Use environment-specific tokens (dev vs production)

---

For more information about available API endpoints, visit the API Documentation.

← Back to Home