Find matching rows in a spreadsheet

Every hour, one tab of a Google Sheet is read in full and the rows you are looking for are picked out: every row on it, the rows where one column holds a given value, or the rows where that column is still empty. A match ignores capitals and stray spaces, and a row with nothing in it is skipped.

What it finds is listed on the run page and handed to whatever step you add next, such as a message for each row or an update that marks it done. On its own it changes nothing in the sheet and sends nothing.

A column heading the tab does not have fails the run and names the headings it does have, so a renamed column is noticed rather than passed off as an hour with nothing to find.

Run every hour.Run every hour.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set tabName to blank.Set tabName to blank.

Set lookupColumn to blank.Set lookupColumn to blank.

Set lookupValue to blank.Set lookupValue to blank.

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

Set found to nothing.Set found to nothing.

If a step below fails, save the error as searchErrorIf a step below fails, save the error as searchError

Try

Run Python code on rows, headers, column, value, and tab and save it as found.Run Python code on rows, headers, column, value, and tab and save it as found.

On error

If searchError.message contains inputs are too largeIf searchError.message contains inputs are too large

Yes

Stop and report a problem: The '@tabName' tab has sheet.count rows, more than this workflow can search in one run: the search takes up to 1 MB of the sheet, which is a few thousand rows of a typical sheet. Move rows you no longer need to search onto another tab so this one fits, then run it again.Stop and report a problem: The '@tabName' tab has sheet.count rows, more than this workflow can search in one run: the search takes up to 1 MB of the sheet, which is a few thousand rows of a typical sheet. Move rows you no longer need to search onto another tab so this one fits, then run it again.

No

No steps

Stop and report a problem: searchError.message.Stop and report a problem: searchError.message.

If found.problem isn't blankIf found.problem isn't blank

Yes

Stop and report a problem: found.problem.Stop and report a problem: found.problem.

No

No steps

Keep each item in sheet.rows where found.row_numbers contains item.row_number and save it as matchingRows.Keep each item in sheet.rows where found.row_numbers contains item.row_number and save it as matchingRows.

Show in the output: found.summary.Show in the output: found.summary.