0

I made a script to combine data from Sheet1 and Sheet2 into Sheet "Master".

If there is a data change in the "Master" sheet, I write the script to update the data changes to each sheet.

Sheet1 and Sheet2

My Spreadsheet

//combine data from multiple sheets
function MergeSheets() {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var data = null;
  var RetrieveSheet = null;
  var PasteSheet = ss.getSheetByName("Master");
  var sheets = ['Sheet1','Sheet2'];
  PasteSheet.getRange(2,1,PasteSheet.getLastRow(),PasteSheet.getLastColumn()).clear();

  for (var i =0; i<sheets.length; i++){
    RetrieveSheet = ss.getSheetByName(sheets[i]);
    if (RetrieveSheet.getName() != 'Master'){
      data = RetrieveSheet.getRange(2,1,RetrieveSheet.getLastRow(),RetrieveSheet.getLastColumn()); 
      data.copyTo(PasteSheet.getRange(parseInt(PasteSheet.getLastRow())+1,1));
    }
  }
}

//update data changes to each sheets
function updateBasedSheet(){
  var master = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master');
  var valueMaster = master.getRange(2, 1, master.getLastRow(), master.getLastColumn()).getValues();
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var dataSheet1 = valueMaster.filter( function(item){
    return item[2] === 'Sheet1';
  });

  var dataSheet2 = valueMaster.filter( function(item){
    return item[2] === 'Sheet2';
  });

  sheet1.getRange(2, 1, dataSheet1.length, dataSheet1[0].length).setValues(dataSheet1);
  sheet2.getRange(2, 1, dataSheet2.length, dataSheet2[0].length).setValues(dataSheet2);
}

But, my code is still no simple. The problem is when I have 100 sheets, I have to write over and over:

this

3
0

My question has been solved. I modified my code like this :

//Remark sheet
function remark_sheet(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets().reduce(function(acc, elem){
    if (elem.getSheetName() != "Master") {
       acc.push(elem);
       }
       return acc;
  },[]);

  for (var i = 0; i < sheets.length; i++){
      var lastR = sheets[i].getRange(1, 2).getDataRegion(SpreadsheetApp.Dimension.ROWS).getLastRow();
      sheets[i].getRange("C2:C").clearContent();
      sheets[i].getRange("C2:C"+lastR).setValue(sheets[i].getSheetName());
  }
}

//combine data from multiple sheets
function MergeSheets() {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var data = [];
  var masterSheet = ss.getSheetByName("Master");

  var sheets = ss.getSheets().reduce(function(acc, elem){
    if (elem.getSheetName() != "Master") {
       acc.push(elem);
       }
       return acc;
  },[]);  // here I get all sheets obj  excluding master sheet as array [sheet, sheet, ...]

  masterSheet.getRange(2,1,masterSheet.getLastRow(),masterSheet.getLastColumn()).clearContent();

  data = sheets.reduce(function(arr, sheet){
     arr = arr.concat(sheet.getDataRange().getValues());
     return arr;
  },[]).filter(function(row){
   return row[0] != "No";
  });

  masterSheet.getRange(2, 1, data.length, data[0].length).setValues(data);
}

//update data changes to each sheets
function updateBasedSheet(){
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var master = ss.getSheetByName('Master');
  var valueMaster = master.getRange(2, 1, master.getLastRow(), master.getLastColumn()).getValues();

  var sheets = ss.getSheets().reduce(function(acc, elem){
    if (elem.getSheetName() != "Master") {
       acc.push(elem);
       }
       return acc;
  },[]);

  for (var i = 0; i < sheets.length; i++){
    var data = valueMaster.filter( function(item){
      return item[2] === sheets[i].getSheetName();
     });
     sheets[i].getRange("A2:C").clearContent();
     sheets[i].getRange(2, 1, data.length, data[0].length).setValues(data);
  }

}
1
  • It's very likely that your answer will be better received in you briefly describe the changes you made to the original script. – Rubén Apr 12 '20 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.