Hoping this is a simple problem for you lot. I have no coding knowledge at all. But been using the below script in a Google Sheet to grab changing data from another sheet and log it daily, appending as it goes. Can't remember where I found the script - if I did I'd go back and ask its creator. It's been working fine; only thing is I have to manually copy paste my preferred date format every day. So I'd like the script to print the date in "dd/MM/yyyy" format (while retaining hours and minutes data inside the cell). I've been reading and searching online, and experimenting for ages but can't figure it out. This is the base code:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("History");
  var source = sheet.getRange("A2:C2");
  var values = source.getValues();
  values[0][0] = new Date();
  sheet.appendRow(values[0]);
};

I've tried placing setnumberformat in various places and nearly always get an error. Perhaps my best attempt, inspired by other examples I've seen, was to add these new lines:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("History");
  var source = sheet.getRange("A2:C2");
  var values = source.getValues();
  values[0][0] = new Date();
  sheet.appendRow(values[0]);
  var cell = SpreadsheetApp.getActiveSheet().getRange(2, 1, 979);
  cell.setNumberFormat("dd/MM/yyyy");
};

I hoped this would format the entire date row (Row A) after appending the new data. Probably a clunky solution even if it worked, but it didn't. It's my only attempt so far that doesn't return an error! So, yay? But it doesn't change the date format. So I give up. Any ideas?

Hoping this is a simple problem for you lot. I have no coding knowledge at all. But been using the below script in a Google Sheet to grab changing data from another sheet and log it daily, appending as it goes. Can't remember where I found the script - if I did I'd go back and ask its creator. It's been working fine; only thing is I have to manually copy paste my preferred date format every day. So I'd like the script to print the date in "dd/MM/yyyy" format (while retaining hours and minutes data inside the cell). I've been reading and searching online, and experimenting for ages but can't figure it out. This is the base code:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("History");
  var source = sheet.getRange("A2:C2");
  var values = source.getValues();
  values[0][0] = new Date();
  sheet.appendRow(values[0]);
};

I've tried placing setnumberformat in various places and nearly always get an error. Perhaps my best attempt, inspired by other examples I've seen, was to add these new lines:

function recordHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("History");
  var source = sheet.getRange("A2:C2");
  var values = source.getValues();
  values[0][0] = new Date();
  sheet.appendRow(values[0]);
  var cell = SpreadsheetApp.getActiveSheet().getRange(2, 1, 979);
  cell.setNumberFormat("dd/MM/yyyy");
};

I hoped this would format the entire date row (Row A) after appending the new data. Probably a clunky solution even if it worked, but it didn't. It's my only attempt so far that doesn't return an error! So, yay? But it doesn't change the date format. So I give up. Any ideas?

• javascript
• datetime
• google-apps-script
• google-sheets
• date-formatting

2 Answers 2

To specify the format for a specific cell

var cell = sheet.getRange("A2");
cell.setNumberFormat("dd/MM/yyyy");

To specify the format for a range of cells

var cells = sheet.getRange("A2:C2");
cells.setNumberFormat("dd/MM/yyyy");

Please see the documentation for Range and formats.

Just in case you need the time as well, follow this code.

var cell = sheet.getRange("A2");
cell.setNumberFormat("dd/MM/yyyy h:mm:ss AM/PM");