Date is copied as "Range" in Google Sheets Macro/Script

Question

I am struggling with a potential minor issue in google Sheets. I simply want to copy a date value to another cell, without changing the date format, etc.

However, if I save the date in a var, the Logger finds "Range". I also tried formatting, but I ended up with wrong dates, current dates, or just nothing at all.

Hint: Cell 63,4 contains a date value, formatted as a date in the spreadsheet.

function test() {
  var as = SpreadsheetApp.getActiveSpreadsheet();
  var ss = as.getActiveSheet();
  var start = ss.getRange(63,4);
  var row = start.getRow();
  var col = start.getColumn();
  Logger.log(start);
  
  start = new Date(0);
  ss.getRange('C2').setValue(start);
  
  ...
  
}

After running this code, the log shows "RANGE".

Do you have an idea how to solve this? I think I am not saving the cell correctly, but I did not find any further ideas.

soMario soMario · Accepted Answer · 2020-11-04T23:28:56

Explanation / issue:

That is because you are not getting the value and getRange returns an instance of the range class.
Keep in mind that if the value is a date you might want to use getDisplayValue().

Solution:

Replace:

var start = ss.getRange(63,4);

with:

var start = ss.getRange(63,4).getDisplayValue();

and remove that part:

start = new Date(0);

since this will overwrite the current value that is assigned to start which is the desired date you want to get.

Code snippet:

function test() {
  var as = SpreadsheetApp.getActiveSpreadsheet();
  var ss = as.getActiveSheet();
  var start = ss.getRange(63,4).getDisplayValue(); // cell D63
  ss.getRange('C2').setValue(start);  
}

Date is copied as "Range" in Google Sheets Macro/Script

1 Answers