Best answer

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

  • 5 September 2022
  • 3 replies
  • 236 views

Userlevel 1
Badge

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 :

  1. Trigger : Webhook GET from the online check-in form.
  2. Action : Lookup Ghseet row after the bookingID
  3. 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 1

Guest 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

icon

Best answer by Danvers 5 September 2022, 16:51

View original

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

3 replies

Userlevel 7
Badge +12

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 together easily. 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 spreadsheet row 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:

  1. Trigger : Webhook GET from the online check-in form.
  2. Action : Lookup Ghseet row after the bookingID
  3. 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”

 

Then add these steps:

  1. Google Sheets: Find Spreadsheet Row Search for a row that contains the booking date in the date column and the 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). 
  2. 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. 
  3. Google Sheets - Find Spreadsheet Row Search for a row that contains the booking date in the date column and the 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. 
  4. Filter: User the field zap_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?

Userlevel 1
Badge

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.

Userlevel 1
Badge

Hi @Danvers,

Sorry it took me a bit longer than expected to set up the Zapp and come back to give you my feedback.

Your solution worked great but i had to make it a little more complex to meet my needs and now it’s all working !

Thanks again for the help !