Weekly chart of how a metric has moved

Every Monday at 08:00, the measurement history you keep in Google Sheets is read, the measure you name is drawn as a line chart of its most recent readings, and the chart is emailed to you with a short note from the AI on how the numbers have moved.

The note describes only what the readings show: no forecast, no target, no advice and no explanation of why anything moved, because a list of numbers cannot tell you that. The readings are printed underneath, so nothing in the note has to be taken on trust. A value can be written with currency signs, commas or percentages and still read fine, and a row with no readable number is listed as left off rather than counted as zero. If the chart cannot be drawn the readings are still emailed, and if nothing matches the measure you get an email saying so.

The tab needs one row per reading with 'Period', 'Measure' and 'Value' columns, oldest at the top. The email carries a permanent link to the chart image rather than an attachment.

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 rowError

Try

Make entry.Measure lowercase and save it as rowMeasure.Make entry.Measure lowercase and save it as rowMeasure.

If rowMeasure is wantedMeasureIf rowMeasure is wantedMeasure

Yes

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 empty

Yes

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 maxPoints

Yes

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 empty

Yes

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 chartError

Try

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 noteError

Try

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: 400

Trim 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 empty

Yes

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 empty

Yes

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 empty

Yes

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 empty

Yes

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