0

I'm working with Google Sheets Script Editor and I'm trying to do a check to see if a cell contains the text DROPPED, DENIED, WEBSITE, POTENTIAL, SELECTED, TESTING, INACTIVE, or ACTIVE. If it contains DROPPED or DENIED, I need it to say "DENIED", if it says SELECTED/TESTING/INACTIVE/ACTIVE, it needs to say "ACCEPTED", and if it says WEBSITE/POTENTIAL it needs to say "INCOMPLETE".

My code looks roughly like this. I don't have any formal training so it's probably a mess, but hopefully there's an easier way to do it.

if (('' + data1[i][1]).trim().toUpperCase() == "DROPPED" ||
    ('' + data1[i][1]).trim().toUpperCase() == "DENIED") {

        sh2.getRange(j + 1, 11, 1, 1).setValue("DENIED"); 

}else if (('' + data1[i][1]).trim().toUpperCase() == "SELECTED" ||
    ('' + data1[i][1]).trim().toUpperCase() == "TESTING" || 
    ('' + data1[i][1].trim().toUpperCase() == "INACTIVE" || 
    ('' + data1[i][1].trim().toUpperCase() == "INACTIVE" ||
    ('' + data1[i][1].trim().toUpperCase() == "ACTIVE") {

        sh2.getRange(j + 1, 11, 1, 1).setValue("ACCEPTED");

}else if (('' + data1[i][1]).trim().toUpperCase() == "WEBSITE" ||
    ('' + data1[i][1]).trim().toUpperCase() == "POTENTIAL") {

        sh2.getRange(j + 1, 11, 1, 1).setValue("INCOMPLETE");

I'm pulling this from a "Cross Check" script that's comparing cell contents between two different sheets, which is why it's using sh2.getRange. Mostly I'm just wondering if there's an easier way to write it than that huge if/elseif. If not, that's fine, it's just going to need to run a couple thousand checks so if I can do it in a way that minimizes the amount of legwork the script does, that would be better.

The only thing I can think of is just forcing the cell to be upper case before it does the checks, and then putting it back to proper case when the checks are done. I don't know if continuing to do toUpperCase is going to take more time or not. Honestly I don't know entirely how that even works, I just know it does. We contracted out the original script and I don't know exactly what he did, he didn't comment anything so I had to figure it out myself.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Matt
  • 5
  • 2
  • 1
    For starters would be a lot cleaner to read by storing `'' +data1[i][1]).trim().toUpperCase()` in a variable and replacing all instances with that variable – charlietfl Aug 09 '18 at 20:22
  • [Switch statement multiple cases](https://stackoverflow.com/a/13207939/463206) could be one way. – radarbob Aug 09 '18 at 20:26
  • 1
    What's wrong with what you have? As charlie mentions, you definitely want to simplify this by only trimming & uppering once: `var dVal = data[i][1].trim().toUpperCase(); if (dVal === ... ` You can use an `Array#indexOf` method as hinted by Simranjit, or even use an associated object, e.g. `var converter = {DROPPED: "DENIED", DENIED: "DENIED", SELECTED: "ACCEPTED", ... }` and then `var output = converter[dVal]; if (output) { ...` – tehhowch Aug 09 '18 at 20:32

2 Answers2

0
sh2.getRange(j + 1, 11, 1, 1).setValue(ParseData(data1[i][1]));


function ParseData(data) {
    switch (('' + data).trim().toUpperCase()) {
        case "SELECTED":
        case "TESTING":
        case "INACTIVE":
        case "ACTIVE":
            {
                return "ACCEPTED";
            }
        case "DROPPED":
        case "DENIED":
            {
                return "DENIED";
            }
        case "WEBSITE":
        case "POTENTIAL":
            {
                return "INCOMPLETE";
            }
        default:
            {
                return "";
            }
    }
}
Dongdong
  • 2,208
  • 19
  • 28
  • That looks like it would work, I completely forgot about switch statements. It's been like six years since I took a class on this, lol. Does "parseData(data)" actually work or do I need to replace "data" with something? Or does data become data1[i][1] because it's calling the function up above? Also, this will all be inside a loop that's incrementing i, will that be a problem? I'm guessing it's best to put the function at the very bottom of the page, after the body function. I think. I'm a bit rusty. – Matt Aug 09 '18 at 21:24
  • the names of variables & functions can be changed to any other names in your real project. – Dongdong Aug 09 '18 at 21:59
0

Create 3 arrays of groupings Then you can simply check if value is in one of the arrays then print the respective value. eg: using one array grouping to get the value denied.

let deniedArray = ['DENIED','DROPPED'];
let value = data1[i][1]).trim().toUpperCase();
if( deniedArray.includes(value) ){
   sh2.getRange(j + 1, 11, 1, 1).setValue("DENIED");
}
Simranjit Singh
  • 384
  • 1
  • 6