Researches each new line on a client's bank feed sheet and writes who was paid, what they do and where to post it, with sources.
Uses
Google Sheets
Identify who was paid on every bank feed line
Set sheetUrl to blank.Set sheetUrl to blank.Set feedTab to Bank feed.Set feedTab to Bank feed.Set resultsTab to Vendor research.Set resultsTab to Vendor research.Set clientName to blank.Set clientName to blank.Set clientNotes to blank.Set clientNotes to blank.Set maxLinesPerRun to 10.Set maxLinesPerRun to 10.
Get today's date and save it as checkedOn.Get today's date and save it as checkedOn.Set headings to [Checked on, Bank line, Date, Amount, Status, Vendor, Website, What they do, Likely reason, Suggested account, Confidence, Independent check, Summary, Evidence, Sources, Ask the client].Set headings to [Checked on, Bank line, Date, Amount, Status, Vendor, Website, What they do, Likely reason, Suggested account, Confidence, Independent check, Summary, Evidence, Sources, Ask the client].Set alreadyChecked to an empty list.Set alreadyChecked to an empty list.
If a step below fails, save the error as resultsTabErrorIf a step below fails, save the error as resultsTabErrorTry
Google SheetsRead the rows of sheetUrl. Save it as done.Read the rows of sheetUrl. Save it as done.
If done.headers is emptyIf done.headers is emptyYes
Google SheetsAdd a row to resultsTab in sheetUrl.Add a row to resultsTab in sheetUrl.No
No steps
If done.headers isn't empty and done.headers doesn't contain Bank lineIf done.headers isn't empty and done.headers doesn't contain Bank lineYes
Stop and report a problem: The 'Vendor research' tab exists but has no 'Bank line' column, so finished lines cannot be told from new ones. Give it these headings, in this order: Checked on, Bank line, Date, Amount, Status, Vendor, Website, What they do, Likely reason, Suggested account, Confidence, Independent check, Summary, Evidence, Sources, Ask the client.Stop and report a problem: The 'Vendor research' tab exists but has no 'Bank line' column, so finished lines cannot be told from new ones. Give it these headings, in this order: Checked on, Bank line, Date, Amount, Status, Vendor, Website, What they do, Likely reason, Suggested account, Confidence, Independent check, Summary, Evidence, Sources, Ask the client.No
No steps
For each doneRow in done.rows:For each doneRow in done.rows:
Trim doneRow["Bank line"] and save it as doneLine. Trim doneRow["Bank line"] and save it as doneLine. If doneLine isn't empty and doneLine is not null If doneLine isn't empty and doneLine is not nullYes
Add doneLine to the end of alreadyChecked and save it as alreadyChecked.Add doneLine to the end of alreadyChecked and save it as alreadyChecked.No
No steps
On error
Show in the output: Could not read the '@resultsTab' tab, so it is being created with its headings.Show in the output: Could not read the '@resultsTab' tab, so it is being created with its headings.If a step below fails, save the error as createTabErrorIf a step below fails, save the error as createTabErrorTry
Google SheetsCreate the tab resultsTab in sheetUrl.Create the tab resultsTab in sheetUrl.Google SheetsAdd a row to resultsTab in sheetUrl.Add a row to resultsTab in sheetUrl.On error
Stop and report a problem: Could not read or create the results tab. Check the spreadsheet address and that Google Sheets is connected, or create the tab yourself with these headings: Checked on, Bank line, Date, Amount, Status, Vendor, Website, What they do, Likely reason, Suggested account, Confidence, Independent check, Summary, Evidence, Sources, Ask the client.Stop and report a problem: Could not read or create the results tab. Check the spreadsheet address and that Google Sheets is connected, or create the tab yourself with these headings: Checked on, Bank line, Date, Amount, Status, Vendor, Website, What they do, Likely reason, Suggested account, Confidence, Independent check, Summary, Evidence, Sources, Ask the client.Google SheetsRead the rows of sheetUrl. Save it as feed.Read the rows of sheetUrl. Save it as feed.If feed.headers is emptyIf feed.headers is emptyYes
Stop and report a problem: The '@feedTab' tab is empty or has no heading row. Paste the bank feed into it with the headings Date, Description and Amount in its first row, or put the name of the tab that holds the feed into the 'feedTab' step, then run it again.Stop and report a problem: The '@feedTab' tab is empty or has no heading row. Paste the bank feed into it with the headings Date, Description and Amount in its first row, or put the name of the tab that holds the feed into the 'feedTab' step, then run it again.No
No steps
If feed.headers doesn't contain DescriptionIf feed.headers doesn't contain DescriptionYes
Join feed.headers with , and save it as feedHeadings.Join feed.headers with , and save it as feedHeadings.Stop and report a problem: The '@feedTab' tab has no column headed Description, so there is no bank line to research. Its headings are: feedHeadings. Rename the column that holds the bank's wording for each line to Description (Date and Amount are used too when the tab has them), then run it again.Stop and report a problem: The '@feedTab' tab has no column headed Description, so there is no bank line to research. Its headings are: feedHeadings. Rename the column that holds the bank's wording for each line to Description (Date and Amount are used too when the tab has them), then run it again.No
No steps
Set checked to 0.Set checked to 0.Set succeeded to 0.Set succeeded to 0.Set failed to 0.Set failed to 0.Set skipped to 0.Set skipped to 0.Set linesLeft to an empty list.Set linesLeft to an empty list.For each line in feed.rows:For each line in feed.rows:Trim line.Description and save it as description.Trim line.Description and save it as description.If description is blank or description is nullIf description is blank or description is nullYes
Skip to the next item.Skip to the next item.No
No steps
If alreadyChecked contains descriptionIf alreadyChecked contains descriptionYes
Add 1 to skipped.Add 1 to skipped.Skip to the next item.Skip to the next item.No
No steps
If checked is greater than or equal to maxLinesPerRunIf checked is greater than or equal to maxLinesPerRunYes
If linesLeft doesn't contain descriptionIf linesLeft doesn't contain descriptionYes
Add description to the end of linesLeft and save it as linesLeft.Add description to the end of linesLeft and save it as linesLeft.No
No steps
Skip to the next item.Skip to the next item.No
No steps
Add 1 to checked.Add 1 to checked.Trim line.Date and save it as dateText.Trim line.Date and save it as dateText.If dateText is nullIf dateText is nullYes
Change dateText to blank.Change dateText to blank.No
No steps
Trim line.Amount and save it as amountText.Trim line.Amount and save it as amountText.If amountText is nullIf amountText is nullYes
Change amountText to blank.Change amountText to blank.No
No steps
Set hint to blank.Set hint to blank.If dateText isn't emptyIf dateText isn't emptyYes
Change hint to Dated dateText.Change hint to Dated dateText.No
No steps
If amountText isn't emptyIf amountText isn't emptyYes
Change hint to hintAmount amountText.Change hint to hintAmount amountText.No
No steps
If a step below fails, save the error as lineErrorIf a step below fails, save the error as lineErrorTry
Run the Verify the vendor behind a bank or card line workflow with bankLine, whatYouKnow, clientName, and clientNotes and save it as result.Run the Verify the vendor behind a bank or card line workflow with bankLine, whatYouKnow, clientName, and clientNotes and save it as result.Google SheetsAdd a row to resultsTab in sheetUrl.Add a row to resultsTab in sheetUrl.Add 1 to succeeded.Add 1 to succeeded.On error
If succeeded is 0If succeeded is 0Yes
Stop and report a problem: Stopped before writing anything: '@description' could not be researched and recorded (lineError.message). Nothing was written for it, so it is tried again on the next run. If the message says the workflow to run is missing, open this workflow's 'Run a workflow' step and choose 'Verify the vendor behind a bank or card line'; that workflow needs Serper, Exa and OpenAI connected.Stop and report a problem: Stopped before writing anything: '@description' could not be researched and recorded (lineError.message). Nothing was written for it, so it is tried again on the next run. If the message says the workflow to run is missing, open this workflow's 'Run a workflow' step and choose 'Verify the vendor behind a bank or card line'; that workflow needs Serper, Exa and OpenAI connected.No
No steps
Add 1 to failed.Add 1 to failed.Show in the output: Could not research '@description': lineError.message as text.Show in the output: Could not research '@description': lineError.message as text.Google SheetsAdd a row to resultsTab in sheetUrl.Add a row to resultsTab in sheetUrl.Add description to the end of alreadyChecked and save it as alreadyChecked.Add description to the end of alreadyChecked and save it as alreadyChecked.Count the items in linesLeft and save it as leftCount.Count the items in linesLeft and save it as leftCount.Set reportLines to an empty list.Set reportLines to an empty list.Add Researched succeeded new line(s) and wrote them to the '@resultsTab' tab; skipped line(s) were already there. to the end of reportLines and save it as reportLines.Add Researched succeeded new line(s) and wrote them to the '@resultsTab' tab; skipped line(s) were already there. to the end of reportLines and save it as reportLines.If leftCount is greater than 0If leftCount is greater than 0Yes
Add leftCount more line(s) are waiting: each run checks at most maxLinesPerRun, so run it again for the rest. to the end of reportLines and save it as reportLines.Add leftCount more line(s) are waiting: each run checks at most maxLinesPerRun, so run it again for the rest. to the end of reportLines and save it as reportLines.No
No steps
If failed is greater than 0If failed is greater than 0Yes
Add failed line(s) could not be researched and are on the tab as 'Could not research' with the reason; delete such a row to have that line tried again. to the end of reportLines and save it as reportLines.Add failed line(s) could not be researched and are on the tab as 'Could not research' with the reason; delete such a row to have that line tried again. to the end of reportLines and save it as reportLines.No
No steps
Join reportLines with a space and save it as runSummary.Join reportLines with a space and save it as runSummary.If failed is greater than 0If failed is greater than 0Yes
Stop and report a problem: runSummary.Stop and report a problem: runSummary.No
No steps
Finish the run: runSummary.Finish the run: runSummary.