The publish on web feature of google spreadsheet makes the whole spreadsheet available on web for anyone to access. Sometimes it is required to hide a few rows that are not really need to be seen by public.
One way is to manually copy the data from one spreadsheet to another and then share it. However this approach will require you to maintain all changes at two different places.
In this simple tutorial we are going to show how you can selectively share a row of a sheet and without maintaining two different sheets.
For the ease of this tutorial we have used small data however this can be easily done for any large spreadsheet.
The Approach At High Level
To share the data with public we have created two sheets and a visibility column in main spreadsheet
The “Main” Sheet
The “Main” sheet will contain all the data that you want to manage.
The “Public” Sheet
This sheet is a simple copy of data using smart spreadsheet formula based on your sharing criteria.
Visibility Column
The “Main” sheet will also contain an additional column named “Visibility” (in addition to all your useful columns). This column will decide the visibility of a row. You can set the value of this column to “Public” for each row you want to share. If the value is empty or not set then the row will not be shared.
Selectively Copy Rows In Public Sheet Using Formula
We have created a simple formula to copy data from one sheet to another sheet. This formula is going to simply look at the visibility column value and if its value is “Public” for that row the data will be copied in public sheet otherwise the cell will be empty.
Below are simple steps to control show and hide of any rows from your public google spreadsheet.
Steps
- Create Main SheetCreate a Spreadsheet with a sheet named “Main”
- Add Visibility ColumnAdd a column named “Visibility” in the “Main” sheet. (along with all other columns of your data)
- Add Public SheetAdd Another sheet named “Public” in same spreadsheet. And copy the cell values of first/header row manually into first row of this sheet.
- Copy FormulaCopy the formula in all the rows of public sheet by dragging it
The Formula To Copy Rows
This formula is using a simple IF condition to copy a row from Main sheet to the Public sheet.
=IF((Main!$C2 = "Public"), Main!A2, "")
This formula is based on the spreadsheet we are showing in the diagram. Your spreadsheet may have less or more columns. Make sure you replace the corresponding column in your formula. For example if the Visibility column is at “Z” our formula will look like this
=IF((Main!$Z2 = "Public"), Main!A2, "")
How To Copy Formula To Work For Whole Sheet
Manually copying the same formula in all cells will not work since it needs to be changed for each cell. Here is the Easy way to copy the formula in the whole sheet. This technique will take care of generating the formula for all cells smartly for you.
- First copy this formula to first A2 cell in your sheet.
=IF((Main!$C2 = "Public"), Main!A2, "")
Now copy the formula to all the columns and rows of data you want to display on the publicly shared page.
Using the Mouse
- Click the cell whose formula you need to copy
- Take your mouse to the bottom right corner of the cell.
- A dark square mouse pointer will show up in the bottom right corner of the cell
- Click and drag mouse here, dragging down the column (or right across the row)
- Stop at the last cell you wish to fill
Using the Keyboard
- Move the cursor to the cell whose formula you want to copy
- Press SHIFT key
- While holding, press down repeatedly to select the range of cells you want to fill
- When you reach the bottom, release SHIFT then press CTRL + D (CMD + D on Mac) or (Use CTRL + R if you’re filling to the right)
- Publish The Public SheetNow you can publish the “Public” sheet on web using following steps.
- Go to File – “Publish to the web…”
- Select the Sheet “Public” from sheets to publish.
- Select the checkbox “Automatically republish when changes are made”
- Hit “Start Publishing” button
You can see the web url of you published sheet something like this
Lets Test Our Web Page.
I am a strong believer of test driven development, and I always try to make sure the setup is working fine. This is really simple,
Showing A Row
- Open the web url in your favorite browser and see what rows are visibile.
- Now go back to your Main sheet in the spreadsheet and set Visibility column for one of the rows to public.
- RePublish the spreadsheet
- Refresh the browser and verify the newly set public row is visible on your web page.
Hiding A Row
- Open the web url in your favorite browser and see what rows are visibile.
- Now go back to your Main sheet in the spreadsheet and pick a row that has Visibility column value set as public. Just remove the value from this cell.
- RePublish the spreadsheet
- Refresh the browser and verify the newly updated row is now hidden from your web page.
Youtube Video For This Tutorial
Limitations
This solution is workable, however you will see that the hidden rows are still showing up as empty rows in published page. So in case you have a lot of private rows you may see a lot of random spacing between rows.
Summary
We use google spreadsheet extensively for a lot of our collaboration work at our site between us and authors. Recently we wanted to create a reservation system for Authors to be able to reserve an article for themselves. We have this technique to share the available articles with all our authors. Once a article is reserved we change the Visibility of it and it disappears from the public list.
I understand that its not the best way to manage a reservation system, however it works for us with no cost. This approach can be used for many different types of things. Hope you will be able to make use of this.
Let us know how are you using google spreadsheet at work?