Sync contacts to Mailchimp or Kit

Northwind keeps its subscriber list in a Google Sheet — it is where new contacts get added, tidied, and tagged. But the newsletter goes out from Mailchimp, and the two drift apart fast. Someone adds a contact to the sheet, forgets to copy it across, and that person never gets the next campaign. The sheet says one thing, the email platform says another, and nobody is sure which is right.

This script makes the sheet the single source of truth. It reads every contact row and pushes it to Mailchimp’s API, creating new subscribers and updating existing ones in the same call. Run it after editing the sheet — or on a schedule — and Mailchimp always matches it. A Kit (formerly ConvertKit) variant is shown below for teams on that platform.

What you’ll need

• A Google Sheet of contacts with email, firstName, and lastName in the first three columns, and a header in row 1.
• A Mailchimp account, an API key, and the audience (list) ID you want to sync into. The key ends in a data-centre suffix like -us1 — that suffix is the MC_DC value below.
• The API key saved as MAILCHIMP_KEY in Script Properties — see Store API keys and secrets securely.
• For the Kit variant, a Kit API secret saved as KIT_SECRET and a form ID.

The script (Mailchimp)

// Mailchimp credentials and target audience.
const MC_KEY = PropertiesService.getScriptProperties()
  .getProperty('MAILCHIMP_KEY');
const MC_LIST = 'YOUR_LIST_ID';   // the audience to sync contacts into
const MC_DC = 'us1';              // the data-centre suffix from your API key

// The spreadsheet that holds the contacts.
const CONTACTS_SHEET_ID = '1abcSubsId';

/**
 * Reads every contact row and upserts it into the Mailchimp audience —
 * new emails are added, existing ones are updated.
 */
function syncToMailchimp() {
  // 1. Read the sheet and drop the header row.
  const [_, ...rows] = SpreadsheetApp.openById(CONTACTS_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  // 2. Bail out early if there are no contacts.
  if (!rows.length) {
    Logger.log('No contacts to sync — nothing to do.');
    return;
  }

  let synced = 0;

  // 3. Upsert each contact one at a time.
  for (const [email, firstName, lastName] of rows) {
    if (!email) continue;

    // 4. Mailchimp keys members by an MD5 hash of the lowercased email.
    //    PUT to that hash creates the member if new, updates it if not.
    const hash = emailHash(email);
    const url = 'https://' + MC_DC + '.api.mailchimp.com/3.0' +
      '/lists/' + MC_LIST + '/members/' + hash;

    UrlFetchApp.fetch(url, {
      method: 'put',
      contentType: 'application/json',
      headers: {
        Authorization: 'Basic ' + Utilities.base64Encode('any:' + MC_KEY),
      },
      payload: JSON.stringify({
        email_address: email,
        status_if_new: 'subscribed',  // only applied when the member is new
        merge_fields: { FNAME: firstName, LNAME: lastName },
      }),
      muteHttpExceptions: true,
    });
    synced++;
  }

  Logger.log('Synced ' + synced + ' contact(s) to Mailchimp.');
}

/**
 * Returns the lowercase-email MD5 hash Mailchimp uses to address a member.
 * Apps Script's digest returns signed bytes, so each byte is normalised
 * back into the 0-255 range before being formatted as hex.
 */
function emailHash(email) {
  return Utilities.computeDigest(
    Utilities.DigestAlgorithm.MD5,
    email.toLowerCase()
  )
    .map((b) => (b < 0 ? b + 256 : b).toString(16).padStart(2, '0'))
    .join('');
}

How it works

1. syncToMailchimp opens the contacts sheet and uses array destructuring to keep every data row while discarding the header.
2. If there are no data rows, it logs a message and stops.
3. It loops the rows, skipping any with a blank email.
4. For each contact it computes the MD5 hash Mailchimp uses as the member ID, then sends a PUT to that member URL. A PUT is an upsert: if no member with that hash exists, Mailchimp creates one; if it does, Mailchimp updates it. status_if_new only applies to brand-new members, so re-running the script never resubscribes someone who unsubscribed.
5. emailHash does the digest. Apps Script returns signed bytes, so negatives are shifted back into 0-255 before being padded to two hex digits — the small detail that makes the hash match what Mailchimp expects.

Example run

Say the contacts sheet holds:

email	firstName	lastName
[email protected]	Sam	Carter
[email protected]	Dana	Lee

The first run creates both members in the Mailchimp audience with subscribed status, and the log reads Synced 2 contact(s) to Mailchimp. If you later fix a typo in Dana’s surname and run again, the same two PUTs fire — Sam is unchanged, Dana’s LNAME is updated, and no duplicates appear.

The script (Kit variant)

Kit’s API addresses subscribers by email directly, so no hashing is needed:

const KIT_SECRET = PropertiesService.getScriptProperties()
  .getProperty('KIT_SECRET');
const KIT_FORM = 'YOUR_FORM_ID';   // subscribers are added through a form

/** Adds each sheet contact to a Kit form, which creates or updates them. */
function syncToKit() {
  const [_, ...rows] = SpreadsheetApp.openById(CONTACTS_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  if (!rows.length) return;

  for (const [email, firstName] of rows) {
    if (!email) continue;
    const url = 'https://api.convertkit.com/v3/forms/' +
      KIT_FORM + '/subscribe';
    UrlFetchApp.fetch(url, {
      method: 'post',
      contentType: 'application/json',
      payload: JSON.stringify({
        api_secret: KIT_SECRET,
        email: email,
        first_name: firstName,
      }),
      muteHttpExceptions: true,
    });
  }
}

Trigger it

If contacts are edited often, run the sync on a schedule:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose syncToMailchimp (or syncToKit), a Time-driven source, and a Day timer at an off-peak hour.

For occasional edits, skip the trigger and run it by hand from the editor, or add an onOpen menu item so editors can sync from the sheet itself.

Watch out for

• One API call per contact. A few hundred rows is fine, but a few thousand will be slow and may brush the six-minute execution limit. For large lists, use Mailchimp’s batch endpoint, which takes many members in a single request.
• The script never deletes. Removing a row from the sheet does not unsubscribe that person — Mailchimp still has them. The sync only adds and updates.
• status_if_new is deliberate. It means a contact who unsubscribed in Mailchimp stays unsubscribed even though they are still in the sheet. Treat the sheet as the membership list, not the consent record.
• The data-centre suffix must be right. MC_DC has to match the -usX suffix on your API key, or every request 404s. Read it straight off the key.
• Kit and Mailchimp model contacts differently — Kit uses forms and tags rather than audiences. Pick one platform per sheet; do not run both syncs against the same data and expect identical behaviour.