Javascript in Google Sheets script: help using setNumberFormat

Question

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?

Sync · Accepted Answer · 2018-02-09 12:48:54Z

3

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.

answered Feb 9, 2018 at 12:48

Sync

3,81725 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Md Sefat Ullah · Accepted Answer · 2022-12-19 04:48:22Z

1

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");

answered Dec 19, 2022 at 4:48

Md Sefat Ullah

111 bronze badge

Collectives™ on Stack Overflow

Javascript in Google Sheets script: help using setNumberFormat

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related