At Oppedahl Patent Law Firm LLC our work-from-home setup is a work in progress. Yes each person has a phone extension at home that works exactly like the phone extension that is on the person’s desk in the office (blog article). Yes each person has a VPN giving them access to all of the office resources (blog article). But how about some way that each work-at-home person can let the others in the office know of that person’s status? Is there a way that at a glance I can see who is “on duty” and who is “off duty” right now? Is there a way that I can see at a glance whether the best way to reach a person just now is by dialing their office telephone extension or by dialing their cell phone? We managed to work out a free-of-charge way to make this possible.
The way we did this is to set up a Google sheet (a cloud-based spreadsheet). It has a row for each person. The first column is the person’s name. The second column permits each person to select from a drop-down list to indicate the person’s current status. The third column tells the time of day that the person last updated their status.
One person with a Google user ID created the spreadsheet. That person gave write access to each of the other people through their Google user IDs. So each person can view the spreadsheet and each person can update his or her status as displayed in the spreadsheet.
The way that we do the drop-down list is by means of “data validation”. You click on a cell to select it, and then you go into “data” and “data validation” as in the screen shot at right. Each person is able to customize his or her “list of items” which are comma-separated. The person might list his or her office telephone extension or cell phone number for example.
After we used this spreadsheet for a day or so, we realized it would be helpful to be able to see at a glance how long ago the person updated his or her status. To make this happen, go into “tools” and “script editor”. You can see a script that executes every time anybody edits any cell in the sheet.
At line 2 the script asks the cloud for information about the sheet in which the script is running. At line 3 the script checks to make sure that it is running in the sheet that it is supposed to be running in (looking for a match with the name of the sheet). At line 4 we find out from the cloud which cell it is that just got edited. At line 5 we find out the column that the cell is in, and we compare the column number with the number “2”. (This is the column in which the drop-down lists appear.) If the answer is no, then the script ends. But if the answer is yes (because somebody made a different selection in the drop-down list) then the script continues. In line 6 it works out the coordinates of the next cell to the right of the cell that just got edited by doing a vertical offset of zero (meaning a cell in the same row) and by doing a horizontal offset of 1 (meaning the cell in the next column to the right). It stores this answer in a place called “nextCell”. In line 7 the script finds out the present date and time. In line 8 the script formats this date into the Mountain Time Zone and a format of AM and PM. In line 9 the script stores that information in the “nextCell”.
I hope you’ll find this helpful. If you get it to work in your office, I’d be delighted if you post a comment below.
The other thing that we did to help our people feel connected with each other was to give each work-at-home person a desk phone with a busy lamp field.
What solution do you use in your office to enable colleagues to know who is on duty just now and who is not? Please post a comment below.