1

I am trying to create a script on Google Sheets to receive a message alert whenever the value of the B10 cell increases. Something like this:

 Static oldVal As Variant
 oldVal = Me.Range("B10").Value
 If Me.Range("B10").Value > oldVal Then
Browser.msgBox('hello world');
End If

There is a way?

Marcio Mathias
  • 11
  • 2

1 Answers1

0

Other than your syntax being way off (Apps Script is based on JavaScript), yes, you can do that using a couple different mechanisms.

Use the onEdit(e) simple trigger to watch the sheet.

The e object has an oldValue and a value key you can compare directly. You can also test for a specific cell reference using the range parameter. The following tests for cell B10, specifically:

function onEdit(e) {
  if (e.range.getA1Notation() == 'B10' && e.value > e.oldValue) {
    Browser.msgBox("you increased the value")
  }
}

Taking it even farther, you can specify a sheet within a Spreadsheet to test before the script will run.

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (e.range.getA1Notation() == 'B10' && e.range.getSheet().getName() == 'Página1' && e.value > e.oldValue) {
    Browser.msgBox("you increased the value")
  }
}

If the cell is not in the specified sheet or range,, nothing happens.

Watching a formula cell is a little more complex, but it will work. There are two methods in Apps Script. getValue() returns the value displayed in the cell, even if it's calculated by a formula. .getFormula() gets the cell's calculating formula. So, yes, this will work with simply calling .getValue() on the range.

The major difference is that you have to watch the formula cell because it is not directly edited by the user. In other words, the event object will not trigger if the cell value is calculated by a formula. Including it is easy:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var formulaRange = e.range.getSheet().getRange("B4");

  if (e.range.getA1Notation() == 'B10' || formulaRange && (e.range.getSheet().getName() == 'Página1' && e.value > e.oldValue) {
    Browser.msgBox("you increased the value")
  }
}

More on Apps Script event objects in the documentation.

Brian
  • 4,274
  • 2
  • 27
  • 55
  • You should probably alter this to be more indicative of a general purpose edit changing the value of b10, e.g. b10 is a formula. Traditional `on edit` functions will only report `oldValue` for the actual edited range. I believe the VBA that is to be ported applies to all value edits. – tehhowch May 03 '18 at 17:47
  • @tehhowch I didn't see his followup. The question, at first, seemed to be asking a very general application and didn't really seem to me that he wanted a specific cell tested. – Brian May 04 '18 at 00:11
  • @Brian and if b10 is a formula as he said above? It's possible to make this alert? e.g: b10 has a COUNTIF function. – Marcio Mathias May 04 '18 at 12:01
  • @MarcioMathias something like what I do in [this answer](https://stackoverflow.com/a/50166892/9337071) is necessary when the target cell is only changed via a formula. For data storage of a single cell's value, something like `PropertiesService` is a feasible alternative to using a "backup" worksheet. – tehhowch May 04 '18 at 16:10
  • There is a `.getFormula()` method which returns the cell's formula. `.getValue()` returns the displayed value, so you can test with the same logic. You just need to watch the formula range in the logic with an `OR` conditional on the range test. My answer has been updated. @MarcioMathias – Brian May 04 '18 at 17:21