How can I getValue in actual date format?

Question

I am running into an issue with getValue and the format it gives back in Google Sheets. The value in the cell is 2/19/2019 in format M/dd/yyyy in sheets but it is giving back =Tue Feb 19 2019 00:00:00 GMT-0600 (CST) (picture attached of issue). I am curious on how I can get the cell I am trying to fill to be the same format and look the same as 2/19/2019. Here is my code I am currently working on:

function fillDown() {
  var ss = SpreadsheetApp.openById('spreadsheetId');
  var sheet = ss.getSheetByName("spreadsheetName");
  var originRange = sheet.getRange(sheet.getLastRow(), 1);
  var targetRange = sheet.getRange(sheet.getLastRow()+1, 1)
  var formula = originRange.getValue();
  targetRange.activate();
  sheet.getCurrentCell().setFormula(formula);
}

https://stackoverflow.com/questions/24894648/get-today-date-in-google-appscript did you see that? — Péttrin Miranda, Feb 19 '19 at 21:27
@PéttrinMiranda Yes, that is specific to a cell's location where I am trying to add it to the end of the sheet, regardless of location. I was a little confused on merging these two ideas together, so I needed a little bit more help. — Bryce Sinclair, Feb 20 '19 at 15:24

score 0 · Answer 1 · answered Feb 19 '19 at 21:49

It looks like you are trying to set a formula as the Date instead of the value of the cell. getValue() gives back the actual value of the cell, not the formatted version. A formula is different from both of these. If you want to set a date to a certain value and have it display in a specific, just use setValue(date) and format the cell.

var formula = originRange.getValue();
targetRange.activate();
// sheet.getCurrentCell().setFormula(formula); this results in the cell containing '=<date>'
sheet.getCurrentCell().setValue(date);

--> Make sure you format the whole column how you want and it'll display appropriately.

In summary, setFormula() does not equal setting the format.

Cooper · Accepted Answer · 2019-02-19T21:56:55.240

0

You could do something like this:

function fillDown() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var originRange = sheet.getRange(sheet.getLastRow(), 1);
  var targetRange = sheet.getRange(sheet.getLastRow()+1, 1)
  var value = originRange.getValue();
  var format = originRange.getNumberFormat()
  targetRange.activate();
  sheet.getCurrentCell().setValue(value);
  sheet.getCurrentCell().setNumberFormat(format);
}

edited Feb 19 '19 at 21:56

answered Feb 19 '19 at 21:51

Cooper

59,616
6
23
54

Thanks, that worked perfectly! I am curious if there is a way I can do that, but then have the targetRange be +1 (sorry if that's the wrong way to ask)? For example, instead of setting the cell to 2/19/2019, it would set it to 2/20/2019? My main goal behind this script is to pretty much add today's date into the last row, the targetCell. – Bryce Sinclair Feb 20 '19 at 14:55
1

If that was the main goal to this script, then why wasn't it in the question? – Cooper Feb 20 '19 at 16:24

How can I getValue in actual date format?

2 Answers2