Google Sheets script that runs based on the face value of a cell while ignoring formulas-CodePudding

I made a Google Sheet to check every media that plays on a certain channel on TV using a lot of workaround formulas within the cells themselves. A part of this sheet is a column (G) that tells me whether or not the specific episode/media/whatever is currently playing, has played in the past or will be played later today/at a later date using a "NOW" function. Next to that column there is another (F) where the user is able to write a "V", and in the case the show is playing but the user hasn't checked it yet, it writes "Check Me" See Example.

I wanted to create a button that will automatically change that "Check Me" into a "V" but the problem is that "Check Me" is based on a simple formula written throughout column F (=IF(G5="Playing","Check Me","")), so when I tried to run a script I found here on StackOverflow:

function Test() {
 var sheet = SpreadsheetApp.getActiveSheet(); 
 var range = sheet.getRange("F5:F700");  
 range.setValues(range.getValues().map(function(row) {
  return [row[0].replace(/Check Me/, "V")];
}));
}

(Can't remember the exact thread I got it from and it's been two days since I found it with lots of similar searches in my history, so I apologize for not crediting)

together with its intended use, it also straight up deleted all the rest of the formulas from the column, probably due to the formula itself containing "Check Me" but I might be mistaken. To be honest, before this week I barely ever worked with either Google Sheets, much less JavaScript or even coding in general, so I'm pretty much restrained to changing values and very minor modifications in scripts I find online.

The only idea I had as to how to solve it is to add an "if IsBlank" but regarding face value of the cell only rather than its contents, but I don't know how to do it or whether it is even possible in the first place. At the very least, google shows no results on the subject. Is there a way to add that function? or perhaps a different method altogether to make that button work? (it's a drawing I will assign a script to)

CodePudding user response：

Because you're using a map function to update the range, you'll need to get the formulas using getFormulas() in addition to the display values using either getValues() or getDisplayValues(). Using only the display values, as you're currently doing, will cause you to lose the formulas when you update the sheet. Conversely, using only the formulas would cause you to lose all of the display values that don't have a formula, so you'll need both. Try this and see if does what you want:

function Test() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var range = sheet.getRange("F5:F700");  

  // Get all cell formulas and display values
  var formulas = range.getFormulas();
  var values = range.getValues();

  range.setValues(formulas.map(function(row, index) {
    var formula = row[0];
    var value = values[index][0];

    // Check only the display value
    if (value == "Check Me") {
      return ["V"];
    } else {
      // Return formula if it exists, else return value 
      return [formula || value];
    }
  }));
}