How to clean up a dispatch dashboard in Google Sheets
Clean up a dispatch dashboard in Google Sheets: stop duplicate rows, standardize formatting, show live wait time, and build a simple ETA model.
Automate monthly Salesforce exports from S3 + Google Sheets
Automate monthly Salesforce exports from S3 + Google Sheets: pull daily files from S3, calculate member status in Sheets, and generate a weekly CSV in Drive.
TABLE OF CONTENTS
If you’re manually exporting data every month and uploading it into Salesforce, you can automate most of it: pull daily files from S3, calculate a clean “member status” in Google Sheets, and generate a weekly CSV your team uploads to Salesforce in minutes.
The pattern for automating Salesforce exports: daily ingest, weekly export
Most teams get stuck because they try to build the final Salesforce upload file first. A more reliable approach is to separate the pipeline into two layers:
- Internal calculation layer (messy is okay): where you ingest raw data, append new rows every day, and compute the “truth.”
- Client-facing export layer (clean and predictable): a weekly CSV with the exact columns Salesforce expects.
This separation makes the automation easier to troubleshoot and safer to share.
What to export to Salesforce (keep it boring)
In a nonprofit texting workflow we mapped, the Salesforce upload file needed four columns:
- Campaign ID
- Constituent/Contact ID
- Member status (Texted, Link clicked, Responded, Opted out)
- Sent date + sent by (if available)
The only “hard” part is member status, because it has precedence rules:
- Opted out overrides everything
- Responded overrides Link clicked
- Link clicked overrides Texted
Step 1: Pull daily exports from S3 into your working sheet
Start by pulling each daily export file from S3 and appending it to a dedicated tab in your internal Google Sheets workbook. We usually run this step in Make on a daily schedule.
A simple structure is:
messagestab (append daily)opt_outstab (append daily)link_clickstab (append daily)campaign_lookuptab (maintained by the client/team)
Add one extra column when you append each daily export:
S3 File Date (parsed from the filename). This makes auditing and backfills much easier.Step 2: Maintain a lightweight campaign lookup sheet
Sometimes the “Campaign ID” Salesforce needs is not present in the raw exports. In that case, maintain a small lookup table in Sheets that maps:
- Texting platform campaign name (or internal campaign key)
- Salesforce campaign ID (or whatever identifier the client uses)
- Optional send date override
This lets the client own the mapping without touching the automation.
Step 3: Compute member status with clear precedence
Once the raw tabs are in place, compute status by choosing a stable join key (often the conversation ID):
- If conversation ID exists in
opt_outs→ status = Opted out - Else if the conversation has a reply in messages → status = Responded
- Else if conversation ID exists in
link_clicks→ status = Link clicked - Else status = Texted
Even if you plan to implement this logic in Make, it’s worth first proving it in Sheets using a small sample week so the client can validate the output.
Step 4: Generate a weekly CSV in Google Drive for Salesforce upload
Instead of exposing your internal calc sheet to the client, generate a clean weekly CSV and drop it into a shared Google Drive folder.
Benefits:
- It matches the client’s existing upload workflow.
- It’s easy to QA before it’s sent.
- The same pipeline can later be upgraded to push directly into Salesforce.
Common gotchas (and how to avoid them)
- Status drift: A contact might be “Texted” on day 1 and “Responded” on day 3. Your weekly export should output the highest-precedence status for the week, not duplicate rows.
- Send date ambiguity: If the send date isn’t reliable in exports, add it to the campaign lookup temporarily.
- Client visibility: Keep internal tabs private; only share the weekly CSV.
When to level up to direct Salesforce writes
Once the weekly CSV export is stable for 4–8 weeks, you can usually replace the “export + manual upload” step with a direct integration that writes campaign/member status updates into Salesforce.
Get help building this pipeline
Most teams get the S3 ingest running and then stall on the status precedence rules or on keeping the weekly CSV clean. If that’s where you are, book a discovery call and one of our consultants will design the pipeline with you end-to-end (S3 ingest, status logic, and weekly CSV generation). We document the full workflow so your team can run it without us.
Related Posts
How to automate BambooHR pension-report CSVs after payroll
BambooHR pension report automation: generate compliant CSVs right after payroll, validate required fields, and deliver the files securely over SFTP.
Fix QuickBooks Duplicate Customers by Email Domain
Fix QuickBooks duplicate customer email domain issues with Zapier Formatter and API-based lookup. Reduce duplicate records, failed receipts, and manual cleanup.
