Cookbook

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.

← Back to Documentation Hub

Prerequisites

  • An existing report in your workspace (for example, stockout-report)
  • Access to Admin → Data Pipelines and Data Automation

1. Create the pipeline

  1. Open Admin (the cog icon in the upper right-hand corner) and navigate to Data Pipelines.
  2. Click New to open the pipeline editor.
  3. 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
  1. 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:

  1. GetReport (tap): Calls ReportConnection to execute report-id over a rolling 7-day window (shift(days=-7) to today). It returns a single-row table containing the report's display name and its rendered Excel binary encoded in base64_output.
  2. SetDataVar (sink): Stores the output row into an in-memory pipeline variable named ReportExport so downstream steps can reference its fields.
  3. SendEmail (sink): Calls EmailConnection and uses variables.look(...) expressions to inject the report name into the subject line and attach base64_output as a native .xlsx file.

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:

  1. Save the pipeline.
  2. Open Jobs and create a new job that executes this pipeline.
  3. 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:

  1. Open your existing daily ingestion/maintenance job.
  2. Add this email pipeline as the final step in the job sequence.
  3. Set skip_if on 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 ''>>

← Back to Documentation Hub