Push Updates To Multiple Google Sheets From Master Sheet

Question

Is there a way to scan multiple Google Sheets to find a match from the Master Sheet on a key and update the cells in the Google Sheet(s) where applicable?

For example, below I have 4 sheets. The first is the master and the others are the children. If there is a match on column A (Initiative Name) across any of the sheets from the Master, update the cells in column C (Amount) and D (Date), otherwise leave the sheets untouched. In this example, the Master has the Initiative Name "G" with the Amount of "50" and Date of "4/1/2020". Child 1 has Initiative Name of "G" and Amount of "30" and Date of "3/1/2020". I want to push the update to Child 1 and update only Amount and Date with what the Master Template has while leave everything else in Child 1 the same, and also not updating Child 2 or 3 (because there is no match on Initiative Name).

Master Sheet - https://docs.google.com/spreadsheets/d/18tWKLly6Hp6cF_9Qlqbjqq1OF0ix-FCR39i__gJyS7Q/edit#gid=0

Child 1 - https://docs.google.com/spreadsheets/d/1FDGQapk5In8DLC6DnFSJjFHTIhe7mUFSPPz1j0mXHz0/edit#gid=0

Child 2 - https://docs.google.com/spreadsheets/d/1mFiBIXt1xQntNXb9g2mWrBpc0saEpQu8ZHB-91l5Arw/edit#gid=0

Child 3 - https://docs.google.com/spreadsheets/d/1teBZ85VcVAePD_e_vp1f4MMnbRTR6JoZWLfF4oZxmTc/edit#gid=0

Do you always want the child sheets to show what data is in the master? Will the rows in the child sheets always have a matching row in the master? — IMTheNachoMan
You can use IMPORTRANGE to pull data from other sheets. So in the child sheets you could use IMPORTRANGE to pull data from the master and then show the data in the child that matches the the other columns in the child. — IMTheNachoMan
If the child sheets will have initatives that are not in the master, then you'll have to either have some recurring job that updates the child sheets or an onEdit trigger on the master that updates child sheets. — IMTheNachoMan

Iamblichus Iamblichus · Accepted Answer · 2020-03-18T08:43:26

You could do something along the following lines (check inline comments):

function updateChildren() {
  var childrenIds = ["child1-id", "child2-id", "child3-id"]; // Array with the ids of the children, modify accordingly (can be more than 3)
  var master = SpreadsheetApp.openById("master-id"); // Master id (modify accordingly)
  var children = [];
  var masterSheet = master.getSheetByName("Summary"); // Name of the Master sheet with data
  // Get the source values in Master:
  var firstRow = 2;
  var firstCol = 1;
  var numRows = masterSheet.getLastRow() - firstRow + 1;
  var numCols = masterSheet.getLastColumn() - firstCol + 1;
  var masterValues = masterSheet.getRange(firstRow, firstCol, numRows, numCols).getValues();
  childrenIds.forEach(function(childId) { // Iterate through each children id
    var childSheet = SpreadsheetApp.openById(childId).getSheetByName("Summary"); // Get sheet with data (sheet is named "Summary")
    var childNumRows = childSheet.getLastRow() - firstRow + 1;
    var childValues = childSheet.getRange(firstRow, firstCol, childNumRows, numCols).getValues(); // Get child values
    masterValues.forEach(function(masterRow) { // Iterate through each row with data in Master
      childValues.forEach(function(childRow, i) { // Iterate through each row with data in Child
        if (masterRow[0] === childRow[0]) { // Check if there is a match in column A
          childSheet.getRange(i + firstRow, 3, 1, 2).setValues([[masterRow[2], masterRow[3]]]); // Update columns C-D in Child
        }
      });  
    });
  });
}

Push Updates To Multiple Google Sheets From Master Sheet

1 Answers

Reference: