Ask a question about your sheet, get a chart back

Give it a link to your spreadsheet, the tab holding the data and a plain question about it, then run it whenever you want a chart. The column headings and one example row are read, the AI is asked which column holds the labels, which holds the numbers and whether a bar, line or pie chart suits the question, and the chart is drawn from the figures already in the tab.

You end up with a permanent link to the chart image, recorded as one new row on your answers tab alongside the date, the question, the chart type, the columns used and how many points were plotted. Your data is only ever read. The columns chosen must exist on the tab and be headed as expected, or the run stops rather than plotting the wrong figures. A cell that cannot be read as a number is left off and counted, and that count is written next to the chart, so no value is guessed and no gap is hidden.

Before the first run you need a tab whose first row holds the column headings, and an existing tab to record answers on.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set dataTabName to blank.Set dataTabName to blank.

Set question to blank.Set question to blank.

Set answerTabName to Chart answers.Set answerTabName to Chart answers.

Set maxPointsOnChart to 24.Set maxPointsOnChart to 24.

Google SheetsRead the rows of spreadsheetUrl. Save it as tab.Read the rows of spreadsheetUrl. Save it as tab.

Set headings to tab.headers.Set headings to tab.headers.

Convert the first item of tab.rows to text and save it as sampleRow.Convert the first item of tab.rows to text and save it as sampleRow.

If headings is emptyIf headings is empty

Yes

Stop and report a problem: No column headings were found on the tab '@dataTabName'. The first row of that tab has to be the headings.Stop and report a problem: No column headings were found on the tab '@dataTabName'. The first row of that tab has to be the headings.

No

No steps

Set promptIntro to You are choosing what to plot from one spreadsheet tab so that one question can be answered as a chart.Set promptIntro to You are choosing what to plot from one spreadsheet tab so that one question can be answered as a chart. The question: question The tab's column headings: headings joined by , .

Set prompt to promptIntroSet prompt to promptIntro One example row: sampleRow Answer with exactly one line, in this form: label column ||| value column ||| chart type Use the column headings exactly as they are written above - do not invent a column, rename one, or answer with a column that is not listed. The value column must be the one holding the numbers to plot. The chart type must be one of bar, line or pie. Write nothing else.

Set answerText to blank.Set answerText to blank.

If a step below fails, save the error as chooseErrorIf a step below fails, save the error as chooseError

Try

Trim the response text from the OpenAI “Ask OpenAI” step and save it as answerText.Trim the response text from the OpenAI “Ask OpenAI” step and save it as answerText.

On error

Show in the output: The step that chooses the columns did not answer this run..Show in the output: The step that chooses the columns did not answer this run..

If answerText is emptyIf answerText is empty

Yes

Stop and report a problem: The step that chooses the columns did not answer this run, so nothing was charted. Run it again.Stop and report a problem: The step that chooses the columns did not answer this run, so nothing was charted. Run it again.

No

No steps

Split answerText by ||| and save it as choice.Split answerText by ||| and save it as choice.

If the length of choice is less than 3If the length of choice is less than 3

Yes

Stop and report a problem: The columns to chart could not be read from this answer: answerText truncated to 120 characters.Stop and report a problem: The columns to chart could not be read from this answer: answerText truncated to 120 characters.

No

No steps

Trim the first item of choice and save it as labelColumn.Trim the first item of choice and save it as labelColumn.

Trim choice[1] and save it as valueColumn.Trim choice[1] and save it as valueColumn.

Make the last item of choice lowercase and save it as chartType.Make the last item of choice lowercase and save it as chartType.

If headings doesn't contain labelColumnIf headings doesn't contain labelColumn

Yes

Stop and report a problem: There is no column headed '@labelColumn' on that tab. Its columns are: headings joined by , .Stop and report a problem: There is no column headed '@labelColumn' on that tab. Its columns are: headings joined by , .

No

No steps

If headings doesn't contain valueColumnIf headings doesn't contain valueColumn

Yes

Stop and report a problem: There is no column headed '@valueColumn' on that tab. Its columns are: headings joined by , .Stop and report a problem: There is no column headed '@valueColumn' on that tab. Its columns are: headings joined by , .

No

No steps

Find the position of labelColumn in headings and save it as labelIndex.Find the position of labelColumn in headings and save it as labelIndex.

Find the position of valueColumn in headings and save it as valueIndex.Find the position of valueColumn in headings and save it as valueIndex.

If labelIndex is greater than 25 or valueIndex is greater than 25If labelIndex is greater than 25 or valueIndex is greater than 25

Yes

Stop and report a problem: This workflow reads columns A to Z only, and one of the columns chosen sits past column Z.Stop and report a problem: This workflow reads columns A to Z only, and one of the columns chosen sits past column Z.

No

No steps

Set columnLetters to [A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z].Set columnLetters to [A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z].

Set labelLetter to columnLetters[@labelIndex].Set labelLetter to columnLetters[@labelIndex].

Set valueLetter to columnLetters[@valueIndex].Set valueLetter to columnLetters[@valueIndex].

