How can I use App scripts to write a note in a cell, based conditionally off another cell?-CodePudding

I'm trying to do some automation with google Sheets! I have a list of orders in a sheet from different suppliers and they all use different billing references, this is filled in manually by me or one of the team, but sometimes it's filled out wrong because of how many ref numbers these companies use so I'm trying some automation to help with this issue but am completely new to app script, My idea is to have a note that says what number billing ref to use so far I have come up with this (Also see example): Sheet Example

function onEdit(e) {
  var sheet = e.source.getSheets()[0];
  if (e.range.getSheet().getSheetName() === sheet.getSheetName()) {
    var range = sheet.getRange("C1:C"   sheet.getLastRow());
    var range2 = sheet.getRange("D1:D"   sheet.getLastRow());
    var values = range.getValues();
    if (values == "ebay") {
        var values3 = [["ebay ref"]];
    } else if (values == "amazon") {
        var values3 = [["amazon ref"]];
    } else if (values == "etsy") {
        var values3 = [["etsy ref"]];
    } else if (values == "AlliBaba") {
        var values3 = [["AlliBaba ref"]];
    } else {
        return "fooo";
    }
    range2.setNotes(values3);
    
    
  }
}

My Issue is that it only works on D1 and not the rest, any help would be appreciated! ™

CodePudding user response：

You need to use a for loop to iterate through all the values and to determine the correct note:

function onEdit(e) {
  var sheet = e.source.getSheets()[0];
  if (e.range.getSheet().getSheetName() === sheet.getSheetName()) {
    var range = sheet.getRange("C1:C"   sheet.getLastRow());
    var range2 = sheet.getRange("D1:D"   sheet.getLastRow());    
    var values = range.getValues();
    for(i=0; i<values.length;i  ){
      if (values[i] == "ebay") {
          var values3 = [["ebay ref"]];
      } else if (values[i] == "amazon") {
          var values3 = [["amazon ref"]];
      } else if (values[i] == "etsy") {
          var values3 = [["etsy ref"]];
      } else if (values[i] == "AlliBaba") {
          var values3 = [["AlliBaba ref"]];
      } else {
          return "fooo";
      }
      range2.getCell(i 1,1).setNote(values3);
    }
  }
}