0
256
VistasHow to go over a 2 columns and set the colour of a cell under a condition
Im trying to go over 2 column and set the colour of them into red if they are not equal to specefic string, but its not working with me.
Here is my code.
function setCellColors() {
//Get the sheet you want to work with.
const thisSheet = SpreadsheetApp.openByUrl(THIS).getSheetByName('Project');
var range = thisSheet.getRange("V3:W").getValues();
var newData = range;
for (var i in newData) {
if(newData!== "There is no NCR" && newData !=="N/A" && newData!=="There is no Reason Codes"){
newData.setBackground("red");
}
}
}
I,m keep getting this error "TypeError: newData.setBackground is not a function" which I think because newData ia=s an array but how would I change it to string in that case, and is the rest of the code right?
If you need more information please let me know
Thank you.
·
Juan Pablo Isaza
2 Respuestas
Responde la pregunta0
Set selected backgrounds to red
function setCellColors() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet(Project);
const tA = ["There is no NCR", "N/A", "There is no Reason Codes"];
const vs = sh.getRange(3, 22, sh.getLastRow() - 2, 1).getValues();
const newData = vs;
vs.forEach((r, i) => {
if (!~tA.indexOf(r[0])) {
sh.getRange(i + 3, 22).setBackground("red");
}
});
}
·
Juan Pablo Isaza
Denunciar
0
It's not quite clear what of the columns should or should not contain one of the texts. I suppose it's the column 'V'. It this case you can try this code:
function setCellColors() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var texts = ['There is no NCR', 'N/A', 'There is no Reason Codes'];
var range = sheet.getRange('V3:W');
var values = range.getValues();
var backgrounds = range.getBackgrounds();
for (var row in values) {
if (texts.includes(values[row][0])) {
backgrounds[row][0] = 'red';
backgrounds[row][1] = 'red';
}
}
range.setBackgrounds(backgrounds);
}
The result:
If you want to check both of the columns you can change this line:
if (texts.includes(values[row][0])) {
with this:
if (texts.includes(values[row][0]) || texts.includes(values[row][1])) {
·
Juan Pablo Isaza
Denunciar
Responde la pregunta