Creating duplicate linked Google spreadsheets that share copy and formatting

Question

I'm looking to create a content calendar on one Google Drive and then have that exact content calendar (copy, formatting, and any new rows that get added) shared as a tab in a spreadsheet on another Google Drive. All of the cell inputs will be text based, I'm not sure if that makes any difference. Is this even remotely possible?

Additional information (from the comments):

Ideally I'd want it to happen in real time. Basically what I want to achieve is I would like one team to be able to write a bunch of coop and have their own approvals process (with a specific set of users who can see the document). I then want this document to get automatically updated in the tab of a master document so a different team can see what is being done. They might need to edit some copy if the approvals are bad but mostly its for oversight. I then want to replicate this out across about 30 teams.

So the structure would be: Team 1 [has read/write privileges and their own set of users for a document] Same for Team 2-30 Then there is a master document that pulls in all of the sheets from Teams 1-30 all in one place. This is a different set of users.

Answer 1

You can achieve this by using an array of importrange() functions. This cannot import the formatting information, which is not possible without Apps Script, but it will pull in multiple sheets and aggregate them into one list, which you can then sort with an additional formula. Keep in mind that each imported sheet will have to have the same number of columns. For example, if you have four sheets to import with 4 columns each, the import formula will be as follows:

={
importrange("url_to_sheet_1", "Approved!$A:$D");
importrange("url_to_sheet_2", "Approved!$A:$D");
importrange("url_to_sheet_3", "Approved!$A:$D");
importrange("url_to_sheet_4", "Approved!$A:$D")
}