Google Sheets – How to Append Text to Each Column

This function can be used in each of the destination sheets to copy back to the main sheet.

function onEdit(event){
  var range = event.range;
  if(range.getSheet().getName() == "Sheet1" && range.getColumn() > 1 && range.getRow() > 1 ){//example conditionals to limit the action to exclude headers or other static data could be changed to work specific cells or ranges
    var mainsheet = SpreadsheetApp.openByUrl("main spreadsheet URL").getSheetByName("destination sheet");
    var dataA1 = range.getA1Notation();
    range.copyTo(mainsheet.getRange(dataA1));

  }
}

this is a simple trigger and requires your users to have edit access to your main sheet. If you do not want to give them that access you could use an installable trigger instead.

I a similar script could be used to push the data out from the main sheet to the secondaries. I would recommend setting it as a menu so that you have better control and because triggers have a 30 second execution time.

For a single sheet per spreadsheet that would look like this:

function copyOut() {
  var source = SpreadsheetApp.getActiveSheet();
  var sheets = [firstsheeturl, secondsheeturl,thirdsheeturl];
   for(var i = 0; i < sheets.length; i++){
     var ss = SpreadsheetApp.openByUrl(sheets[i]);
     source.getRange("A1:Z26").copyTo(ss.getRange("A1:Z26"));
   }

}

if you want to send each secondary sheet different data sub sets not using a loop would be simpler. It is important to keep the same data in the same cells in both sheets or the onEdit as written won't know where to put things. Writing in symetrical offsets into the onEdit and the copyOut would be a way around that.

I stumbled onto the answer myself, but attached is a sample data sheet in case this helps anyone else with a similar need.

The trick was taking the ending row reference "350" out from all ranges.

https://docs.google.com/spreadsheets/d/1-dxpI3GpLAxkoMyscPRsCwkaec1CWRuX5-5Ej0DjVTo/edit?usp=sharing