Google brings us a collaborative spreadsheet interface(the Google Spreadsheet) with tons of features and sharing options. We can share spreadsheets with somebody, protect individual sheets and publish it to web. Still it is lacking a feature which may be useful for its users i.e. share portion of data from a sheet. It comes handy when we want to share only the relevant data to a person. But amazingly we can achieve that with Google App Script. Hence we wrote a small piece of code which will be deployed as web app and it can read and display a portion of data range provided.
How to allow others to view a particular data range
Before discussing further, I'll highlight another alternative approach, i.e.. using a spreadsheet formula. We can create a new blank sheet and use the IMPORTRANGE formula to insert the required data range. Once the data appears on the new sheet, we can publish it to web to be viewed by others. There is an option to publish a single sheet. But this approach involves unnecessarily crating a new sheet. Moreover it does not even display the stylings like background-colors, font-colors etc.
In contrast the app script approach doesn't need additional sheet and it also displays used stylings.
- Open above spreadsheet URL.
- Go to file → Make a copy
- Go to Tools → Script editor to view the script
- In code.gs you can find some settings at the top of the script.
- Provide the spreadsheet URL, name, data range and title to be displayed.
- Go to Publish → Deploy as web app …
- Project version: new
- Execute the app as: me
- Who has access to the app: Anyone even anonymous
- Once you update you can see a deployed URL where you can see the required data
The scripts supports
- Displayed values
- Background colour
- Font color
- Font size
- Font weight
- Font family
It doesn't support cell merging and cell splitting.
Enhancements
The script can be enhanced in many ways. You can share multiple data range to multiple people, keep additional authentication layer like asking for password before displaying the data, auto update the data in every 5 secs, share portion(data range) to specific email id instead of making it public. Contact us if you need any customisation.
