Send reports on a schedule
Generate a document, drop it in Space, and email the link to the people who need it.
Why anyone does this
The weekly figures already exist somewhere. What takes the time is turning them into something readable and putting it in front of the people who asked, every week, without anybody remembering to.
What a key needs
Grant these and nothing else. A key can never do more than the person who created it, and one that carries only what the job needs is one you can leave running.
The flow
-
Write the figures into a sheet
Appending rows to a spreadsheet is available today, so this step already works against a workbook that exists. Append adds after the last populated row of your own columns, which is what a weekly run wants.
POST
/v1/spreadsheets/:id/values/:range/append -
Put it somewhere durable
A link to a shared document outlives an attachment in a mailbox and can be corrected in place. Share it with the people who need it rather than mailing a copy each.
POST
/v1/files/:id/permissions -
Mail the link
Sending is available today, so the last step of this flow already works: a report you build elsewhere can be delivered now.
POST
/v1/messages/send
The shape of it
Short enough to read in one go, and close enough to run that the gaps are obvious.
# Create the sheet once; keep the id in your own store.
doc=$(curl -s -X POST https://api.veruapis.com/v1/documents \
-H "Authorization: Bearer $KEY" -H "Content-Type: application/json" \
-d '{"type":"spreadsheet","title":"Weekly figures"}' | jq -r '.data.id')
# Each run appends a row rather than rewriting a range.
curl -s -X POST "https://api.veruapis.com/v1/spreadsheets/$doc/values/A:C/append" \
-H "Authorization: Bearer $KEY" -H "Content-Type: application/json" \
-H "Idempotency-Key: weekly-$(date +%G-%V)" \
-d "{\"values\":[[\"$(date +%F)\",\"North\",1200]]}"
# Then mail the link, not a copy.
curl -s -X POST https://api.veruapis.com/v1/messages/send \
-H "Authorization: Bearer $KEY" -H "Content-Type: application/json" \
-d "{\"to\":[\"[email protected]\"],\"subject\":\"Weekly figures\",
\"text\":\"https://space.verusuite.com/d/$doc\"}"What catches people out
Every one of these came from somebody getting it wrong.
- Send an Idempotency-Key. A retried job that mails the report twice is the classic scheduled-task failure.
- Link rather than attach, so a correction updates what people open instead of arriving as a second email.
- Create the spreadsheet once and keep its id. Creating one per run leaves a drive full of near-identical files and breaks every link you have already sent.
- Append rather than write a computed range. Append needs no read first, and two runs cannot overwrite each other.