GOOGLE script 'copyTo values only' does not work when the source is a function (e.g. NOW())

Question

I am trying to copy the values only from a cell containing the function NOW(). For everything I try the target cell is empty :-(

I have tried:

  spreadsheet.getRange('K1').activate();
  spreadsheet.getCurrentCell().setValue('Erstellt am:');
  spreadsheet.getRange('P1').activate();
  spreadsheet.getCurrentCell().setFormula('=NOW()');

  spreadsheet.getRange('N1').activate();
  spreadsheet.getRange('P1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getActiveRangeList().setNumberFormat('dd.MM.yyyy HH:mm:ss');

The result is that cell N1 is empty. If I add the statement:

spreadsheet.getCurrentCell().setValue('OTTO');

Then the cell N1 gets the value OTTO as expected.

I also tried outsourcing the logic into a separate function like this:

function COPY_DATE() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('N1').activate();
  spreadsheet.getRange('P1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getActiveRangeList().setNumberFormat('dd.MM.yyyy HH:mm:ss');  
};

This did not work either.

I also tried this way:

var source = spreadsheet.getRange ('P1');                                    
source.copyTo (spreadsheet.getRange ('N1'), {contentsOnly:true});

Everything to no avail. I would really like to know what's going on here and appreciate any feedback.

Are you getting an error? – Andres Duarte Nov 12 '19 at 14:55 — Andres Duarte, Nov 12 '19 at 14:55

score 1 · Accepted Answer · answered Nov 12 '19 at 03:26

To be honest, I have no idea why SpreadsheetApp.CopyPasteType.PASTE_VALUES isn't working here.

Instead of doing that, try a different approach: use getValue() and setValue().

spreadsheet.getRange('N1').setValue(spreadsheet.getRange('P1').getValue())

I'm not sure if it makes sense for your purposes, but I'd also suggest you look at removing a lot of those .activate() calls. As I wrote in this answer, activation is basically just for interacting with a user selection, and it can slow down the execution. The way you're doing it, you're calling the Spreadsheet service 4 times:

get the range
activate the range
get the activated range
do something to the activated ranged

You can halve those calls by simply getting the range and then perform whatever actions you need on it. Then you could simplify your code to something like this:

spreadsheet.getRange('K1').setValue('Erstellt am:');
var value = spreadsheet.getRange('P1').setFormula('=NOW()').getValue();
spreadsheet.getRange('N1').setValue(value).setNumberFormat('dd.MM.yyyy HH:mm:ss');

Very much thanks for your answer Diego. I tried your approach and it works :-) Also thanks foryour comments. As I am a complety newby to script development your hints are much appreciated. — mortpiedra, Nov 15 '19 at 17:21
@mortpiedra Good to hear. If the answer worked for you, please consider marking it as accepted. — Diego, Nov 16 '19 at 00:06

score 1 · Answer 2 · answered Nov 12 '19 at 14:56

I tested the following code and is working perfect setting the date value from P1 cell to N1 cell:

function COPY_DATE() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('N2').activate();
  spreadsheet.getRange('P1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getActiveRangeList().setNumberFormat('dd.MM.yyyy HH:mm:ss');  
};

Remember that to use getActive() type of functions you need to have the script bound to the Spreadsheet [1].

[1] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getactive

GOOGLE script 'copyTo values only' does not work when the source is a function (e.g. NOW())

2 Answers2

Linked