0

I currently have a formula in cell H9 & H10 that is testing certain cells for a Regular Expression with =REGEXMATCH and I would like the above function to run only when there is a TRUE value in these cells. However, I have tried all of the formats below and the function still runs regardless of whether the value is true or false.

function CoordFormat() {
  var ss = SpreadsheetApp.getActive().getSheetByName('FORM');
  var regex1 = ss.getRange('H9')
  var regex2 = ss.getRange('H10')
  var lat = ss.getRange('C20');  
  var long = ss.getRange('C21');

  if (regex1 == true){
    lat.setFormula('=IF(C9>500000,(LEFT($C$9,2)*10000)+(MID($C$9,4,2)*100)+MID($C$9,7,4),"")')
  } else {
    SpreadsheetApp.getUi().alert("Grid Reference is not in DSM format!");
  }

  if (regex2 == true){
    long.setFormula('=IF(C10>500000,(LEFT($C$10,1)*10000)+(MID($C$10,3,2)*100)+MID($C$10,6,2),"")')
  } else {}
}

function CoordFormat() {
  var ss = SpreadsheetApp.getActive().getSheetByName('FORM');
  var regex1 = ss.getRange('H9')
  var regex2 = ss.getRange('H10')
  var lat = ss.getRange('C20');  
  var long = ss.getRange('C21');

  if (regex1){
    lat.setFormula('=IF(C9>500000,(LEFT($C$9,2)*10000)+(MID($C$9,4,2)*100)+MID($C$9,7,4),"")')
  } else {
    SpreadsheetApp.getUi().alert("Grid Reference is not in DSM format!");
  }

  if (regex2){
    long.setFormula('=IF(C10>500000,(LEFT($C$10,1)*10000)+(MID($C$10,3,2)*100)+MID($C$10,6,2),"")')
  } else {}
}

function CoordFormat() {
  var ss = SpreadsheetApp.getActive().getSheetByName('FORM');
  var regex1 = ss.getRange('H9')
  var regex2 = ss.getRange('H10')
  var regex1tr = (regex1 = true)
  var regex2tr = (regex2 = true)
  var lat = ss.getRange('C20');  
  var long = ss.getRange('C21');

  if (regex1tr){
    lat.setFormula('=IF(C9>500000,(LEFT($C$9,2)*10000)+(MID($C$9,4,2)*100)+MID($C$9,7,4),"")')
  } else {
    SpreadsheetApp.getUi().alert("Grid Reference is not in DSM format!");
  }

  if (regex2tr){
    long.setFormula('=IF(C10>500000,(LEFT($C$10,1)*10000)+(MID($C$10,3,2)*100)+MID($C$10,6,2),"")')
  } else {}
}

Not sure whether the format of true is correct and whether it should be bounded by apostrophes or If this would test for a text value?

Any help appreciated!

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
Xanthippus480
  • 7
  • 4
  • from `var regex1 = ss.getRange('H9')` to `var regex1 = ss.getRange('H9').getValue()` and the same for `H10` – Marios Mar 18 '22 at 10:58
  • right now you get a range object, not the value of the cell. So you have to use `getValue` in every statement you want to get the value of the cell. – Marios Mar 18 '22 at 10:58
  • Ah I see, that seems obvious now you've said it - many thanks. – Xanthippus480 Mar 18 '22 at 12:56

0 Answers0