How do I set up the conditional logic to assign lockers with bookings within Google Sheets?

Question

Hi all, 👋I’m Raoni, an Airbnb host, and the Zapier Community already helped me a lot with my automations ! 😊 Today i’m back because i need to build a zap that will allocate a locker (for luggage deposit) to my guests. As usual, sorry for my english... Context :I have 2 lockers with code lock.Before their stay, my guests fill an online check-in form and indicate if they’ll need luggage deposit on arrival, departure or both.Guests might fill the online check-in form several times.Guests might fill the online check-in form in non-chronological order (IE : guest 3 fills the form before guest 1, and guest 1 does it before guest 2) What is my workflow :When new booking is made a zap fills my Gsheet data base with all the booking informations (booking number, name, dates, phone, etc...) When guests fill the online check-in form another zap updates the Gsheet data base with the informations provided by the guests (beds needed, arrival time, departure time and if they’ll need luggage deposit).A third zap creates a google calendar event for each booking with all the details that could help the cleaning lady or myself for welcoming the guests. All of this works great. Objective : The zap will grab the webhook from the online form.Zap will allocate the guest a locker.Let call it “Locker 1”.Zap will then allocate the other locker to next guest on the planning.Let call it obviously “locker 2”. Where am I ?To allocate the lockers I choosed to modify the second zap which is trigerred by the online check-in form for 2 reasons :1. That’s where i get the info if the guest will need the locker.2. I have bookings until next March but the guests usually fill the online check-in form 1 week before the stay.That means the zap will alsmost immediately take effect.If it was triggered by the new bookings, i would have to update manually all the previous bookings. So, for now the zap works like this: The steps already in place :Trigger : Webhook GET from the online check-in form.	Action : Lookup Ghseet row after the bookingID	Action : Update Gsheet row - Zap fills the guest row from my Gsheet booking data base with the informations provided bt guest in the form.The steps i was thinking to add : (+creation of new Gsheet database called “Luggage deposit”)      4. Filter - Zap only continues if field “luggage deposit” on arrival OR departure is “YES”      5. Action : Lookup Gsheet row after the bookingID      6. FILTER - Only continue if booking ID doesn’t exists in “Luggage deposit” data base.      7. ACTION - Create new Gsheet row with cell 1 = booking ID cell 2 = Locker 1 (that action will allocate the locker 1 to the guest booking ID) From now i’m stuck… I guess i could find a way so the zap allocates locker 1 first time it runs and locker 2 the next time but it won’t work.If guest 2 fills the form before guest 1 he will have locker 1.Then guest 1 fills the form and gets locker 2.Then guest 3 fills the form and gets locker 1Guest 2 and 3 will have the same locker (locker 1). If they arrive and leave the same day that’s a problem.I also thought, i could trigger the zap from the Gcalendar events.5 days before the stay, it triggers a new zap.Zap creates Gsheet row in the “luggage data base” alternatively with locker 1 or 2. That way i’m sure the lockers are given in chronological order no matter if the guest asked for luggage deposit or when he filled the form. Questions :What would be the best way to alternatively insert 1 or 2 in the Gsheet cell ? (lookup spreadsheet row above ?)Do you think of a better solution than using the Gcalendar trigger ?Any great idea on how dealing with it ? That’s all falks, thank you for reading me and sorry for the long post but the more informations the better i guess.Let me know if there are missing or unclear informations. Have a great day 🤗 Raoni

Danvers · Accepted Answer

Hi @Raoni!I’m really glad that we’ve been able to help you in the Community! You’ve got some great ideas and this is a really neat set up for your Zaps. Before I offer an idea with Google sheets, I’m going to suggest thinking about using a tool that’s not Google Sheets. If you use a database tool like Airtable, you can link information togethereasily. For example, you can have a database of properties, a database of customers (with all of their contact information/needs/etc), a database of the luggage lockers, so when you get a booking form or other information you’re creating a link between the different places.OK, that said, let’s see if we can work this out in Google Sheets...What would be the best way to alternatively insert 1 or 2 in the Gsheet cell ? (lookup spreadsheetrow above ?)For this, I would create a new sheet for the luggage. Have a column for date, a column for the locker number and column for the booking reference. Then after these steps:Trigger : Webhook GET from the online check-in form.Action : Lookup Ghseet row after the bookingIDAction : Update Gsheet row - Zap fills the guest row from my Gsheet booking data base with the informations provided btguest in the form.The steps i was thinking to add : (+creation of new Gsheet database called “Luggage deposit”)   4. Filter - Zap only continues if field “luggage deposit” on arrival OR departure is “YES”Then add these steps:Google Sheets: Find Spreadsheet Row Search for a row that contains the booking date in the date columnandthe number 1 in the locker column. Tick the box that says ‘Create Google Sheets Spreadsheet Row if it doesn’t exist yet?’ If a locker hasn’t yet been assigned for that day, the action will create a new row with the date, the locker number 1 and the booking ID (taken from your previous GSheet lookup step).Filter: Use the field _zap_data_was_found and set the filter to only continue with the Zap if it’s true. The _zap_data_was_found field says true if the search was successful and false if nothing was found. That means that the rest of the Zap will only run if locker 1 is already assigned on that day.Google Sheets - Find Spreadsheet Row Search for a row that contains the booking date in the date columnandthe number 2 in the locker column. Tick the box that says ‘Create Google Sheets Spreadsheet Row if it doesn’t exist yet?’ If locker 1 is already assigned, it will create a new row to assign locker 2.Filter: User the fieldzap_data_was_found again. Set the filter so the Zap continues if zap_data_was_found is true. This will stop the Zap if a locker was successfully assigned. If a locker couldn’t be assigned because both one and 2 are already taken on that date, then the Zap will continue.You can then add any step you like on the end to alert you that someone has requested a storage locker, but none are available.Do that sound like it would work in the way that you need it to?

Raoni · Answer

Hi @Danvers,Thank you for you help !Never tought about Airtable. I’ll give it a try one day but for now i’ll stick with the Gsheet database as there is allways a learning time with new softwares and i need this to be working quickly.Your method sounds very promising but i need to put it in application to see if it perfecly fit my needs.I’ll give you a feedback as soon as it’s done.Thanks again.

Sign up

Use your Zapier credentials

Log in to the Community

Use your Zapier credentials

Scanning file for viruses.

This file cannot be downloaded