1

I have a Google Spreadsheet with around a dozen sheets in it. I'd like to publish it to the web, but in a way so that I can give a dozen people each a different URL, each of which takes them each to one of the published sheets within the webpage, but without access to the other sheets. At the moment, I only get a single URL, and there's a navigation bar that lets you switch between sheets, so that doesn't work for my purpose.

Example:

Person A gets a link to the published webpage for sheet A within the spreadsheet, and when they go there, they have no way of getting to the webpage for sheet B, C, D, etc.

Person B gets a link to the published webpage for sheet B within the spreadsheet, and when they go there, they have no way of getting to the webpage for sheet A, C, D, etc.

And so on.

Is this possible out of the box, or with a little jiggering? Thank you!

0

3 Answers 3

1

The Google Sheets built-in publish to the web doesn't include a feature that meets your requirements.

One option is to make copies of the spreadsheet or split the sheets across multiple spreadsheets to have a different spreadsheet for each sheet to be published. You might make additional steps to fulfill your requirements completely.

If you opt to have only one sheet by spreadsheet and they have dependent formulas, you might use IMPORTRANGE to get the required value or array of values.

There are many other options, but each depends on decisions based on the circumstances of each user.

Related

1

Quick Read

Google Sheets "Publish to the web" can generate direct URLs without inter-sheet navigation but they can be manipulated to expose all published sheets and also change file formats. In cases where the entire document is published, the underlying formulas are readily exposed as well.

  1. The only way to restrict access to a single published sheet per document using native functionality is to publish no more than a single sheet per document.
  2. To keep underlying formulas private, even in the case of a single-sheet document, "Published content & settings" must not be set to "Entire Document."

Not-So-Quick Read

Sheets: Publish to the web

A.  Configuration

File > Share > Share to web
The Link section allows users to configure the link properties and publishing options:

  1. Whether the generated URL points to the entire document, or to a specific sheet.
  2. The preferred published file format. Such as, HTML, PDF, CSV, etc.
  3. The "Published content & settings" section specifies if the entire document should be published, or only specific sheets. It also contains an option to "Automatically republish when changes are made."

B.  Generated URLs

"Publish to the web" URLs are generated by combining a base path, a document ID, and additional parameters.

  1. The base path is the same for any published document. For example:
    https://docs.google.com/spreadsheets/d/e/
    
  2. The document ID is always the same for all sheets within the document. For example:
    7PCAX-1vSM3hPEpe747klvZ_T8hMTqkj_jaKq6kLzZXs3CN38RljE7kCfVpnwkxcG54M0R38C4AW6iJ-69mcli
    
  3. The parameters vary based on the configuration options. Some examples:
    # Link to entire doc, HTML
    # Inter-sheet navigation displayed
    /pubhtml
    
    # Link to entire doc, PDF
    /pub?output=pdf
    
    # Link to specific sheet, HTML
    # No inter-sheet navigation displayed 
    /pubhtml?gid=1466251838&single=true
    

C.  Security & Clearing the Fog

Users should think about the generated URLs separately from the publishing settings .

  1. The configuration choices under "Published content & settings" solely determine whether some or all of your individual sheets are being published.
  2. Any published sheet can be accessed by any user in HTML format, regardless of the link generated, by manipulating the link they are provided.
  3. If an individual sheet's gid is shared, it can be accessed in any format except XLSX and ODS. The left-most sheet always has gid=0 so when published its gid needn't even be exposed to a user.
  4. If the entire document is published, regardless of the URL generated or the file type specified, the entire document can be easily accessed in HTML, PDF, XLSX, and ODS formats, and the latter two spreadsheet formats will expose the underlying formulas.
  5. CSV and TSV formats will always default to a single sheet. Either the specified gid or the left-most sheet if the entire document is published and no gid is specified.

Generated & Manipulated Links

  1. Generated Links Table
    Summarizes 3 "Publish to the web links generated by Sheets.
  2. Manipulated Links Table
    Summarizes the same 3 links after being manipulated by A user.

Generated Links

Published          Generated Link Parameters                                                                   
1 All Sheets All Sheets, PDF /pub?output=pdf
2 All Sheets Sheet2, PDF /pub?gid=1466251838&single=true&output=pdf
3 Sheet1, Sheet3 Sheet1, CSV /pub?gid=0&single=true&output=csv

Manipulated Links

Original Link        Manipulated Link           New Parameters                
1 All Sheets, PDF All Sheets, HTML /pubhtml
2 Sheet2, PDF All Sheets, XLSX /pub?output=xlsx
3 Sheet3, CSV Sheet1, Sheet3, HTML /pubhtml
0

By the sound of it, you have tried to publish your spreadsheet and have encountered these problems. Admittedly the documentation slips over this aspect, though a little experimentation reveals the options.

You can "Publish to the Web" as many times as you like.

On the popup (under Link) there is a dropdown where you select which sheet(s) to publish - just select the specific sheet you want to share. Then copy the URL. Do the same for each of the sheets you want to share. Each URL is unique and none will allow a user to see the others sheets.

1
  • Thanks, but unfortunately, this does not fulfill the stated requirements. This yields a URL ending in "&single=true". If the user deletes that part of the URL, they get the same old sheet with tabs where they can see all the other tabs. If there was a way to obscure that, it might be acceptable, but as is, it's much too easy to gain access to the other sheets.
    – Brionius
    Commented Nov 30, 2021 at 0:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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