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.
Our firm has a piece of software called Cisco Jabber that allows us to forward our office phones to our cell phones. It also acts as an instant message application, like Yahoo!Messenger, etc. It also allows two connected users to screenshare. Only drawback is some of the attorneys felt it was too intrusive and therefore, never log into Jabber so unable to see their status. But those of us that use it regularly, we can see when people are Available, Away, In a Meeting, busy, etc.
Thank you. I’m curious, does this Cisco Jabber cost money to set up? Does it cost money to use? Or maybe it’s a thing that you can only use with a telephone system that is from that company? What I’m getting at is, do you have to be paying a monthly fee to Cisco to be able to use this Cisco Jabber? Oh and if your phone system is from Cisco, I wonder if your hold music is Opus Number One?
I abandoned desk phones for blue tooth ear inserts for my cell phone, about a year ago. I find the BlueTooth devices comfortable and I have several of the same model, so no matter how many I misplace, I always have some charged and ready to go. My “office” has extended Wifi and Wifi calling, so I am free to room in and around the outside of my “office” without fear of missing calls. (For example, I often break mid day to go turn over some dirt or other chore out in my garden, outside my house, because my “office” is in my house.)
As to the the greater issue, I think screen sharing may be useful, but a bit intrusive to know that someone is at their work station. And I think the google sheets is deficient because it requires user keyboard action. I would rather have a passive system to let co-workers know whether their colleague was on duty or off duty. One possibility might be a blue tooth based proximity detector connecting PC wearable, and corresponding desktop widget showing in real time whether each coworker’s wearable was within bluetooth range of their workstation PC.