I have a scenario to transfer information from one database to personalized google sheets based on an identifier. I am using “add a row” and what happens is that the information is transferred properly, but being a new row the last column skips the data validation set for that column (screenshot).
So now I want to look at the first available blank row, and then use the module “update a row”. But I am struggling to find a proper way to filter the module “Search a row” and retrieve the first available blank row number.
Do you have any tip?
Hello @Michelangelo_Romano,
What do you mean data validation is skipped? You try to enter data into that cell but it ends up blank?
Does what you’re trying to put into that cell pass the validation if you were entering it manually?
If I use Add a Row and one of the cells is a dropdown, it selects it correctly as long as I enter the text correctly.
Even if I don’t enter it correctly it still gets in the cell, but reports an error that that I must select an item from the list.
If you want to find the last used row there are a few ways I can think of.
- Use the Google Sheets Get Range Values module. Input a range you know will be outside the existing used range, but the module will only return those with data. If you have 43 rows of data it will return 43 bundles. That number 43 is the last used row so if you want to update a row, use 44 to use the next blank row.
- If you Add a Row, that module will return the row number data was added to. You can then use that row number in the update a row module.
- Use Google Sheets Make an API Call to query a single column and determine the last used row similar to option 1. Please see this post Add Column in Google Sheets module instead of Add Row - #4 by Donald_Mitchell
3 Likes
Hello @Donald_Mitchell , I am sending information to column A,B,C of the other file, while mapping for column D is empty because I still don’t have this information. So I can’t proactively select a value from the dropdown, this needs to be done by another person who has access to the file.
I don’t know if clear enough, anyway I am going to test your proposed solutions to find the first empty row, many thanks!
I think I missed something here.
Are you trying to update Column D when you add a row and data to Columns A, B and C?
Or does Column D need to be added at another time?
2 Likes
Looks like you need to update column A-C, without overriding the contents of D. Is that right? So basically update range values.
Alternatively you can try inserting the special variable in the D column: {{ignore}} or {{omit}}
4 Likes
Thank you @samliew worked perfectly 
1 Like
No problem, glad I could help!
1. If you have a new question in the future, please start a new thread. This makes it easier for others with the same problem to search for the answers to specific questions, and you are more likely to receive help since newer questions are monitored closely.
2. The Make Community guidelines encourages users to try to mark helpful replies as solutions to help keep the Community organized.
This marks the topic as solved, so that:
others can save time when catching up with the latest activity here, and
- allows others to quickly jump to the solution if they come across the same problem
To do this, simply click the checkbox at the bottom of the post that answers your question:
3. Don’t forget to like and bookmark this topic so you can get back to it easily in future!
2 Likes
