Loop through a list of sheet URLs and publish data from a central sheet into new tabs on each child sheet.

publishDataToSheets.gs

/*

  Copy ALL finance actuals to each team forecast sheet so they can be used to show actuals in graphs, etc and assist forecasting.

*/


//source actuals sheet (from our Report master)
var source_sheet_doc = SpreadsheetApp.openById("XXXXXXX");

// Do the replication to the team sheets
function rollOutActuals() {
  
  // open the sheet containing the keys and links for each team forecasting sheet
  var linkSheet = SpreadsheetApp.openById("XXXXXXX").getSheetByName("sources");

  //get links to team sheets
  var teamLinks = linkSheet.getRange("C1:C").getValues();
  
  //this takes the actuals from the monthly report master
  var actualsSheet = source_sheet_doc.getSheetByName("finance actuals");

  //define dimensions of actuals range to get
  var lastRow = actualsSheet.getLastRow();
  var lastColumn = actualsSheet.getLastColumn();
  
  // get values
  var values = actualsSheet.getRange(1,1,lastRow,lastColumn).getValues();
    
  //loop through team forecast sheet URLs
  for each (teamLink in teamLinks) {
    //teamLink is actually an array... get the first element which is the url we want
    url = teamLink[0];
    
    // Is it really a URL?
    if(url.indexOf("http") >= 0) {
      //do the transfer
      actualTransfer(url,lastRow,lastColumn,values);
    }
  }
}

function actualTransfer(TargetSheetUrl,sourceLastRow,sourceLastColumn,sourceValues){

  var targetSpreadsheet = SpreadsheetApp.openByUrl(TargetSheetUrl);
  
  //this is mainly for the first time I run the script, if there isn't an 'Actuals" worksheet (which there won't be) then it will create one
  if (targetSpreadsheet.getSheetByName("Actuals") == null){
   targetSpreadsheet.insertSheet("Actuals"); 
  }
  
  var targetActuals = targetSpreadsheet.getSheetByName("Actuals");
  
  //define target range
  var targetLastRow = targetActuals.getLastRow();
  var targetLastColumn = targetActuals.getLastColumn();
  
  //empty sheet if it has values
  if (targetLastRow !== 0){
    targetActuals.getRange(1,1,targetLastRow,targetLastColumn).clearContent();
  }
  
  //copy/paste values
  targetActuals.getRange(1,1,sourceLastRow,sourceLastColumn).setValues(sourceValues);
  
  //protect new tab
  targetActuals.protect();
  
  //hide it
  targetActuals.hideSheet();
}