GroundmailDocs

Google Sheets and Groundmail

Send email from a Google Sheet with Google Apps Script. A short script reads rows, calls POST /emails with UrlFetchApp, and writes the result back, so each row is sent once.

Prerequisites

  • A Groundmail account with a verified sending domain.
  • A Groundmail API key. Create one under API Keys in the dashboard. Choose Send only, and limit it to your sending domain with the allowed domains option. See API keys.
  • A Google Sheet with a header row. This guide uses the columns to, subject, message and status.

Groundmail is for transactional mail such as order updates, receipts and alerts. Don't use a sheet to send newsletters or cold email; see the Acceptable Use Policy.

1. Store the key

  1. In the sheet, open Extensions, then Apps Script.
  2. In the Apps Script editor, click Project Settings (the gear icon).
  3. Under Script Properties, click Add script property. Enter GROUNDMAIL_API_KEY as the Property and your key as the Value, then click Save script properties.

Script properties are shared with everyone who can edit the script, so give the script's collaborators only what they need and use a Send only key.

2. Add the script

Replace the contents of the editor with:

const FROM = 'notifications@yourdomain.com'

function sendEmail_(apiKey, row, rowNumber) {
  const response = UrlFetchApp.fetch('https://api.groundmail.dev/emails', {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + apiKey,
      'Idempotency-Key': 'sheet-' + SpreadsheetApp.getActive().getId() + '-row-' + rowNumber,
    },
    payload: JSON.stringify({
      from: FROM,
      to: row.to,
      subject: row.subject,
      text: row.message,
    }),
    muteHttpExceptions: true,
  })
  return { code: response.getResponseCode(), body: response.getContentText() }
}

function sendPendingRows() {
  const apiKey = PropertiesService.getScriptProperties().getProperty('GROUNDMAIL_API_KEY')
  const sheet = SpreadsheetApp.getActiveSheet()
  const values = sheet.getDataRange().getValues()
  const header = values[0]
  const col = {}
  header.forEach(function (name, i) { col[name] = i })

  for (let r = 1; r < values.length; r++) {
    if (values[r][col.status]) continue // already handled
    const result = sendEmail_(apiKey, {
      to: values[r][col.to],
      subject: values[r][col.subject],
      message: values[r][col.message],
    }, r + 1)
    const ok = result.code >= 200 && result.code < 300
    const note = ok ? 'sent ' + JSON.parse(result.body).id : 'error ' + result.code + ' ' + result.body
    sheet.getRange(r + 1, col.status + 1).setValue(note.slice(0, 500))
  }
}

Change FROM to an address on your verified domain. The script skips any row whose status cell has something in it, sends the rest, and writes the Groundmail message id (msg_…) or the error into status. The Idempotency-Key header is built from the spreadsheet and the row number, so running the script twice on the same row does not send twice within 24 hours. See Idempotency.

The first time you run sendPendingRows, Google asks you to authorize the script, including permission to connect to an external service, which is how UrlFetchApp reaches the Groundmail API.

Google applies daily quotas to Apps Script, including URL fetch calls, so this approach suits modest volumes. For anything larger, send from your application instead; see Emails and batch sends.

3. Test it

Put a test key (gm_test_…, created by ticking Test key on the API Keys page) in the script property and add a row:

to subject message status
delivered@simulator.groundmail.dev Sheet test It works.

Run sendPendingRows from the editor. The status cell becomes sent msg_…. A test key runs every check but delivers nothing. The email is stored with "test": true, and you can see it under Emails in the Groundmail dashboard.

To see a failure, add a row to bounce@simulator.groundmail.dev, or use a live key to send a simulated hard bounce. All simulator outcomes are in Testing. When you are done, replace the property with a live key and clear the status cells.

To send automatically, use Triggers (the clock icon) in the Apps Script editor to run sendPendingRows on a schedule or when the sheet changes.

Troubleshooting

error 401. The key is missing or wrong. Check the script property name is exactly GROUNDMAIL_API_KEY and the key is active on the API Keys page.

error 403. The domain in FROM is not verified on your account, or the account is still on the sandbox sender, which only sends to your own account email. Verify the domain under Domains. A domain_not_allowed error means the key is limited to other domains.

error 400. A cell is empty or to is not a valid address. The response body names the field. See Errors.

error 422 with suppressed. The recipient is on your suppression list or was held back as unable to receive mail. See Suppressions.

Permission errors when the script calls UrlFetchApp. Run the function from the editor once and approve the permissions, including connecting to an external service.