CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Bava
Bava

Reputation: 73

Push Updates To Multiple Google Sheets From Master Sheet

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

Upvotes: 0

Views: 1556

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

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
        }
      });  
    });
  });
}

Reference:

Upvotes: 2

Related Questions