Question

Lookup first blank cell in a column Google Sheets

  • 24 March 2021
  • 4 replies
  • 3420 views

Hi there,

I am attempting a Zap step using ‘Lookup Spreadsheet Row’ in Google sheets, and I cannot find a way to tell Zapier to look for the first blank cell (or cell with no value) in the search column (leaving the ‘Search Value’ field blank fails the step as a value is required). Is there an operator Zapier recognises that tells it to search for the next blank row/cell in a column?

The broader challenge - I have a list of unique sequential IDs in column C, and I need Zapier to update a row to included new registrant details (such as name, email etc) when they register. Using the ‘create new row’ action simply inserts a new row below the last ID. I have also considered having Zapier assign a unique ID when it creates a new row, but I don’t know how to have it lookup the last ID in sequence and add the ID+1. I need the ID for later steps.

Any ideas?

John


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

4 replies

Userlevel 7
Badge +14

Hi @john.gore 

Better approach would be to have the desired column with all the same value indicating the row is “unused” instead of blank cells, then once the row is “used” the value in the desired column would be updated.

Zap Steps

  1. Trigger: TBD
  2. Action: GSheets Lookup Row (e.g. Column C = “unused”)
  3. Action: GSheets Update Row (e.g. Column C = “used”)

Hi @john.gore 

Better approach would be to have the desired column with all the same value indicating the row is “unused” instead of blank cells, then once the row is “used” the value in the desired column would be updated.

Zap Steps

  1. Trigger: TBD
  2. Action: GSheets Lookup Row (e.g. Column C = “unused”)
  3. Action: GSheets Update Row (e.g. Column C = “used”)

@Troy Tessalone, thanks for such a quick reply. I’d read some of your responses other such requests - my apologies if this a well covered topic.

 

How would I set a row/cell to unused? I’m not entirely sure I follow what you mean by having the value as ‘unused’. Do you simply mean that rather than the cell being blank, it has a text value of ‘unused’?

Userlevel 7
Badge +14

@john.gore

Do you simply mean that rather than the cell being blank, it has a text value of ‘unused’?

Correct, give that a try. (can be any value really)

Userlevel 3
Badge +6

Hey @john.gore,

Just wanted to check in on this thread, was Troy’s recommendation able to help you out?

The issue with working with blank cells or fields is that they are ignored by zaps and most APIs. Switching to using text to represent an unused/empty cell will allow the zap to search for the row using that text. The search results from a Find Spreadsheet Row step will return the first row found, so that should fit your needs. :)