Google Script .getvalue() Not Working With Cells With a Formula In It

Question

I have this google script for google sheets that moves rows of data from "Sheet1" to "Sheet2" when column 15 says "tracking", and it works perfectly fine when I type in "tracking" but I would like that column to be an IF equation something like IF(G:G="tracking not available at this time","","tracking"). But the code does not seem to recognize the formula change from "" to "tracking". Do I need to change the getvalue()? Or is there a different workaround to this issue? I've used =query(importrange) withing the spreadsheet to copy over data with a trigger word, but I really want this to be more of an archive system and add a row to the bottom of "Sheet2" whenever row15 on "sheet1"Thanks! Here is the code:

function onEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if(s.getName() == "Sheet1" && r.getColumn() == 14 && r.getValue() == "tracking") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Sheet2");
if(targetSheet.getLastRow() == targetSheet.getMaxRows()) {

  targetSheet.insertRowsAfter(targetSheet.getLastRow(), 20);
}
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
} 

Answer 1

I had an issue with this recently I spent about 3 hours debugging something yesterday and this was the culprit.

try using r.getDisplayValue() instead of r.getValue

I am still new to this myself, and feel free to correct me if I am wrong, because if there is a different reason I would really love to know!!! It seems that if a value in a cell is not typed in but placed there through a formula such as =query() or a similar method, I don't think it actually sees that there is a value in the cell. (I got null values or the formula itself) If you use getDisplayValue, it "should" get the value that you actually see in the cell.

Answer 2

The correct way to get formulas, instead of displayed values, is with getFormulas rather than getValues

Answer 3

What worked for me was:

1. I saved spreadsheet as a named version (file > version history > name current version > enter name and save)
2. Then restored the spreadsheet to an older version (file > version history > see version history > select a version from an earlier date > click restore)
3. Finally restored back the named version you saved in earlier.

getValue() started seeing the formula populated values I needed it to see after following these 3 steps.