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);
}
}

That is a bit of a condundrum with Google Sheets. getValue() only gets the value in the cell and it knows that formulas are not the value. Likewise getFormulas() gets the formulas as a string but if the cell contains only a value the string is blank. Now the condundrum is you can not overlay the value array with the formula array and use setValues(). You almost need to go through the formula array and put the formula if it exists in that cell as a string in the value array and then use setValues(). — TheWizEd
@TheWizEd yeah that's exactly what I was running into. Thanks for the response. — Ricky Adams
Possible duplicate of e.range.getA1Notation() unable to track changes caused by formula update — TheMaster

SRW SRW · Accepted Answer · 2018-11-15T15:08:49

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.

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

1 Answers