This tutorial explains how to automatically record login and logout time for PC in Google Docs Excel. Using this tutorial, whenever someone will login to PC, a row will be added to Google docs excel. Similar, at the time of logoff, another row will be added to Google Docs Excel. All this works automatically once the initial setup has been done. This setup can be done on multiple PCs as well, and data for all of them can be logged to same Google Docs excel, so that login and logoff times for all PCs are consolidated in one centralized excel.
This automatically logging of login and logoff time in Excel will be useful in many scenarios. An employer can easily maintain a record of login and logout time and can access that Google Docs Excel anytime to see when his employees login and logout from their individual PCs.
Let’s get started.
How to Record PC Login and Logout Time in Google Docs Excel:
This tutorial is basically a three step tutorial. Steps one and two have already been covered in our previous tutorials, while Step 3 will be explained in detail here. The basic premise of this tutorial will be that an email will be sent whenever someone logs in or logoff from PC. Then, using IFTTT, we will capture information from those emails and record them to Google docs Excel file.
Before you start setting things up, keep following things in mind:
- Process to send emails at login will work for all versions of Windows. However, process for logout will work only for those versions, which has Group Policy Editor. Details have been given in the tutorial linked below.
- The tutorials mentioned below use two different programs to send email. However, you can use same program in both (SendEmail is my preferred choice).
- The tutorials send fixed email message for login and logout. If you are trying to implement this across multiple PCs, or, if multiple users login to same PC, then it would be helpful to get username as well for which the entry is done. For that, you can add parameter %USERNAME% in scripts for login and logout. You can add username to either subject or body of the email.
- In the tutorials below, we are sending pretty long messages at login and logoff. However, as we will need IFTTT to get information from those emails and there is very limited information that we can extract, it will make sense to setup emails as below:
- Setup a new Gmail address to send all such emails. Configure that as “From” email address in scripts below. In your To email address, the one which will get all the emails, setup a Filter to Label all messages from that email address as “Login_Logout”. This will ensure that IFTTT will know which emails it has to act on.
- For Login emails, the subject should be just “Login”. Body should only have “%USERNAME%” (without quotes).
- Similarly, for Logoff emails, set subject as “Logoff” and body of email as “%USERNAME%”.
Now, proceed with steps listed below to start setting everything up to maintain Login and Logoff time of PC in Google Docs Excel.
Step 1: First of all, setup an email notification system so that an email is sent whenever someone logs in to PC. For doing this, refer to detailed steps here: How To Get An Email Whenever Someone Logs Into Windows. This will send email whenever someone logs in (it could be when someone restarts PC, or starts PC afresh, or just Logs in to already switched on PC).
Step 2: Next step is to setup similar email alert system whenever someone logs off from PC. You can see detailed steps for that here: How To Send An Email Whenever Someone Log Off From PC . This will send email whenever someone logoff, or shutdown, or restart PC.
Make sure you test that Steps 1 and 2 are working fine, before you proceed on to Step 3.
Step 3: This is the main step which will actually do all the Login and Logoff entries in Google Docs Excel. This will use IFTTT. Make sure you have a free account with IFTTT and have authorized your email address with that (the email address on which you will get login and logoff emails; not the one from which email will be sent). Here are the step you will follow:
- Login to your IFTTT account and click on “Create” at top of the page. Then, click on “This” and select “Gmail” as the Trigger channel. In the next step, click on “New Email Labeled” and then specify label as “Login_Logout” (this is the label that I mentioned above that you should configure in your “To” email address, so that all emails coming from “From” email address should be labeled as “Login_Logout”. If you don’t want to label all emails, you can modify filter in Gmail to label only those email which have “Login” or “Logoff” text in them and come from “from” email address). Do remember that Labels are case sensitive, so use same case of Labels here that you use in Gmail. Click on “Create Trigger”. We are done with the first part of our recipe.
- Now we get to second part. Click on “That”. Then select Action Channel as “Google Drive”. If you have not already IFTTT to access your Google Drive account, you will be asked to do that now. After that, click on “Choose Row To A Spreadsheet”.
- In the Next section, do settings as below:
Give any name for the spreadsheet (IFTTT will create this spreadsheet, if it does not already exists in your Google Drive). In the Formatted Row box, make following entry: {{Subject}} ||| {{BodyPlain}} ||| {{ReceivedAt}}This basically tells IFTTT to record the following information in our Google Docs Excel in three separate cells:- Subject: In our case, this is either “Login” Or “Logoff”. So, the first column of our Excel will show whether the entry is for Login or Logoff.
- BodyPlain: This will take content from Body of email and add that to second cell. In our case, Body is simply USERNAME of the user who logged in or logged off from PC. So, second column will contain username for which Login or Logout time is being logged.
- ReceivedAt: This is the time at which email was received. This will essentially be Login or Logoff Time. So, third column will have actual date and time at which the user logged in to PC, or Logged Off.
Drive Folder Path is where IFTTT will create the Google Spreadsheet. You can specify any path here, or leave the default path in place.
- Then click on “Create Action”. And finally, click on “Create Recipe”.
That completes the tutorial. Now, if you did everything correctly, then IFTTT should start making entries in Google Docs Excel whenever someone Login or Logoff from PC. No notification will be given to the user who is logging in or logging out from PC and the entire setup will run in a completely automated manner.
You can see data that is recorded in the excel in first screenshot of this tutorial (I added column headers myself to the spreadsheet). Pretty neat, right?
I know it’s a long tutorial, involving lot of moving parts. So, if things don’t work out for you, you need to validate that each individual sub-part is working correctly. Start by validating Step 1 that whether you get an email whenever someone logs in to PC. Then move on to Step 2, and so on.
Share your feedback about this tutorial in comments below.
Also see: How to Get Email When Someone Connects To Your Wi-Fi Network