Pushing formulas (not values) from Main Sheet to Child Sheets

Question

My problem is:

I have various staff that input data into Sheets. Each staff member has their own copy of a spreadsheet, but the formulas are the same for all staff.
It is very painful when I need to update the formulas because it requires me to update the formula in each spreadsheet.

Question:

Is there any way for me to have a master spreadsheet that contains only formulas which will be pushed to each spreadsheet? So I only need to change the master file which automatically updates the formulas in the child spreadsheets.

I tried to look for this on the internet but it seems impossible. This question has also been asked before in Stack Overflow and the answer was "not possible". I am trying to ask again wishing that there is a new solution for this problem.

Here are some sample spreadsheets:

This is the link for master spreadsheet with the formula: https://docs.google.com/spreadsheets/d/1UTICzoDWt8rU8csZWVB7pHcsyMkf_jQDNFQ0kxuvxC4/edit?usp=sharing
This is the link for an example child spreadsheet: https://docs.google.com/spreadsheets/d/12nmW4FRCYSb-7YrVeRgstGpB_FYRlYTlvTb1nQfdjc4/edit?usp=sharing

Yellow cells are the cells where I put the formulas.

Green cells are the cells where the staff put the data.

Do you mean staff members each have their own copy of a spreadsheet? — Calculuswhiz
Yes thats true! All staff members have their own copy of spreadsheet with same formulas but different data input according to each. — Randy Adikara
One option would be to keep all your formulas in your master sheet and have any staff sheets do an IMPORTDATA on the master sheet. — Calculuswhiz
I tried it with IMPORTDATA but I dont think it is the solution since the formulas werent transferred from one sheet to another — Randy Adikara
are the formulas always in the same place? In your example, A2 and B2? And you just want to bring the formulas from these cells into the same location in all your staff 's sheets? — iansedano

iansedano iansedano · Accepted Answer · 2020-09-28T17:04:35

Intro

Below you have a script that you can configure yourself, it does involve a bit of setup though, so I have tried to list the instructions as thoroughly as possible.

You will need to be able to get the Sheet IDs of all your employees. This can be found in the URL of each Sheet.

https://docs.google.com/spreadsheets/d/{HERE IS THE ID}/edit#gid=0

It will be a long string of random characters.

You will also need to either have edit access or be the owner of all the sheets.
You can keep the master sheet private.
Be aware of the quotas. It should be fine for your use, but you may run into quota limitations if you have many child sheets.

Proposed Solution

Open your master sheet.
Go to "Tools" > "Script Editor"
Replace the empty function with the below code:

function updateFormulas(e) {
  
  // Here insert the INDIVIDUAL ranges of all the cells
  // that will contain formulas you want to move across
  var formulaRanges = ["A2", "B2"]

  // Here list the names or IDs of employees with
  // the corresponding ID of the Sheet.
  var childSheets = {
    user1: "YOUR 1st CHILD ID HERE",
    user2: "YOUR 2nd CHILD ID HERE"
  }

  // If you want to change your sheet name, change it here
  var sheetName = "Sheet1"
  
  try {
    var ss = e.source.getActiveSheet().getName()
    var rng = e.range.getA1Notation()
    var formulaToSet = e.range.getFormula()
    
    if (ss == sheetName && formulaRanges.includes(rng)) {
        for (const user in childSheets) {
          var userSheet = SpreadsheetApp.openById(childSheets[user])
          userSheet.getRange(rng).setFormula(formulaToSet)
        }
        
      }
  } catch (err) {Logger.log(err)}
}

function install() {
  var sheet = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("updateFormulas")
  .forSpreadsheet(SpreadsheetApp.getActive())
  .onEdit()
  .create();
}

In the menu click "View" > "Show manifest file".
Open the file that appears on the left "appsscript.json".
Add in the oauthScopes section:

{
  "timeZone": "Europe/Paris",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets"
  ]
}

Go back to the original script file.
You should see a drop down box like this:

Select updateFormulas and then press the "play" button which will run the script.
It should ask you to grant permissions, grant them.
Then select install and run that, you will only need to run this once.
Configure the updateFormulas function with the ranges of each formula you want to push to the child spreadsheets and the users and IDs of every Sheet that you want to push to. This part:

  // Here insert the INDIVIDUAL ranges of all the cells
  // that will contain formulas you want to move across
  var formulaRanges = ["A2", "B2"]

  // Here list the names or IDs of employees with
  // the corresponding ID of the Sheet.
  var childSheets = {
    user1: "YOUR 1st CHILD ID HERE",
    user2: "YOUR 2nd CHILD ID HERE"
  }

  // If you want to change your sheet name, change it here
  var sheetName = "Sheet1"

Test it by changing a formula in your master sheet and watch it propagate!

Explanation and References

This script makes use of an installable onEdit trigger. This will run every time you make an edit on the Sheet. It will check if the edit is on "Sheet1" and if the edit is of a range defined in "formulaRanges". If these are true, then it will copy the edited range over to the child Sheets.
It reads, comaperes and writes formulas with methods detailed here. For example: