6

I find a lot of -especially new- users on the site are having difficulties on sharing a demo sheet so they can be easily helped if any at all.

So, what is the right way to share a demo test sheet?

3
  • 1
    I like this a lot! It was very helpful with modifying my question. The only quibble I have is that this question's title may be taken to mean "How to share it with other people in general" instead of "how to share a sheet to help people answer a question more quickly/fully on WebApps."
    – aschultz
    Jan 31, 2020 at 18:10
  • @aschultz Well... Hmmm. I believe it will help other people as well. We are not the only site helping out. And anyway I wanted it to be brief and easy to remember. Any other suggestions? Jan 31, 2020 at 18:16
  • Related: Share Google Sheets Anonymously Mar 20, 2023 at 16:58

2 Answers 2

7
+50

Other than the privacy issues mentioned in the previous answer, the biggest weakness of sharing spreadsheets is that they can be un-shared any time. Any question depending solely or partly on external links isn't worthy of any stackexchange site including webapps. There are other better alternatives to sharing a spreadsheet. In my opinion, using tables is the best option. This answer deals with neat ways to create such tables.

All you need to create a table is a simple formula:

  • Formula:

    =ARRAYFORMULA("|"&TO_TEXT(A1:C8))
    

    If you want to share A1:C8, use the above formula in a another tab. To use formula, the second row A2:C2 should only contain dashes -- in all the cells, while A1:C1 acts as a header.

    • If A1:C7 looked like this:
    Item 1  Item 2  Item 3        
    food    chips   
    drinks  beer    coffee
    food    chips   
    food    chips   
    drinks  beer    coffee
    drinks  beer    coffee
    
    • Add a empty row with -- under the header row:
    Item 1  Item 2  Item 3
    --      --      --
    food    chips   
    drinks  beer    coffee
    food    chips   
    food    chips   
    drinks  beer    coffee
    drinks  beer    coffee
    
    • In a empty range or a another sheet, use the formula =ARRAYFORMULA("|"&TO_TEXT(A1:C8)), you'll get:
    |Item 1 |Item 2 |Item 3
    |--     |--     |--
    |food   |chips  |
    |drinks |beer   |coffee
    |food   |chips  |
    |food   |chips  |
    |drinks |beer   |coffee
    |drinks |beer   |coffee
    
    • Now, copy and paste it into your question. Make sure to paste it after two empty lines. And add two more empty lines at the bottom(Even if you think it works as seen from the preview, it won't show up in your real post without those empty lines). It'll automatically show as a table:
    Item 1 Item 2 Item 3
    food chips
    drinks beer coffee
    food chips
    food chips
    drinks beer coffee
    drinks beer coffee
  • Script: If formulas are hard, you can use this script by Cooper. It comes with a user interface with various customization options:

    • Go to Extensions > Apps Script
    • Copy paste the three files into the editor
      • redact.gs
      • redacttable.html
      • tableMarkdown.html
      • You can create new files by clicking the + button on the left pane of the script editor. Note that .gs file is a script file and .html files are html files. When creating a new file, make sure it has a appropriate extension.
      • Save all files by clicking save button
    • Go back to your spreadsheet and refresh the page
    • You'll get a menu item named Redactable table. Click it and follow the instructions as shown: (GIF from https://stackoverflow.com/a/66772209 by Cooper)
  • Addons: Addons like this helps in creating markdown tables easily.

  • Formatting guide: https://meta.stackexchange.com/questions/356997/

3
  • 1
    If any one needs any help with code mods feel free to ask. There are some things That just can't be displayed in the mark up tables that can be in a spreadsheet.
    – Cooper
    Oct 12, 2022 at 19:55
  • Dumb question: where should I use the formula printed above ("=ARRAYFORMULA("|"&TO_TEXT(A1:C8))")? I tried the conditional formatting in Google Sheets but it didn't seem to have any effect. I'm tempted to use Cooper's script, but honestly I'd like to learn how to use formula as printed above. Need this to correctly format a different StackExchange question...! Sep 10, 2023 at 19:33
  • @ReverendSpeed As written In a empty range or a another sheet. Say in A10 of the same sheet.
    – TheMaster
    Sep 11, 2023 at 10:55
3

The correct way to publicly share a Google test sheet


You have 2 options:

  1. Share an already existing sheet OR
  2. Create a brand new one under your existing or a new account.

Please follow the steps:

Option 1
Skip to "Prepare your sheet"

Option 2

  • Creating a new sheet:

    • A brand new sheet can be created by clicking here or here
  • Prepare your sheet.

    Before sharing your sheet make sure you fill in all the information needed. That is:

    • Include only but all necessary demo data excluding any sensitive data.
    • Not too little, not too much. Do not overload the sheet. No need to have 500 rows and 50 columns. As well as not just 2 rows and 1 column. Just as much as needed.
    • Place in the spreadsheet -as well as in the question- the essential information for others to fully understand your issue.
    • Give an example of your expected results.
    • Include your efforts thus far. (It shows good will and determination)
  • Share your spreadsheet

    Correct way to publicly share a Google test sheet

  • Let us know:

    After you shared your sheet, do let us know.

    • Check your answer for possible mistakes and add additional clarifications if any needed.
    • Edit/Update your answer including the copied link to the spreadsheet.
    • Alert the person who asked for it by leaving a comment for them under your question (@name some text).
    • Do NOT just add the link in the comment
  • Privacy Note:

    Sharing your spreadsheet exposes your email address. If you'd like your email address to be "more private", you can try the Blank sheet maker created by members of Google products experts program shared by @Mattking. You would still need to trust and share your email with those members, but your email is not public to everyone.
    Alternatively, you can create a dummy Google account for sharing sheets, forms, documents, etc. to the stackexchange sites.

Related

12
  • 2
    By sharing a spreadsheet, a questioner is also publicly exposing their email address. I suggest that you add a warning to your answer along the lines that "You should be aware that sharing your spreadsheet will unavoidably expose your email address.""
    – Tedinoz
    Mar 17, 2020 at 13:26
  • 1
    I do understand your comment, and it was certainly news to me when it was brought to my attention. This post on StackOverflow Meta has the instructions- the one's regarding using the api. Let me know how you get on.
    – Tedinoz
    Mar 22, 2020 at 19:48
  • 1
    The problem with sharing it editable is that anyone can trash the sheet. Surely it's better just to share it read-only and let people make their own copies to work on?
    – PhilHibbs
    Apr 16, 2021 at 20:48
  • 1
    @marikamitsos I didn't mean "deleted", I meant trashed in the sense of "wrecked, destroyed, made useless".
    – PhilHibbs
    Apr 18, 2021 at 10:47
  • 1
    Most certainly neither one's email nor one's real name. Certainly true. Email address is public, if sheet is public and will be shared and may be easily linked with SO account by bots
    – TheMaster
    Oct 22, 2021 at 8:55

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.