How to get fixed cell value, not formula?

Question

I have a cell in Google Sheets, with =NOW() formula in it. All I need - to get the value, generated by formula (22.01.2014 15:23:51), not the formula by itself.

If I try: var nowtime = SpreadsheetApp.getActiveSheet().getRange('A1').getValues();,
I get ={DATE(2014\1\22)+TIME(15\19\13)}, but not the numbers.

How to do it with Google Script syntax?

Answer 1

I know I'm a little late to the party here, but you could try .getDisplayValue() instead of .getValue() or .getDisplayValues() instead of .getValues() That will give you the result of the formula instead of the formula itself.

I just had the same problem and the above solution worked for me.

Answer 2

Use this little snippet to do that.

Code

function getCell(startcol, startrow) {
  // prepare string
  var str = String.fromCharCode(64 + startcol) + startrow;

  // retrieve value
  var vCell = SpreadsheetApp.getActive().getRange(str).getValue();

  // return content cell
  return vCell;  
}

Screenshot

Explained

The first parameter of the custom function passes on the column index, as an integer. The var str creates from the column integer and the row index a string (A1 notation). Then the value is retrieved and returned to the spreadsheet.

This piece of code will retrieve any value.

Add the script under Tools>Script editor. Press the "bug" button to validate the script and you're on the go !!