Identify who was paid on every bank feed line

Point it at the Google Sheet that holds a client's bank feed and run it. Every line that has not been looked into yet is handed to the vendor verifier, which searches the live web for the business or person behind it and reads the vendor's own page, and what it finds lands as one row on a 'Vendor research' tab: status, vendor, website, what they do, the likely reason, a suggested account, the independent check's verdict, the summary, the evidence, the sources and a question for the client.

The feed is never edited and finished lines are skipped on the next run. If the very first line of a run cannot be researched, usually because the verifier is not set up yet, the run stops and says why, and nothing is written. A line that fails later is recorded as 'Could not research' and the run ends as failed. A feed tab without a Description column stops the run with a note of what it needs. Each run is capped so the cost stays predictable, and its last message says how many lines are still waiting.

You need Google Sheets connected, and the 'Verify the vendor behind a bank or card line' workflow imported and chosen in this one's 'Run a workflow' step, with Serper, Exa and OpenAI connected for it. When you add it, give the sheet's link, the client's name and the client's notes; import a fresh copy for each client.

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 resultsTabError

Try

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 empty

Yes

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 line

Yes

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 null

Yes

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 createTabError

Try

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 empty

Yes

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 Description

Yes

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 null

Yes

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

No

No steps

If alreadyChecked contains descriptionIf alreadyChecked contains description

Yes

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 maxLinesPerRun

Yes

If linesLeft doesn't contain descriptionIf linesLeft doesn't contain description

Yes

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 null

Yes

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 null

Yes

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 empty

Yes

Change hint to Dated dateText.Change hint to Dated dateText.

No

No steps

If amountText isn't emptyIf amountText isn't empty

Yes

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 lineError

Try

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 0

Yes

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 0

Yes

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 0

Yes

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 0

Yes

Stop and report a problem: runSummary.Stop and report a problem: runSummary.

No

No steps

Finish the run: runSummary.Finish the run: runSummary.