Every Monday, charts the recent history of one metric from your sheet and emails you the chart with a note on what changed.
Uses
Claude
Gmail
Google Sheets
QuickChart
Weekly chart of how a metric has moved
Run every Monday at 08:00.Run every Monday at 08:00.
Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.Set measureName to blank.Set measureName to blank.Set recipientEmail to blank.Set recipientEmail to blank.Set historyTab to History.Set historyTab to History.Set maxPoints to 24.Set maxPoints to 24.
Trim measureName and save it as measureLabel.Trim measureName and save it as measureLabel.
Make measureLabel lowercase and save it as wantedMeasure.Make measureLabel lowercase and save it as wantedMeasure.Google SheetsRead the rows of spreadsheetUrl. Save it as history.Read the rows of spreadsheetUrl. Save it as history.Set periodLabels to an empty list.Set periodLabels to an empty list.Set pointValues to an empty list.Set pointValues to an empty list.Set readingLines to an empty list.Set readingLines to an empty list.Set leftOff to an empty list.Set leftOff to an empty list.
For each entry in history.rows:For each entry in history.rows:
If a step below fails, save the error as rowErrorIf a step below fails, save the error as rowErrorTry
Make entry.Measure lowercase and save it as rowMeasure.Make entry.Measure lowercase and save it as rowMeasure.
If rowMeasure is wantedMeasureIf rowMeasure is wantedMeasureYes
Trim entry.Period and save it as periodRaw.Trim entry.Period and save it as periodRaw.Trim periodRaw with "Trim periodRaw with
replaced by with , replaced by with replaced by and save it as period.Extract the first number from entry.Value and save it as reading.Extract the first number from entry.Value and save it as reading.If period is emptyIf period is emptyYes
Add a row with no period name to the end of leftOff and save it as leftOff.Add a row with no period name to the end of leftOff and save it as leftOff.Else if @reading is blank
Add period to the end of leftOff and save it as leftOff.Add period to the end of leftOff and save it as leftOff.Otherwise
Add period to the end of periodLabels and save it as periodLabels.Add period to the end of periodLabels and save it as periodLabels.Add reading as text to the end of pointValues and save it as pointValues.Add reading as text to the end of pointValues and save it as pointValues.Add period: reading as text to the end of readingLines and save it as readingLines.Add period: reading as text to the end of readingLines and save it as readingLines.No
No steps
On error
Show in the output: Skipped a row that could not be read: rowError as text.Show in the output: Skipped a row that could not be read: rowError as text.Set cappedNote to blank.Set cappedNote to blank.
Set takeFromEnd to 0 minus maxPoints.Set takeFromEnd to 0 minus maxPoints.If the length of periodLabels is greater than maxPointsIf the length of periodLabels is greater than maxPointsYes
Take periodLabels from position takeFromEnd on and save it as periodLabels.Take periodLabels from position takeFromEnd on and save it as periodLabels.Take pointValues from position takeFromEnd on and save it as pointValues.Take pointValues from position takeFromEnd on and save it as pointValues.Take readingLines from position takeFromEnd on and save it as readingLines.Take readingLines from position takeFromEnd on and save it as readingLines.
Change cappedNote to Only the most recent maxPoints as text readings are charted. Raise 'maxPoints' to chart more, at the cost of a more crowded axis.Change cappedNote to Only the most recent maxPoints as text readings are charted. Raise 'maxPoints' to chart more, at the cost of a more crowded axis.No
No steps
If periodLabels is emptyIf periodLabels is emptyYes
GmailSend an email to recipientEmail.Send an email to recipientEmail.
Subject: No readings to chart for measureLabelNothing on the '@historyTab' tab had a readable number against the measure '@measureLabel', so there is no chart to send.
Check that the measure is spelled the same way in the Measure column, and that whatever fills that tab is still running.
Finish the run.Finish the run.No
No steps
Join periodLabels with , and save it as labelsText.Join periodLabels with , and save it as labelsText.Join pointValues with , and save it as valuesText.Join pointValues with , and save it as valuesText.Join readingLines with a new line and save it as numbersText.Join readingLines with a new line and save it as numbersText.Set chartUrl to blank.Set chartUrl to blank.If a step below fails, save the error as chartErrorIf a step below fails, save the error as chartErrorTry
QuickChartCreate a line chart titled measureLabel. Save it as chart.Create a line chart titled measureLabel. Save it as chart.Trim chart.url and save it as chartUrl.Trim chart.url and save it as chartUrl.On error
Show in the output: The chart could not be drawn: chartError as text.Show in the output: The chart could not be drawn: chartError as text.Set note to blank.Set note to blank.If a step below fails, save the error as noteErrorIf a step below fails, save the error as noteErrorTry
ClaudeAsk Claude: 'Below is one measure taken from a spreadsheet: one reading per period, oldest first. Write two or three sentences describing how it has moved.Ask Claude: 'Below is one measure taken from a spreadsheet: one reading per period, oldest first. Write two or three sentences describing how it has moved.
Rules you must follow:
- Describe only what these readings show. Do not forecast, project, extrapolate or say what is likely to happen next.
- Do not suggest a target, a goal or an action, and do not say whether a number is good or bad.
- Do not explain WHY anything moved. A list of readings cannot tell you that, and a plausible invented cause is worse than none.
- Use the readings exactly as given. Do not invent readings for periods that are not listed.
- Say where the highest and lowest readings fall, and whether the most recent stretch moves differently from the earlier part of the run.
Measure: measureLabel
Readings:
numbersText'. Save it as written.
Maximum response length: 400Trim written.response_text and save it as note.Trim written.response_text and save it as note.On error
Show in the output: The note on what changed could not be written: noteError as text.Show in the output: The note on what changed could not be written: noteError as text.Set bodyParts to an empty list.Set bodyParts to an empty list.Add Here is how '@measureLabel' has moved over its last the length of periodLabels as text readings. to the end of bodyParts and save it as bodyParts.Add Here is how '@measureLabel' has moved over its last the length of periodLabels as text readings. to the end of bodyParts and save it as bodyParts.If note isn't emptyIf note isn't emptyYes
Add What the readings show:Add What the readings show:
note to the end of bodyParts and save it as bodyParts.No
No steps
If chartUrl is emptyIf chartUrl is emptyYes
Add The chart could not be drawn this time, so the readings are below on their own. to the end of bodyParts and save it as bodyParts.Add The chart could not be drawn this time, so the readings are below on their own. to the end of bodyParts and save it as bodyParts.No
Add The chart:Add The chart:
chartUrl to the end of bodyParts and save it as bodyParts.Add The readings it was drawn from:Add The readings it was drawn from:
numbersText to the end of bodyParts and save it as bodyParts.If cappedNote isn't emptyIf cappedNote isn't emptyYes
Add cappedNote to the end of bodyParts and save it as bodyParts.Add cappedNote to the end of bodyParts and save it as bodyParts.No
No steps
If leftOff isn't emptyIf leftOff isn't emptyYes
Add Left off the chart, with no readable number: leftOff joined by , to the end of bodyParts and save it as bodyParts.Add Left off the chart, with no readable number: leftOff joined by , to the end of bodyParts and save it as bodyParts.No
No steps
Join bodyParts with a blank line and save it as emailBody.Join bodyParts with a blank line and save it as emailBody.GmailSend an email to recipientEmail.Send an email to recipientEmail.
Subject: measureLabel - how it has movedemailBody