How I Built a Custom Google Sheets Sync for Artist Alignment Academy

1. The Problem: Data Lives in Too Many Places

Artist Alignment Academy's workflow looked like this: a potential lead fills out a Typeform questionnaire, then books a call through Calendly. Both platforms capture useful data like names, emails, time zones, and UTM parameters. But none of it talked to each other.

The team was manually copying details into a master Google Sheet. Rows had gaps. Some leads had a name but no call date. Others had UTM tracking but no location. It was time-consuming and error-prone.

The goal was simple: one source of truth that stays current on its own.

2. The Architecture: Smart Matching, Not Blind Importing

Instead of dumping raw data into the sheet, the script takes a more thoughtful approach. It starts by scanning every existing row to understand what's already there and what's missing.

• ●It builds an email index of all existing contacts in the sheet
• ●It checks which fields are empty: name, call date, location, UTM source
• ●It only fetches data from Typeform or Calendly when it's actually needed
• ●It matches records by email address, so nothing gets duplicated

This pre-scan approach means the script is efficient. It doesn't waste API calls pulling data it won't use.

3. Pulling Data from Typeform

The script connects directly to the Typeform API and paginates through all form submissions since a configurable cutoff date. For each response, it extracts:

• ●The respondent's name and email
• ●When the form was submitted
• ●UTM parameters (source, medium, campaign, term) from hidden fields

These details get stored in a lookup table keyed by email. Later, the script checks the master sheet and fills any gaps using this data.

4. Pulling Data from Calendly

Calendly works similarly but reveals different information. The script authenticates with a personal access token, identifies the correct event type (in this case, a 60-minute consultation), and retrieves all scheduled events.

For each event, it pulls the invitee's name, email, call date, booking date, and timezone. This fills in the fields that Typeform can't, especially the scheduled call date and the lead's location.

Between the two platforms, you get a complete picture of every lead without touching a single cell manually.

5. Two-Pass Logic: Fix Existing Rows, Then Add New Ones

The sync runs in two deliberate passes:

Pass 1: Fill missing data. The script walks through every existing row in the sheet. If a row has an email but is missing a name, it checks Calendly first, then Typeform. If the call date is empty, it pulls from Calendly. If UTM data is blank, Typeform fills it in. Every field gets the best available source.

Pass 2: Add new leads. Any Typeform submission with an email that doesn't exist in the sheet yet gets added as a brand-new row. The script also cross-references Calendly to see if that person has already booked a call, so the new row starts as complete as possible.

6. Runs Every Five Minutes. No Oversight Needed

The final piece is a time-based trigger. The script sets itself to run every five minutes using Google Apps Script's built-in scheduler. No external tools. No monthly subscription. No breaking when a third-party integration updates their pricing.

If something goes wrong, like an API being temporarily down or a rate limit being hit, the script logs the issue and moves on. The next run picks up where it left off.

7. What This Means for the Business

• ●No more copying data between platforms
• ●No more incomplete rows in the tracker
• ●UTM attribution stays intact for marketing analysis
• ●New leads appear in the sheet within minutes of submitting a form
• ●The team can focus on conversations, not data entry

What used to take 30 minutes of daily admin now happens silently in the background.