In a Spreadsheet, I have a tab "CLIENTS" with values in range B4:Q, each row is a customer.

These values are imported from another Spreadsheet with importrange.

In C4:C of "CLIENTS", there is the category to which each customer belongs. Then I have 3 other tabs for each category ("DATA", "EPHAD", "LIVRET") which have the same column format as the "CLIENTS" tab.

In A4:A of "CLIENTS" I added a formula NB.SI which counts the number of customers and therefore changes each time importrange does its job.

What I would like is that in "CLIENTS", each time the range A4:A (NB.SI formula that counts the customers) changes, the script copies all the values of the range B4:Q and pastes them in the tabs that correspond to the category (category DATA in tab DATA, EPHAD in tab EPHAD and tab LIVRET in LIVRET). Only the values directly on the existing values.

You will probably ask me why I don't just do the import via FILTER, QUERY or some other function. The answer is because I need the imported content to be editable.

Here is my sheet (the tab "SOURCE IMPORTRANGE" is not there on the original table. I simply inserted the values from the source file into it).

I tried to write a script but I can't get it to work properly. Also I can't get onEdit() to work. Here is the script:

//function onEdit(e) {
//  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CLIENTS");
//  var editedcol = e.range.getColumn();
//  console.log(editedcol)
//
//  if(ss.getActiveSheet().getName() == "CLIENTS" && editedcol == 1){
//    UpdateClients()
//  }
//}

function UpdateClients() {
  var sss = SpreadsheetApp.openById('1NTSTb8MlLyncgaEAPY5GwEeRY0qGy7B8UbuzjpiXZEI');
  var sourceValues = sss.getSheetByName("CLIENTS").getDataRange().getValues();  
  
try{
 //DATA
  var tdata = sourceValues.filter(function (row) {
    return row[2] == "DATA";
  }); 

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DATA")
  ss.getRange(4,2, 900, 17).clearContent()
  ss.getRange(4, 2, tdata.length,17).setValues(tdata);

}
catch(e){console.log(e)}
 
 try{
  //EPHAD
  var tephad = sourceValues.filter(function (row) {
    return row[2] == "EPHAD";
  }); 

  ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("EPHAD")
  ss.getRange(4,2, 900, 17).clearContent()
  ss.getRange(4, 2, tephad.length,17).setValues(tephad);

  }
catch(e){console.log(e)}

try{
  //LIVRET
  var tlivret = sourceValues.filter(function (row) {
    return row[2] == "LIVRET";
  }); 

  ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LIVRET")
  ss.getRange(4,2, 900, 17).clearContent()
  ss.getRange(4, 2, tlivret.length,17).setValues(tlivret);

  }
catch(e){console.log(e)}

}


//function onBGChange(e) {
//  if(e.changeType == 'VALUE')
//    Logger.log(SpreadsheetApp.getActive().getSheetByName("LIVRET").getActiveRange(A1).getValue());{
//    updateSheets()
//  }

    

CodePudding user response：

Distributing Rows to sheets named in Column C

function updateClients() {
  const sss = SpreadsheetApp.openById('id');
  const sh = sss.getSheetByName("CLIENTS");
  const vs = sh.getRange("B4:Q"   getColumnHeight(2,sh,sss)).getValues();
  const ss = SpreadsheetApp.getActive();
  const csh = ss.getSheetByName("COMMENTAIRES");
  csh.getRange(4,2,csh.getLastRow() - 3,csh.getLastColumn() - 1).clearContent();
  csh.getRange(4,2,vs.length,vs[0].length).setValues(vs);
  let oObj = { sA: [] };//collects rows into 2D arrays
  vs.forEach((r, i) => {
    if (!oObj.hasOwnProperty(r[1])) {
      oObj.sA.push(r[1]);
      oObj[r[1]] = [];
      oObj[r[1]].push(r);
    } else {
      oObj[r[1]].push(r)
    }
  });
  oObj.sA.forEach(n => {
    let sh = ss.getSheetByName(n);
    if (sh.getLastRow() > 3) {
      sh.getRange(4, 2, sh.getLastRow() - 3, sh.getLastColumn() - 1).clearContent();
    }
    sh.getRange(4, 2, oObj[n].length, oObj[n][0].length).setValues(oObj[n]);//outputting 2d arrays from oObj
  });
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i  ) {
    if (rcA[i].toString().length == 0) {
      s  ;
    } else {
      break;
    }
  }
  return rcA.length - s;
}