1

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?

  • @Rubén yeah I saw that thread and it's dated from 2011 and the solution there is with a third party that didn't seem to work for me – Happy Bird Aug 30 '18 at 18:19
  • I believe that this is not strictly a duplicate. This question is specific to Google Spreadsheet (and as such can use a Spreadsheet-specific solution), while the linked answer is generic to all Google Drive documents. – Vidar S. Ramdal Aug 31 '18 at 8:34
  • 1
    @VidarS.Ramdal It's a duplicate because the answer is the same. While this question is specific to Google Sheets., AFAIK there aren't specific solutions. i.e. onEdit simple/installable triggers and onChange installable triggers which are specific for spreadsheets, can't be used to log spreadsheet views from anonymous users. – Rubén Aug 31 '18 at 20:22
  • 1
    The onOpen trigger besides being available for spreadsheets it's also available for documents, presentations and forms (edit view) – Rubén Aug 31 '18 at 20:28
  • @Rubén I guess you're right, after how this question has evolved. – Vidar S. Ramdal Sep 3 '18 at 7:21
2

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.

| improve this answer | |
  • Or how about this: can the log be copied automatically to a different sheet in my drive folder, then wipe the log data on the original sheet that the anonymous user has access to? – Happy Bird Sep 1 '18 at 12:39
  • See revised answer. The script now logs to a separate spreadsheet document. – Vidar S. Ramdal Sep 2 '18 at 20:55
0

unfortunately, that's not possible, because anonymous users are not included in the log.

alternatives are - bitly statistics or custom script

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.