JS / Google Sheets optimal way of turning a "B2:H2" range to an array of cells

I'll be giving a bit of context:

Using App script, I'm working on a macro which copies data from a sheet to another. To avoid copying mistakes cascading, I added code to check whether or not the cells are empty. There are 7 cells to check and the first or 5th one may be empty. (they either are both filled, or one of the 2 is blank, if the 2 are blank it's invalid), to simplify I made it so that the ranges are always continuous and on a single line, so only the columns change.

To achieve my goal I did a simple counting loop:

var rangeArray = rangeToArray(range);
var emptyCells = 0;
for (var o = 0; o < rangeArray.length; o++) {
  if (sheet.getRange(rangeArray[o]).isBlank()) { emptyCells ++; }
}

and only copy if the value of emptyCells is < 2 (it is presumming the cells they leave empty are the correct ones but I can't reasonably account for all human errors).

(Please ignore the var, I tried using let, but for obscure reasons App Script doesn't support it)

range is the range in the A1 format such as "B2:H2",

emptyCells is the counter of empty cells,

sheet.getRange(rangeArray[o]).isBlank()) returns true when the cell is blank, false otherwise.

rangeToArray(range) however is where my question lies in, it's a custom function I made which turns range into an array of cells, such as "B2:H2" → ["B2", "C2", "D2", "E2", "F2", "G2", "H2"]

Because it has to account for ranges anywhere between A1 and AZ999..., it has to work for both 1 letter and 2 letters column indexes on both the 1st and 2nd cell indexes as well as for any finite row indexes.

The code displayed below works, however, It's having about 1.8 +/- 0.2 seconds execution time which is slow, even for a Google Sheet macro. (simplier macros on that sheet run between 0.3 and 1.4 seconds) I'm not a JS dev, I started learning it a little over a week ago specificaly for that project; so it's the best I could do after spending a few days on the matter but I have no idea how good or bad that actualy is. If anyone has suggestions for improvements or see obvious optimisations, please go ahead I'm all hears.

P.S.: I already tried adding an if statement to break out of the for loop in as soon as both the 2nd and 4th letters are found but it actualy increases execution time by 0.2 seconds on average.

My range to array of cells converter is as follows:

function rangeToArray(range) {
  var posColon = range.indexOf(":"); // position of the colon
  var posSecondLetter = 0; // position of the 2nd letter if there is one, the 1st one otherwise
  var posFourthLetter = posColon+1; // position of the 4th letter if there is one, the 3rd one otherwise
  var posTemp1 = -1;
  var posTemp2 = -1;
  var rangeArray = [];
  // Loops through all 26 letters to find if it matches with a potential 2nd or 4th letter
  for (var k = 0; k < 26; k++) {
    posTemp1 = range.indexOf(letterArray[k], 1);
    posTemp2 = range.indexOf(letterArray[k], posColon+2);
    if (posTemp1 != -1 && posTemp1 < posColon) {
      // it found what the 2nd letter is if there is one
      posSecondLetter = posTemp1;
    }
    if (posTemp2 != -1) {
      // it found what the 4th letter is if there is one
      posFourthLetter = posTemp2;
    }
  }
  // isolate the according column indicators of the 1st and 2nd cell as well as their row numbers
  var firstCellColumnIndex = numberOfLetter(range.slice(0, posSecondLetter+1));
  var secondCellColumnIndex = numberOfLetter(range.slice(posColon+1, posFourthLetter+1));
  var firstCellRowIndex = range.slice(posSecondLetter+1, posColon-posSecondLetter);
  var secondCellRowIndex = range.slice(posFourthLetter+1, range.length);
  //generating the array of cell inbetween and including them
  for (var row = firstCellRowIndex; row <= secondCellRowIndex; l++) {
    for (var col = firstCellColumnIndex; col  <= secondCellColumnIndex; m++) {
      rangeArray.push(letter(col)+row.toString());
    }
  }
  return rangeArray;
}

letterArray is just an array of all capital letters in alphabetical order, I made it for 6 other functions to convert from numerical index to alphabetical since unlike Ada, JS can't count in alphabetical indexes, so it came in handy there.

numberOfLetter() is a custom function which takes a capital letter character or pair of characters and returns their corresponding numerical index, the reciprocal of letter(). ("A" → 1, "AZ" → 52)

letter() is a custom function which takes a numerical index and returns their corresponding alphabetical index. (1 → "A", 52 → "AZ")

I doubt there's much to improve in letter() or numberOfLetter(), unless there's a trick I don't know about for loops but here they are if you wish.

function letter(number) {
  if (number <= 26) { //indexes from A to Z
    return listeLettres[number-1];
  } else if ((26 < number) && (number <= 52)) { //indexes from AA to AZ
    return "A"+listeLettres[number-27];
  }
}

function numberOfLetter(letter) {
  for (var i = 0; i < 26; i++) { //indexes from A to Z
    if (letter == listeLettres[i]) {
      return i+1;
    }
  }
  for (var j = 0; j < 26; j++) { //indexes from AA to AZ
    if (letter == "A"+listeLettres[j]) {
      return 26+j+1;
    }
  }
}

(I'm aware the multiple returns are bad practice but considering I'm forced to have all variables as global thanks to App Script I'd rather limit unecessery intermediary variables where I can + it also seems to shorten execution time and the lower I can get that, the happier I am xD)

Via Active questions tagged javascript - Stack Overflow https://ift.tt/OdwRuxv