Send completed jobs to a Google Sheet with a CalSpark webhook

1. In Settings, Developers, Webhook endpoints, click Add endpoint.
2. Enter your HTTPS receiver URL.
3. Under Events to send, choose "Only selected events" and tick job.completed.
4. Save, then copy the signing secret. An admin can show it again with Reveal secret on the endpoint page.

Then click Send test event. That sends a customer.updated sample about a made-up customer, so it will not look like a job. It proves the URL and the signature work. A real job.completed arrives when a job is marked completed.

Create a service account, enable the Sheets API, share the sheet with the service account email as an editor, and put the key JSON in an environment variable. Row one of a tab named Completed holds headers, with event id in column A. Reuse the verifier from the signature guide.

const express = require('express');
const { google } = require('googleapis');
const { verifyCalSpark } = require('./verify'); // from the signature guide

const app = express();
const auth = new google.auth.GoogleAuth({
  credentials: JSON.parse(process.env.GOOGLE_SERVICE_ACCOUNT_JSON),
  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});
const sheets = google.sheets({ version: 'v4', auth });
const spreadsheetId = process.env.SHEET_ID;

app.post('/calspark/jobs', express.raw({ type: 'application/json' }), async (req, res) => {
  const raw = req.body.toString('utf8');
  if (!verifyCalSpark(raw, req.get('CalSpark-Signature'), [process.env.CALSPARK_WEBHOOK_SECRET])) {
    return res.sendStatus(400);
  }
  const event = JSON.parse(raw);
  if (event.type !== 'job.completed') return res.sendStatus(200);
  const job = event.data.object;
  try {
    const seen = await sheets.spreadsheets.values.get({ spreadsheetId, range: 'Completed!A:A' });
    if (!(seen.data.values || []).flat().includes(event.id)) {
      await sheets.spreadsheets.values.append({
        spreadsheetId,
        range: 'Completed!A:H',
        valueInputOption: 'RAW',
        insertDataOption: 'INSERT_ROWS',
        requestBody: {
          values: [[event.id, job.id, job.customer_id, job.scope_of_work,
            job.actual_start, job.actual_end, job.assigned_user_ids.join(' '), job.updated]],
        },
      });
    }
    res.sendStatus(200);
  } catch (err) {
    console.error(err);
    res.sendStatus(500); // CalSpark will retry
  }
});

The append call finds the table in the range and adds a row after it. The check on column A is the dedupe. Two deliveries at the same instant could both pass it, which is rare at a small shop. Sheets allows 60 reads and 60 writes per minute per user, and this code makes one of each per event.

async function customerName(id) {
  const r = await fetch('https://www.calspark.app/api/v1/customers/' + id, {
    headers: { Authorization: 'Bearer ' + process.env.CALSPARK_API_KEY },
  });
  if (!r.ok) return 'customer ' + id;
  const c = await r.json();
  return c.company_name || [c.first_name, c.last_name].filter(Boolean).join(' ');
}

Apps Script can publish a web app that runs doPost. Google's web app guide lists the fields on the event object, and request headers are not among them. Without the CalSpark-Signature header you cannot verify anything.

The reply is also a problem. A Google developer write-up says a POST to the /exec URL first gets a 302 Found with a Location header. CalSpark does not follow redirects and counts any 3xx as a failure, so it would retry up to 8 times and could eventually disable the endpoint. I found no Google documentation that lets a script set the HTTP status, so I do not recommend this route.