Google Sheets – View History of Anonymous Users

google sheets

I have a Google Spreadsheet, which is accessible (only view) by anyone who has the link. I want to have an overview of every timestamp the document was opened by an not-logged-in (= anonymous) user. How can I do this?

Best Answer

I was hoping to answer your question with a Google Apps script. There is an onOpen event, but it fires only on users that have edit access to the spreadsheet. Since your users only have view access, a script won't work like that.

If you're willing to give anonymous users edit access (which will let anyone in the world who knows the URL edit), you could log all visits to your spreadsheet to a separate Access Log spreadsheet.:

  1. Create a new spreadsheet document, and create a sheet (tab) on it, let's call the sheet Access Log.
  2. Note the ID of the created spreadsheet. You'll find the ID in the browser's location bar: Spreadsheet ID in URL bar
  3. Open the spreadsheet you're going to make public
  4. Go to Tools → Script editor
  5. Paste the following script into the script editor window: function onOpen() { var sheet = SpreadsheetApp.openById("11evsXXXXXXXXXXXXXX8Xt3E6vI").getSheetByName("Access Log"); sheet.appendRow([new Date(), Session.getActiveUser().getEmail()]); } ... replacing 11evsXXXXXXXXXXXXXX8Xt3E6vIis the ID you copied in step 2
  6. Click the Run button, a dialog will open asking you to grant access to the script (you only have to do this once)
  7. Close the script editor window

From now on, anyone visiting your sheet will cause a line to be appended to the Access Log sheet.

I have setup an example spreadsheet to demonstrate. It does not allow anonymous users to edit, so it will not log anything, but feel free to copy it and use it as a basis for your own sheet.

The script explained:
The onOpen function name tells Google Sheet to run this script whenever the sheet is opened by someone. ...openById(...).getSheetByName("Access Log") gives a reference to the sheet named Access Log in the separate spreadsheet document. Finally, sheet.appendRow([...]) appends a new row to the log sheet, with a timestamp and the current user's email address (which in case of an anonymous user will be empty).

An alternative solution is to use a URL shortener with analytics, such as bit.ly. It will allow you to make a short link that you will distribute to your users. When your users visit the link, they will be redirected to your spreadsheet.
By logging in to bit.ly, you will be able to see simple click stats.

Now, obviously, this will only work if your users only use the short link when visiting your spreadsheet. You won't be able to count anyone going directly to your spreadsheet by its long URL.

Related Topic