Connect Google Sheets to Mailjet: Send, Track and Log the Whole Round Trip

Connecting Google Sheets to Mailjet takes about sixty lines of Apps Script and no third-party connector. Sending is one UrlFetchApp call to Mailjet's v3.1 Send API; tracking is the mirror image — you deploy the same script as a web app, paste its URL into Mailjet's Event API settings, and every open, click and bounce lands back in a tab of the same spreadsheet. That round trip is the part connector platforms charge a monthly fee for, and it is the part that is genuinely worth building properly.

If you only need to send a handful of internal notifications, you do not need Mailjet at all — Apps Script's built-in mail service is simpler, and our Google Sheets SMTP guide covers when each one is right. This post is for the case where you have outgrown that: a verified sending domain, real volume, and delivery data you can act on.

Step 1: keys, and where to put them

In Mailjet, open Account Settings → API Key Management and copy the API Key and Secret Key. Mailjet uses HTTP Basic auth, so these two together are your credentials.

In the Apps Script editor (Extensions → Apps Script from your sheet), go to Project Settings → Script properties and add MJ_KEY and MJ_SECRET. Do not put them in the code. A spreadsheet gets shared, duplicated and exported far more casually than a codebase does, and a key in the script body travels with every copy.

Step 2: send from your rows

Set up a Queue tab with columns Email | Name | InvoiceRef | Status | SentAt | MessageID. This function walks it, sends, and writes the outcome back:

const MJ_URL = 'https://api.mailjet.com/v3.1/send';

function mjAuth_() {
  const p = PropertiesService.getScriptProperties();
  return 'Basic ' + Utilities.base64Encode(
    p.getProperty('MJ_KEY') + ':' + p.getProperty('MJ_SECRET')
  );
}

function sendQueue() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Queue');
  const rows  = sheet.getDataRange().getValues();
  rows.shift();

  rows.forEach((row, i) => {
    const [email, name, ref, status] = row;
    if (!email || status === 'SENT') return;
    const rowNumber = i + 2;

    const res = UrlFetchApp.fetch(MJ_URL, {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: mjAuth_() },
      muteHttpExceptions: true,
      payload: JSON.stringify({
        Messages: [{
          From: { Email: 'billing@yourdomain.com', Name: 'Your Company' },
          To:   [{ Email: email, Name: name }],
          TemplateID: 1234567,           // built in Mailjet's template editor
          TemplateLanguage: true,
          Subject: 'Your invoice ' + ref,
          Variables: { name: name, invoice_ref: ref },
          CustomID: 'row-' + rowNumber,  // the join key — see below
        }],
      }),
    });

    if (res.getResponseCode() >= 300) {
      sheet.getRange(rowNumber, 4).setValue('ERROR ' + res.getResponseCode());
      return;
    }

    const body = JSON.parse(res.getContentText());
    sheet.getRange(rowNumber, 4).setValue('SENT');
    sheet.getRange(rowNumber, 5).setValue(new Date());
    sheet.getRange(rowNumber, 6).setValue(body.Messages[0].To[0].MessageID);
  });
}

Two things here earn their keep long after the first send works.

CustomID is the whole integration. Mailjet echoes it back on every event for that message. Set it to the row number — or an invoice reference, or a deal ID — and you can join an open or a bounce back to the exact row that caused it. Without it you are matching on email address alone, and the moment a customer receives two messages from you, you cannot tell which one they opened.

Store the MessageID. It is Mailjet's own handle on the message and the thing their support will ask for when a delivery goes strange.

Use TemplateID rather than pasting HTML into the script. Building the email in Mailjet's template editor means whoever writes your copy can change it without touching code, and the Variables object is how you pass the row's values into it.

Step 3: catch the events coming back

This is the half that turns a send script into a system. Add a doPost to the same project:

function doPost(e) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Events');
  const body  = JSON.parse(e.postData.contents);

  // Mailjet sends one object, or an array when grouped events are on
  const events = Array.isArray(body) ? body : [body];

  const rows = events.map(ev => ([
    new Date(ev.time * 1000),   // Mailjet sends Unix seconds
    ev.event,                   // sent | open | click | bounce | blocked | spam | unsub
    ev.email,
    ev.CustomID || '',          // -> 'row-14', your join key
    ev.MessageID || '',
    ev.error || ev.error_related_to || '',
  ]));

  if (rows.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 6).setValues(rows);
  }

  return ContentService.createTextOutput(JSON.stringify({ ok: true }))
    .setMimeType(ContentService.MimeType.JSON);
}

Deploy it: Deploy → New deployment → Web app, Execute as yourself, Who has access Anyone. Copy the /exec URL, then in Mailjet open Account Settings → Event Notifications (Event API) and paste it in, ticking the events you care about.

Turn on Mailjet's grouped events option. Without it, a send to four hundred people generates four hundred separate HTTP calls at your script, which is both slow and a good way to meet Apps Script's execution limits. The array handling above is there precisely for that.

Note the batched setValues rather than appendRow in a loop — one write instead of hundreds. On a busy list that difference is the difference between a script that finishes and one that times out. Our webhooks guide goes deeper on the doPost patterns that survive real traffic.

Step 4: turn events into numbers

Once events are landing, the reporting is spreadsheet formulas, not code. On a Dashboard tab:

Metric	Formula
Delivered	=COUNTIF(Events!B:B, "sent")
Unique opens	=COUNTA(UNIQUE(FILTER(Events!C:C, Events!B:B="open")))
Bounce rate	=COUNTIF(Events!B:B,"bounce") / COUNTIF(Events!B:B,"sent")
Did this row open?	=COUNTIFS(Events!D:D, "row-"&ROW(), Events!B:B, "open") > 0

That last one is why CustomID mattered. It puts a true/false next to each person in your Queue tab — a follow-up list that maintains itself.

Add one guard while you are here: never send to an address that has a bounce, spam or unsub event on record. A COUNTIFS check at the top of your send loop costs one line and protects the sending reputation that everything else depends on.

Keeping the contact list in sync

If you also maintain a Mailjet contact list, push the sheet up on a schedule rather than editing both by hand. Mailjet's REST API exposes /v3/REST/contact and /v3/REST/contactslist, and the rule that keeps this from rotting is one direction only: the sheet is the source of truth, Mailjet is the copy. Two-way sync between a spreadsheet and a mail platform sounds better and fails in every edge case — an edit on both sides between runs, and you have no principled way to decide which wins.

The one exception is unsubscribes, which must flow back down. Someone who unsubscribes in Mailjet has to be marked in the sheet, or your next sync will re-add them.

What this replaces, and what it costs

Mailjet's free tier at the time of writing covers 6,000 emails a month with a 200/day ceiling, and the script costs nothing to run. A connector platform doing the same round trip starts around $20–$30/month and bills by task — every send and every returned event is a task, so the bill grows exactly as your list does.

The structural difference matters more than the money. The script sits in the spreadsheet your team already opens, uses your own Mailjet account, and can be read end to end by anyone who can read JavaScript. Nothing sits between your data and your mail provider that you did not put there. That is the same case we make in our rent-or-own comparison of n8n, Zapier and Make — the arithmetic over three years is not close.

Where this gets genuinely fiddly is the operational edge: retries that do not double-send, grouped-event batching under load, suppression lists, and a log an accountant would accept. If you would rather have that built and handed over working, tell us what you are sending and to how many people. A short feasibility review is free — and if the answer is that the script above is already enough for you, we will say so.