Copy/paste a range from one sheet to several others depending on the value of a cell in a column

In a spreadsheet, I have a tab “CUSTOMERS” with values in the range B4:Q,. These values are imported from another spreadsheet with importrange.

In C4:C of “CLIENTS”, there are the names of 3 products which correspond to the names of 3 other tabs (“DATA”, “EPHAD”, “LIVRET”) which have the same column format as the tab “CLIENTS”.

What I would like to do is to copy all the values from the range B4:Q of “CLIENTS” and paste them into the tabs that correspond to the product (DATA rows in the DATA tab, EPHAD in the EPHAD tab and LIVRET tab in LIVRET).

I don’t just import via FILTER, QUERY or another function 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. Here is the script:

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)}

}

   

Answer

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;
}