Hide author email address from Google Sheets API

Question

Google Sheets allows publishing any spreadsheet to web. Doing so exposes the data contained in the published spreadsheet for access via variety of APIs, such as Google Sheets API.

This is all fined and good, except for one thing. Accessing the a published worksheet via API, returns email addresses of the users who have contributed to that worksheet.

For example a request like this:

https://spreadsheets.google.com/feeds/cells/[key]/[sheetId]/public/basic?alt=json

Among other information, will also return this:

"author": [ {
  "name": {
    "$t": "***"
  },
  "email": {
    "$t": "***@***.com"
  }
} ],

The *** in the above snippet are real Google account names and emails.

Is there a way, either via document properties or via generic Google Account properties, to disable publishing of your email address via public APIs?

Answer 1

There is no way to hide that information in that spreadsheet. However, here is a workarround:

copy the sheet contents to another spreadsheet, and publish that other spreadsheet. Copying can be done several ways:

1. You can make a manual copy, which will preserve everything including images, but is harder to maintain in sync.
   
   
2. use the =ImportRange formula to import values automatically into the copy. that handles changes but doesnt handle all formatting and wont work if images change (unless they are placed using =image formula).

=IMPORTRANGE("1z5afM6Wn-8RKsdUpTPicU0d81YCWX4bqzgKIna1uep0","Instructions!A1:E") for example will copy all content in columns A to E into your sheet. In the published sheet make that "File:Spreadsheet settings" is set to "Recalculation: on change and every minute". A more detailed example of using importRange is in this blogpost I made, see the part about importRange to copy the "instructions" sheet to other sheets: http://zigmandel.blogspot.com/2015/09/how-i-crowd-translated-my-product-tour.html

3. have an apps script with a trigger to copy the entire sheets into the published spreadsheet. that will preserve everything but is a little more code to delete the old sheets and copy the new ones over.

And of course, the email you use for making those copies (manually or by script) needs be one you don't mind publishing.