Write a question about a tab of your spreadsheet and get back a chart of the numbers already in it, recorded on your answers tab.
Uses
Google Sheets
OpenAI
QuickChart
Ask a question about your sheet, get a chart back
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 emptyYes
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 chooseErrorTry
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 emptyYes
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 3Yes
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 labelColumnYes
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 valueColumnYes
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 25Yes
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 0Yes
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 nullYes
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 emptyYes
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 nullYes
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 blankYes
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 emptyYes
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 pieYes
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 0Yes
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.