How to check for cell value?

Question

I have a really simple script that is run when the sheet is edited (below). There is data validation on my cell c6 to allow only Yes or Cancel which once selected, causes the script to run. I would like the macro to check if the value Yes has been selected and if so, run the module CopyDownNews. My issue is that CopyDownNews runs every time irrespective of what is selected in C6. Any ideas?

function changeMade() {

var checkForNews = spreadsheetApp.getActiveSpreadsheet().getSheetByName("Tracking").getRange("c6");

if (checkForNews='Yes') {
  
  CopyDownNews();

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tracking").getRange("c6").clearContent();
  
};  

}

score 0 · Answer 1 · answered Nov 14 '21 at 09:24

0

You must check for getValue()

var checkForNews = spreadsheetApp.getActiveSpreadsheet().getSheetByName("Tracking").getRange("c6").getValue();

answered Nov 14 '21 at 09:24

Mike Steelson

14,650
2
5
20

1

Thanks Mike, have updated but it still makes the same error. I've added an extra line of code to copy the variable checkForNews onto the sheet and each time, the value is always Yes which explains why the code runs but strange given when i test the code, the cell is either blank or contains another value. could it be taking a default value from the data validation? – Chris McKenna Nov 14 '21 at 10:32
Figured it out. Added a double equals: if (checkForNews=='Yes') and it works. Not sure why though but I'll take it! I'm sure i tried that before though so probably only works now due to your tip. Thanks again! – Chris McKenna Nov 14 '21 at 10:45
In apps script, a single equals symbol *sets* a value, while a double equals symbol *checks* a value. (There is a triple equals symbol as well, but it doesn't pertain to what you're doing.) – Erik Tyler Nov 15 '21 at 02:38

score 0 · Answer 2 · answered Nov 15 '21 at 09:13

Posting this for documentation purposes:

Issues:

As MikeSteelson said, you have to retrieve the value of the range, not the range itself. Use Range.getValue().
It's always entering the if block because you added an assignment operator (=), while you should be using a comparison operator (==, ===).

Code snippet:

function changeMade() {
  var range = spreadsheetApp.getActiveSpreadsheet().getSheetByName("Tracking").getRange("c6");
  var checkForNews = range.getValue();
  if (checkForNews==='Yes') {
    CopyDownNews();
    range.clearContent();
  };  
}

How to check for cell value?

2 Answers2

Issues:

Code snippet:

Linked