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

  • 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 at 16:09
  • Related: Functions — reusable blocks of code – Rubén Apr 10 at 16:18
  • my problem has been solved, thanks sir – Lutfi Creativesys Apr 12 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);
  }

}
| improve this answer | |
  • 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 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.