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

Pileus

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(). – Eiffel

@Eiffel yeah that's exactly what I was running into. Thanks for the response. – Pileus

Possible duplicate of e.range.getA1Notation() unable to track changes caused by formula update – Maibach

@ThWizEd That's not what op is asking. – Maibach

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.

Mouthpiece

thanks for the feedback! I tried playing around with the getDisplayValue(), but that didn't seem to work. It still doesn't register it to copy it over to the – Pileus

Something else you could try. An extra space in the cell whether it is at the start or at the end can also cause trouble when reading the value. – Mouthpiece

Answer 2

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.

Supple

Answer 3

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

Muscarine