CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscustom-functiongoogle-sheets-custom-function
harvanchik
harvanchik

Reputation: 78

Search through all sheets for a string which have a specific value in the cell next to it

I have a custom formula script that loops through all sheets containing "@" in their tab name. In there, I then look through a range to find the string that I am searching for (given as an argument). If I find that string in the range, I run some other code to check other cells.

The important part here is finding the string in the range. Here is my code for doing that.

    /**
    * Searches through the box scores and counts Goaltender Wins.
    *
    * @param {"Hollywood|Hattricks|HOL"} teamName The team to search for.
    * @param {"Z1"} dummy The cell to store random number in order to refresh values on editing the box scores.
    * @return {number} The number of times the item appears in the range(s).
    * @customfunction
    */
    function calcGoaltenderWins(username, dummy) {

      var count = 0;

      SpreadsheetApp.getActive().getSheets().forEach(function (s) {
        //regular season sheets
        if (s.getName().indexOf("@") >= 0 && s.getName().indexOf(":") === -1) {
          //if sheet is finalized
          if (s.getRange("U37").getDisplayValue() != "Live ⬤") {
            //if the home team won
            if (s.getRange("Q11").getValue() < s.getRange("Q12").getValue()) {
              //check if goaltender is on the player list for that game
              s.getRange("Y5:Y12")
              .getValues()
              .reduce(function (a, b) {
                return a.concat(b);
              })
              .forEach(function (v) {
                if (v === username) count += 1;
              });
            }
            //if the away team won
            if (s.getRange("Q12").getValue() < s.getRange("Q11").getValue()) {
              //check if goaltender is on the player list for that game
              s.getRange("D5:D12")
              .getValues()
              .reduce(function (a, b) {
                return a.concat(b);
              })
              .forEach(function (v) {
                if (v === username) count += 1;
              });
            }
          }
        }
      });
      return count;
    }

Here is what the sheet looks like for reference.

The thing I want to figure out is how to check if the string I am checking for has a specific value 2 cells to the left of it. This value should be "G".

For example, I used the formula =calcGoaltenderWins("Name", dummy). If the name "Name" is found inside the range D5:D12 (specifically in D5), then it should also only check if B5 (the cell 2 to the left) if it equals "G" in order to continue running the code. If it does not equal "G", then it should not respond to the string it found.

Upvotes: 0

Views: 170

Answers (1)

Cooper
Cooper

Reputation: 64110

Try this:

function calcGoaltenderWins(username, dummy) {
  var count = 0;
  const ss=SpreadsheetApp.getActive();
  const shts=ss.getSheets()
  shts.forEach(function(s) {
    if (s.getName().indexOf("@")>=0 && s.getName().indexOf(":")==-1) {
      if (s.getRange("U37").getDisplayValue()!="Live ⬤") {
        if (s.getRange("Q11").getValue() < s.getRange("Q12").getValue()) {
          s.getRange("Y5:Y12").getValues().reduce(function(a,b){return a.concat(b);}).forEach(function(v){if (v==username)count+=1;});
        }
        if(s.getRange("Q12").getValue() < s.getRange("Q11").getValue()) {
          let v=s.getRange("B5:B12").getValues();//added
          s.getRange("D5:D12").getValues().reduce(function(a,b,i) {if(v[i][0]=="G"){return a.concat(b)}else{return a;};}).forEach(function(v){if (v==username)count += 1;//edited
          });
        }
      }
    }
  });
  return count;
}

Upvotes: 3

Related Questions