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

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.

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:

1. get the range
2. activate the range
3. get the activated range
4. 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');