Best idempotent Make scenario for Shopify Forms vouchers via Google Sheets

Questions
,
1

:bullseye: What is your goal?

We are building a low-volume, 30-day voucher MVP with this architecture:

Shopify Forms → Shopify Flow → Google Sheets → Make → transactional voucher email

Shopify Flow will add one row to Google Sheets for each form submission.

Make should then:

Detect the new row.
Use the Shopify metaobject ID to create a customer-facing voucher reference, for example:
gid://shopify/Metaobject/1234567890123 → WH-1234567890123
Calculate an expiry date equal to the submission date plus 7 days, using the Europe/Rome timezone.
Send a strictly transactional voucher email to the submitted email address, even when marketing consent is false.
Update the same Google Sheets row with:
voucher_code
expires_at
voucher_status = issued
email_sent = yes
email_sent_at

Marketing consent is optional and completely separate from delivery of the requested voucher.

:thinking: What is the problem & what have you tried?

We have not built the Make scenario yet. We want to confirm the safest architecture before implementing it.

Our proposed scenario is:

Google Sheets — Watch New Rows
Filter rows with a valid metaobject_id and email
Check whether the voucher has already been processed
Generate the voucher code from the numeric part of the Shopify metaobject ID
Add 7 days to submitted_at
Send the transactional email
Update the original Google Sheets row

Our main concern is preventing duplicate voucher emails during retries, incomplete executions or scenario reprocessing.

What is the recommended idempotency pattern in Make for this case?

Is checking email_sent = no and using sequential processing sufficient?
Should we use a Make Data Store with metaobject_id as the unique key?
How should we handle a failure occurring after the email is sent but before the Google Sheets row is updated?
Which Make functions would you recommend for extracting the numeric ID and calculating the 7-day expiry correctly?

:clipboard: Error messages or input/output bundles

No error yet — this is an architecture review before implementation.

Sanitized example input:

{
“metaobject_id”: “gid://shopify/Metaobject/1234567890123”,
“submitted_at”: “2026-08-12T10:15:00+02:00”,
“email”: “[email protected]”,
“marketing_consent”: false
}

Expected output:

{
“voucher_code”: “WH-1234567890123”,
“expires_at”: “2026-08-19”,
“voucher_status”: “issued”,
“email_sent”: “yes”
}

3

I would not rely only on email_sent = no in the sheet. That helps, but it is not enough for the failure case you called out: email succeeds, then the sheet update fails, then the scenario retries and sends again.

For this MVP I would use the metaobject ID as the idempotency key and make the Data Store the send ledger.

Suggested shape:

  1. Watch new rows from Google Sheets
  2. Validate email and metaobject_id
  3. Extract the numeric ID from the Shopify gid
  4. Look up the Data Store record by metaobject_id
  5. If status is sent or sending, stop
  6. Create or update the Data Store record as sending with row number, email, voucher_code, and started_at
  7. Send the transactional email
  8. Update the Data Store record as sent with email_sent_at
  9. Update the original Google Sheets row

That way, if the email sends but the final Google Sheets update fails, the retry sees the Data Store record as sent and does not send a duplicate. The sheet becomes the business-facing status view, not the only safety ledger.

For the voucher code, split on / and take the last item, then prepend WH-.

For the 7 day expiry, parse submitted_at, add 7 days, and format it as YYYY-MM-DD in Europe/Rome. Since your sample already includes +02:00, keep the date logic explicit and test around midnight so the voucher does not expire a day early.

I would also add one extra status value:

voucher_status = email_sent_sheet_update_failed

Use that if the send succeeded but the row update failed. Then you can repair the sheet manually without creating a duplicate email.

4

Thank you, this is very helpful. We will use the full metaobject_id as the Data Store key and treat the Data Store as the send ledger.

We have one final question about records that become stuck in sending.

If the Data Store record is successfully set to sending, but the email module then times out or returns an ambiguous error before the record can be updated to sent, every subsequent execution would stop at the existing sending status. The voucher might therefore never be delivered.

For this low-volume MVP, would you recommend:

  • storing started_at and an attempt identifier;

  • treating a recent sending record as an active lock;

  • moving an old sending record, for example after 15 minutes, to manual_review;

  • retrying automatically only when the email provider definitively confirms that the message was not accepted?

We would prefer manual review over a possible duplicate whenever the delivery result is ambiguous.

Also, if the Google Sheets update fails after the email was sent, we assume sent_sheet_update_failed should be stored in the Data Store, since the sheet itself may be unavailable. Should sent, sending, and sent_sheet_update_failed all block any further email send?

Finally, would enabling sequential processing together with the unique metaobject_id Data Store key be sufficient to avoid concurrent processing of the same submission?

Thank you again.

5

Yes, for that MVP I would use exactly that pattern and keep ambiguous cases out of automatic retry.

A practical state model would be:

new or missing record means eligible to send.

sending with a recent started_at means active lock. Do nothing.

sending older than your timeout window means move to manual_review, not automatic resend, unless you have a provider response that proves the email was not accepted.

sent means permanent block. Never send again for that metaobject_id.

sent_sheet_update_failed should also permanently block another email send. At that point the customer side already may have received the voucher, so the failure is only a reporting or reconciliation problem. The recovery action should be to repair the Sheet row from the Data Store, not resend the voucher.

I would store at least:

status
started_at
attempt_id
attempt_count
last_error
email_provider_message_id if available
sent_at if accepted

For a low volume setup, 15 minutes is a reasonable stale lock window if your normal email module timeout is much shorter. The important thing is that stale sending becomes a human review item, not a blind retry.

Sequential processing plus the unique Data Store key is a good start, but I would still treat the Data Store record as the lock. Sequential processing protects one scenario run. The Data Store key protects you across reruns, retries, webhook repeats, and manual replays.

So the simple rule is:

Only send when no record exists, or when the existing record is explicitly in a safe retry state that you set manually after checking the provider result.

Everything else blocks send and either updates reporting or goes to manual review.

6

thank you so much!!!