CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
DimitryART
DimitryART

Reputation: 31

If statement in appscript for a range of data

I have this code where the first big part is a column rearrangement and the second one contains an 'IF' statement

function moveCols() {
var ss = SpreadsheetApp.getActive();
var fonteEng100 = ss.getSheetByName('Relatório ENG 100 - base');
ss.insertSheet('ENG 100');
var novoEng100 = ss.getSheetByName('ENG 100');
fonteEng100.getRange('A:A').copyTo(novoEng100.getRange('A1'));
fonteEng100.getRange('J:J').copyTo(novoEng100.getRange('B1'));
fonteEng100.getRange('B:B').copyTo(novoEng100.getRange('C1'));
fonteEng100.getRange('C:C').copyTo(novoEng100.getRange('D1'));
fonteEng100.getRange('D:D').copyTo(novoEng100.getRange('E1'));
fonteEng100.getRange('F:F').copyTo(novoEng100.getRange('F1'));
fonteEng100.getRange('E:E').copyTo(novoEng100.getRange('G1'));
fonteEng100.getRange('G:G').copyTo(novoEng100.getRange('H1'));
fonteEng100.getRange('H:H').copyTo(novoEng100.getRange('I1'));
fonteEng100.getRange('K:K').copyTo(novoEng100.getRange('J1'));
fonteEng100.getRange('M:M').copyTo(novoEng100.getRange('K1'));
fonteEng100.getRange('O:O').copyTo(novoEng100.getRange('L1'));
fonteEng100.getRange('P:P').copyTo(novoEng100.getRange('M1'));
fonteEng100.getRange('N:N').copyTo(novoEng100.getRange('N1'));

novoEng100.getRange('O1').setValue("SITUAÇÃO");
novoEng100.getRange('P1').setValue("DIAS CORRIDOS");

var situ = novoEng100.getRange('O2:O');
//var dias = novoEng100.getRange('P2:P')

var status = novoEng100.getRange('B2:B');
if(status == "Cancelado"){
  situ.setValues("OS CANCELADA");
}

The catch is with the 'IF' statement. I want it to look through all cells in the range 'B2:B' and whenever the cell value in 'B2:B' ="Cancelado" it must return "OS CANCELADA" in the destiny range of 'O2:O'.

How can i make it look through each cell return th expected value (string)?

Upvotes: 0

Views: 160

Answers (1)

Cooper
Cooper

Reputation: 64140

Try something like this:

var situ = novoEng100.getRange('O2:O'+ novoEng100.getLastRow()).getValues();
var status = novoEng100.getRange('B2:B' + novoEng100.getLastRow()).getValues().flat();
status.forEach((e,i) => {
  if(e == "Cancelado") {
    situ[i][0] = "OS CANCELADA"
  }
})
novoEng100.getRange(2,15,situ.length, 1).setValues(situ)

Upvotes: 2

Related Questions