Read rights in Google Sheets

When working with Google Sheets , fine, user-based rules for editing worksheets, columns and rows can be defined within a spreadsheet. Unfortunately, there is no way to restrict reading rights. However, this would be very helpful in many scenarios. So you only show your customers those tickets that concern them or the employees only the projects they are working on.

One possible solution is to create a so-called mastersheet and, with the help of IMPORTRANGE, to read selected data from there in one direction. Unfortunately, that means a lot of manual work: creating new sheets, assigning file rights, developing and implementing the IMPORTRANGE formula, accepting permission, setting formatting. If the structure or the format changes, this has to be done manually for all spreadsheets.

The following Google Apps script does this automatically. If you execute the init () method once in the mastersheet, all slave sheets are automatically created, rights are assigned and formulas and formatting are set. If data in the master sheet change, it is automatically mirrored in the slave sheet. If the structure of the master sheet changes, simply execute init () again (previous sheets are automatically updated).