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
  • It looks that this question is more about programming that about using Google Apps Script editor / Google Sheets but IMHO it's not yet clear enough to migrate it to another site in the network. I suggest you to checkout the How to ask guidelines of Stack Overflow and Code Review.
    – Rubén
    Apr 10 '20 at 16:09
  • Related: Functions — reusable blocks of code
    – Rubén
    Apr 10 '20 at 16:18
  • my problem has been solved, thanks sir Apr 12 '20 at 11:56
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.