Send Scheduled Excel Reports via Email
This cookbook shows how to generate a report dynamically, attach it as a dated Excel (.xlsx) file, and deliver it to recipients on a schedule—complete with a direct link back to the live interactive report.
Prerequisites
- An existing report in your workspace (for example,
stockout-report) - Access to Admin → Data Pipelines and Data Automation
1. Create the pipeline
- Open Admin (the cog icon in the upper right-hand corner) and navigate to Data Pipelines.
- Click New to open the pipeline editor.
- Paste the following YAML configuration:
- name: GetReport
kind: tap
show: 5
connection:
kind: CustomConnection
locator: ReportConnection
params:
id: report-id
start: <<API.dt(None).shift(days=-7).format('YYYY-MM-DD')>>
end: <<API.dt(None).format('YYYY-MM-DD')>>
context:
locations: []
- name: SetDataVar
kind: sink
connection:
kind: Variable
locator: ReportExport
- name: SendEmail
kind: sink
connection:
kind: CustomConnection
locator: EmailConnection
params:
message:
sender: [email protected]
recipients:
- [email protected]
subject: "Emailing: <<variables.look('ReportExport[0].name')>> [<<API.dt(None).shift(days=-7).format('YYYY-MM-DD')>> - <<API.dt(None).format('YYYY-MM-DD')>>]"
text: |
Please find the attached data.
Access live report here:
https://eu.automatbi.com/#/reports/report-id
attachments:
- content: <<variables.look('ReportExport[0].base64_output')>>
name: <<variables.look('ReportExport[0].name')>> <<API.dt(None).format('YYYY-MM-DD')>>.xlsx
mime: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
- Click Execute to run a test delivery and verify the email arrives in your inbox.
2. How it works
The pipeline chains three steps together in memory without writing intermediate files to storage:
GetReport(tap): CallsReportConnectionto executereport-idover a rolling 7-day window (shift(days=-7)to today). It returns a single-row table containing the report's displaynameand its rendered Excel binary encoded inbase64_output.SetDataVar(sink): Stores the output row into an in-memory pipeline variable namedReportExportso downstream steps can reference its fields.SendEmail(sink): CallsEmailConnectionand usesvariables.look(...)expressions to inject the report name into the subject line and attachbase64_outputas a native.xlsxfile.
3. Customize for your reports
| What to change | Where to edit | Example |
|---|---|---|
| Target report | GetReport → params.id (and the URL in text) |
sales-weekly-summary |
| Date window | start and end expressions |
<<API.dt(None).shift(months=-1).format('YYYY-MM-DD')>> for a 30-day lookback |
| Report filters | GetReport → params.context |
Pass filter keys matching your report parameters (e.g., locations: ['LOC-01']) |
| Recipients | SendEmail → params.message.recipients |
Add multiple addresses as a YAML list (- [email protected]) |
4. Automate delivery on a schedule
Once your test run succeeds:
- Save the pipeline.
- Open Jobs and create a new job that executes this pipeline.
- Create a new Schedule for the job with your desired cadence (for example, every Monday at
06:00) so recipients have the updated spreadsheet before their shift begins.
Best practice: Chain into your daily ingestion job
Instead of maintaining a separate schedule—and risking the report running before your nightly data sync finishes—append this email pipeline directly to the end of your daily ingestion/maintenance job and gate it with skip_if:
- Open your existing daily ingestion/maintenance job.
- Add this email pipeline as the final step in the job sequence.
- Set
skip_ifon the steps to control when it fires—for example, to send only on the 1st day of the month:
skip_if: <<'yes' if API.dt(None).day != 1 else ''>>