0

I have the following code which loops through the values in column A and if it finds a certain phrase, updates the cell value next to it. The issue I have is that the value i am looking for in column A is within a string. I believe what I need is IndexOf, however I am yet to be able to get the correct syntax/placement. I have added the toLowerCase because again, there is no consistency on how that value will be found. This is what I have managed to do thus far:

function Add_Label(){

  var ss = SpreadsheetApp.getActive();
  var s = ss.getSheetByName('test')
  var lr = s.getLastRow();

var range = s.getRange("A:A");
// var values = range.getValues().toString().toLowerCase();
 // Logger.log(values);


  for(var i=1;i<=lr;i++){


  var somecell = s.getRange(i, 1).getValue().toString().toLowerCase();


    if (somecell == 'vip') {
    s.getRange(i,2).setValue('VIP');
    } else if 
    (somecell == 'decom') {
    s.getRange(i,2).setValue('Decomission');
    } else if 
    (somecell == 'cert') {
    s.getRange(i,2).setValue('Certificate');
    } 

}
}

Edit:20 Jan

I did some more playing/learing today and managed to get the following code to work however it is painfully slow and often falls over/times out. I believe what i need to do is something along the lines of not getting the cell values with each iteration and instead use an array but I'll admit i'm a little lost in how to achieve that. 

  function Add_Labels_new(){

  var ss = SpreadsheetApp.getActive();
  var s = ss.getSheetByName('test')
  var lr = s.getLastRow();
  var range = ss.getRange('C1:C').getValues();

  Logger.log(lr);

 for(var i=1;i<=lr;i++){


var somecell = s.getRange(i, 3).getValue().toString().toLowerCase();


if (range[i][0].indexOf('new') != -1 && somecell.indexOf('vip') != -1 || 
   somecell.indexOf('vm') != -1)   {
s.getRange(i,12).setValue('VIP/VM');
} else if 
(range[i][0].indexOf('decom') != -1) {
s.getRange(i,12).setValue('Decom');
} else if 
(range[i][0].indexOf('cert') != -1) {
s.getRange(i,12).setValue('Cert');
} else if 
(range[i][0].indexOf('alert') != -1) {
s.getRange(i,12).setValue('Alert');
} else
  (s.getRange(i,12).setValue(issuetype));


   }
}
Morris Coyle
  • 63
  • 9

2 Answers2

2
function Add_Label(){
  var ss=SpreadsheetApp.getActive();
  var s=ss.getSheetByName('test');
  var v=s.getRange(1,1,s.getLastRow(),2).getValues();
  for(var i=0;i<v.length;i++) {
    var s=v[i][0].toString().toLowerCase();
    v[i][1]=(s=='vip')?'VIP':v[i][1];
    v[i][1]=(s=='decom')?'Decomission':v[i][1];
    v[i][1]=(s=='cert')?'Certificate':v[i][1];
  }
  s.getRange(1,1,v.length,v[0].length).setValues(v);
}

ternary operator

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • hmm, I had no idea you could write an If statement like that. Cool – xyz Jan 18 '20 at 16:03
  • Thanks Cooper, however when trying this I get an error with the final line "TypeError: Cannot find function getRange in object incident." – Morris Coyle Jan 20 '20 at 15:10
0

Finally managed to resolve this using the trick Simon Birt mentioned in the comments of this thread: Determining the last row in a single column

I used that to find the end of Col L and use that as a variable to start my iterations from.

Morris Coyle
  • 63
  • 9