Set labelCells to the values from the Google Sheets “Read column” step.Set labelCells to the values from the Google Sheets “Read column” step.

Set valueCells to the values from the Google Sheets “Read column” step.Set valueCells to the values from the Google Sheets “Read column” step.

If the first item of labelCells is not labelColumn or the first item of…If the first item of labelCells is not labelColumn or the first item of…

Yes

Stop and report a problem: Column labelLetter is headed 'the first item of labelCells' and column valueLetter is headed 'the first item of valueCells', which is not what was chosen. Nothing was charted.Stop and report a problem: Column labelLetter is headed 'the first item of labelCells' and column valueLetter is headed 'the first item of valueCells', which is not what was chosen. Nothing was charted.

No

No steps

Set chartLabels to an empty list.Set chartLabels to an empty list.

Set chartValues to an empty list.Set chartValues to an empty list.

Set valuesSkipped to 0.Set valuesSkipped to 0.

Set cursor to -1.Set cursor to -1.

For each labelCell in labelCells:For each labelCell in labelCells:

Add 1 to cursor.Add 1 to cursor.

If cursor is 0If cursor is 0

Yes

Skip to the next item.Skip to the next item.

No

No steps

If the length of chartLabels is greater than or equal to maxPointsOnChart…If the length of chartLabels is greater than or equal to maxPointsOnChart…

Yes

Stop repeating.Stop repeating.

No

No steps

Trim labelCell and save it as labelRaw.Trim labelCell and save it as labelRaw.

If labelRaw is empty or labelRaw is nullIf labelRaw is empty or labelRaw is null

Yes

Skip to the next item.Skip to the next item.

No

No steps

Trim labelRaw with , replaced by with , replaced by and save it as labelClean.Trim labelRaw with , replaced by with , replaced by and save it as labelClean.

If labelClean is emptyIf labelClean is empty

Yes

Skip to the next item.Skip to the next item.

No

No steps

Trim valueCells[@cursor] and save it as valueRaw.Trim valueCells[@cursor] and save it as valueRaw.

If valueRaw is empty or valueRaw is nullIf valueRaw is empty or valueRaw is null

Yes

Add 1 to valuesSkipped.Add 1 to valuesSkipped.

Skip to the next item.Skip to the next item.

No

No steps

Extract the first number from valueRaw and save it as valueNumber.Extract the first number from valueRaw and save it as valueNumber.

If valueNumber is blankIf valueNumber is blank

Yes

Add 1 to valuesSkipped.Add 1 to valuesSkipped.

Skip to the next item.Skip to the next item.

No

No steps

Add labelClean to the end of chartLabels and save it as chartLabels.Add labelClean to the end of chartLabels and save it as chartLabels.

Add valueNumber to the end of chartValues and save it as chartValues.Add valueNumber to the end of chartValues and save it as chartValues.

If chartLabels is emptyIf chartLabels is empty

Yes

Stop and report a problem: Nothing in column '@valueColumn' could be read as a number, so there was nothing to plot.Stop and report a problem: Nothing in column '@valueColumn' could be read as a number, so there was nothing to plot.

No

No steps

Join chartLabels with , and save it as labelsText.Join chartLabels with , and save it as labelsText.

Join chartValues with , and save it as valuesText.Join chartValues with , and save it as valuesText.

Shorten question to 90 characters and save it as chartTitle.Shorten question to 90 characters and save it as chartTitle.

Set chartUrl to blank.Set chartUrl to blank.

If chartType is pieIf chartType is pie

Yes

Trim the url from the QuickChart “Create pie chart” step and save it as chartUrl.Trim the url from the QuickChart “Create pie chart” step and save it as chartUrl.

Else if @chartType is "line"

Trim the url from the QuickChart “Create line chart” step and save it as chartUrl.Trim the url from the QuickChart “Create line chart” step and save it as chartUrl.

Else if @chartType is "bar"

Trim the url from the QuickChart “Create bar chart” step and save it as chartUrl.Trim the url from the QuickChart “Create bar chart” step and save it as chartUrl.

Otherwise

Stop and report a problem: '@chartType' is not a chart this workflow can draw. It draws bar, line and pie charts.Stop and report a problem: '@chartType' is not a chart this workflow can draw. It draws bar, line and pie charts.

If chartUrl doesn't start with https:// (ignoring case)If chartUrl doesn't start with https:// (ignoring case)

Yes

Stop and report a problem: The chart service did not return an image address this run, so nothing was recorded.Stop and report a problem: The chart service did not return an image address this run, so nothing was recorded.

No

No steps

Set skippedNote to blank.Set skippedNote to blank.

If valuesSkipped is greater than 0If valuesSkipped is greater than 0

Yes

Change skippedNote to valuesSkipped row(s) were left off the chart because their value could not be read as a number.Change skippedNote to valuesSkipped row(s) were left off the chart because their value could not be read as a number.

No

No steps

Google SheetsAdd a row to answerTabName in spreadsheetUrl.Add a row to answerTabName in spreadsheetUrl.

Show in the output: Chart: chartUrl skippedNote.Show in the output: Chart: chartUrl skippedNote.