I use an onEdit() script that adds the color of the edited cells in column "L" according to their value (source of background colors in another tab).
I have a second script that copies the updated client list to another tab and pastes them into the whole sheet (only the values). The client list can't be sorted in a logical order, so the values in the rows change. So the values in column "L" change but the background colors do not follow the movement.
Example, if in L8 the value is "RDV 1" the background color is blue. If I add 2 rows, the value in L8 will move to L10. The blue will stay in L8 no matter what the value of L8 is and "RDV 1" which will be in L10 will have the background color that was before it. I added some example pictures.
This spreadsheet is very complex for the backup and import of data for a commercial management. So it would be long and complicated to explain in details where the problem comes from, and I'm sorry for that, but I know what should be done to fix it. To make it simple:
In tab DATA, EPHAD and LIVRET, when B4:R pasted then getValues of column L4:L of the 3 tabs, findNext().getBackgrounds() of C6:C of the tab VALIDATION and setBackgrounds() in L4:L of tabs DATA, EPHAD and LIVRET. To add this inside updateClients() code.
Before added rows:
After added rows:
My script that add values ( I tried to modify it but it doesn't work):
function updateClients() {
const sss = SpreadsheetApp.openById('1NTSTb8MlLyncgaEAPY5GwEeRY0qGy7B8UbuzjpiXZEI');
SpreadsheetApp.getActive().toast("", "⏳ Chargement des Leads en Cours...", -1);
const sh = sss.getSheetByName("CLIENTS");
const vs = sh.getRange("B4:R" getColumnHeight(2,sh,sss)).getValues();
//Modified 1
const cs = sh.getRange("L4:L" getColumnHeight(2,sh,sss)).getBackgrounds();
//End 1
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);
//Modified 2
csh.getRange(4,12,csh.getLastRow() - 3,csh.getLastColumn());
csh.getRange(4,12,cs.length,cs[0].length).setBackgrounds(cs);
//End 2
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();
}
//Modified 3
sh.getRange(4, 2, oObj[n].length, oObj[n][0].length).setValues(oObj[n]);
if (sh.getLastRow() > 3) {
sh.getRange(4, 12, sh.getLastRow() - 3, sh.getLastColumn());
sh.getRange(4, 12, oObj[n].length, oObj[n][0].length).setBackgrounds(oObj[n]);//outputting 2d arrays from oObj
}
//End 3
});
SpreadsheetApp.getActive().toast("", "✅ Chargement des Leads Terminé");
}
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;
}
It takes the range B4:R of "CLIENTS", pastes it in "COMMENTAIRES" and then if in "C4:C" the value is DATA then range B4:R in the sheet DATA, if in "C4:C" the value is EPHAD range B4:R in the sheet EPHAD and if "C4:C" the value is LIVRET range B4:R in the sheet LIVRET.
I would like that in DATA, EPHAD and LIVRET, it pastes the good background colors in the column "L" of each of these 3 sheets. The color source is the range C6:C on the sheet VALIDATION if the values match
CodePudding user response：
I finally found the solution:
function updateClients() {
const sss = SpreadsheetApp.openById('1NTSTb8MlLyncgaEAPY5GwEeRY0qGy7B8UbuzjpiXZEI');
SpreadsheetApp.getActive().toast("", "⏳ Chargement des Leads en Cours...", -1);
const sh = sss.getSheetByName("CLIENTS");
const vs = sh.getRange("B4:R" 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
});
const ssss = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.getActive().toast("", "⏳ Attribution des Couleurs aux Statuts...", -1);
["DATA", "EPHAD", "LIVRET"].forEach(function (z) {
const sh = ssss.getSheetByName('VALIDATION');
const cA = sh.getRange(6,3,sh.getLastRow()).getDisplayValues().flat();//values
const bA = sh.getRange(6,3,sh.getLastRow()).getBackgrounds().flat();//colors
let colors = {pA:[]};
cA.forEach((c,i) => {
colors[c]=bA[i];
colors.pA.push(c);
});
const osh = ssss.getSheetByName(z);
const vs = osh.getRange(4,12,osh.getLastRow()-3).getDisplayValues();
const bs = osh.getRange(4,12,osh.getLastRow()-3).getBackgrounds();
let bgA = vs.map((r,i) =>{
r.forEach((c,j) =>{
let idx = colors.pA.indexOf(c);
if(~idx) {
bs[i][j] = colors[c];
}
});
return bs[i];
});
osh.getRange(4,12,bgA.length,bgA[0].length).setBackgrounds(bgA);
})
SpreadsheetApp.getActive().toast("", "✅ Chargement des Leads Terminé");
}
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;
}