How to check who has viewed your shared Spreadsheet and at what time.

How to check who has viewed your shared Spreadsheet and at what time.

Question:

If you have shared a spreadsheet with many coworkers to view only so they have access to the information at any time, Then is there a way to see who has opened it when they are not making any changes?


Answer:

NO, you can't if you have shared your sheet in "view only" mode.

But YES, if you have shared it in "can edit" mode, then you can have this following script that will be executed whenever any user opens (access) it. And it will record the time (when sheet gets opened) in "Column A" and user's email Id in "Column B".

Note: This cannot be done if you share your spreadsheet in "view only", because then you are not allowing that user to edit your spreadsheet, so even this script will not have the permission to edit this spreadsheet.

Instead of sharing whole spreadsheet in "view only" mode, you can do the following:
1) Share your spreadsheet in "can edit" mode with your co-workers or friends.
2) Protect all the sheets, except one sheet and name it as "WhoHasAccessed" or whatever you want. So now by doing this, only one sheet can be edited by your co-workers or friends and you have protected all the other sheets.
3) Hide this "WhoHasAccessed" sheet. Click here if you don't know how to hide a Sheet. So by doing this, you will hide the data of this sheet. 
(But remember any of your co-worker and friend can easily view this hidden sheet and can view and edit data of this sheet. To avoid this, you can have a script which will immediately send you email with an attachment of this spreadsheet.)


Now, before explaining further, let us have a look at the following script with an example:



SCRIPT:

function onOpen() {
 WhoHasAccessed();
}

function WhoHasAccessed() {
  var userEmail = Session.getEffectiveUser();
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName("WhoHasAccessed");
  var range = sheet.getDataRange();
  var lastRow = range.getLastRow();
  var currentTime = Date();
  range.offset(lastRow, 0, 1, 1).setValue(currentTime);
  range.offset(lastRow, 1, 1, 1).setValue(userEmail);
}

The above script will record the time when sheet gets opened, in "Column A", and User's Email Id in "Column B".


Example:

Suppose you are "USER A" owner of a spreadsheet and you have shared it with "USER B" AND "USER C"

Now, if you shared this spreadsheet in 
"view only" mode with "USER A" and 
"can edit mode" with "USER B".
Then whenever "USER A" opens this spreadsheet, this script will not be executed successfully as "USER A" don't have the rights to edit this spreadsheet.
And whenever "USER B" opens this spreadsheet, this script will record the time and Email ID of "USER B".


Following is the screenshot of "WhoHasAccessed" Sheet:

I hope this script will help you to find out who has accessed your spreadsheet and at what time.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive