Search code examples
google-sheetsgoogle-sheets-apigoogle-sheets-query

Replicate the rows and columns from a base sheet in a tab, and keep multiple tabs up to date with info there

We have a base sheet as shown here.

Base sheet

This is the place where this info needs to be edited by our core team. No other team can edit this, so we will make this tab 'view only' for everyone except the core team.

However, we want other teams to always refer to the info created and managed there, and inherit it for their purposes.

It is important for us to have a single file for everyone. No multiple files all over the place. As such, the additional (non-core) teams will be given their own "tabs" or worksheets. Their worksheet will be editable by them, and they add some columns there but not be able to edit the info from the core team. As such, the tab for these other teams will look like this:

OtherTeamTab

The orange here signifies that those items are uneditable by anyone in this tab, as they're inherited from the Base tab.

Is there an easy mechanism to do this: to inherit rows and columns from another tab (Sheet), and keep them updated automatically such that any info changed in the original will effortlessly reflect in this additional tabs but be non-editable, while inside these additional tabs the users can add and edit their own columns.

Can we do this without going crazy with spreadsheet programming and Google API? Many thanks for any pointers!

Solution

  • Maybe in A1s:

    =query(Base!A:A)

    and in D1s

    =query(Base!B:D)