Desk reservation form

@mobukuro

I built something similar to this, for assigning one of six IDs to temporary workers. You'll need some helper columns.

I set up the sheet with the temp IDs at the top. This is where their status of available or taken is shown. The TEMP 1, TEMP 2, etc would be your desk locations.

Main columns were:

Temp ID, Status, Temp Employee Name, Start Date, End Date.

Helper columns:

Temp ID Available, Total, Blank End Date, Known End Date, Name in Use

On rows below, users would select a Temp ID that was listed as available at the top. They'd enter their name, and a start date, and if known, an end date.

Helper formulas:

Temp ID Available: =COUNTIFS([Temp ID]:[Temp ID], [Temp ID]@row, Total:Total, >0)

Blank End Date: =COUNTIF([End Date]@row, "")

Known End Date: =IF([End Date]@row > TODAY(-1), 1, 0)

Total: =IF(SUM([Blank End Date]@row + [Known End Date]@row) > 0, 1, 0)

Name In Use: =IF(Total@row > 0, [Temp Employee Name]@row, "")

Now, in the Status column for the rows at the top (where you list the desk locations):

=IF([Temp ID Available]@row > 0, "Temp ID In Use", "Available")

What this is doing is checking to see if any rows with that Temp ID have a blank end date or a known end date that is greater than or equal to today, and if so, it marks that Temp ID as In Use. I used conditional formatting to mark those rows red.

@mobukuro Unfortunately not. However, there is a workaround. You can create a Dashboard/Portal, with an embedded report showing the desk statuses so they can see what's available, and an embedded form for making their reservation. I added an extra helper column on my sheet, a checkbox called "Report", and only checked it for the Temp ID rows. I created a Report that filtered by the 'Report' box being checked. You could probably find a way to display the current reserved user's start and end date for each desk.

Instead of launching the form to enter a new desk reservation, they launch the "Desk Reservation Dashboard" to see what's available and make their reservation.

You don't have to manually share the dashboard access, just the "published" link.

On the dashboard, under the File menu, select "Publish". Click the slider to turn on the Read Only - Full version of the dashboard.

Select either of the top two Access Control options. You can set it to allow anyone with the link to view the dashboard, or only users in your org. The publish URL is shown below in the same popup box:

This works because when your Form is open to anyone in your company, that access passes through on the dashboard. The user can enter new records into the form without a problem. (I just tested this with my unlicensed personal account that's outside my org. No permissions at all on the underlying sheet or the dashboard, the form is open to anyone, and I could enter a new record using the form embedded in the Read Only - Full published dashboard.)