CODEWITHSUNDEEP
google-apps-script
ben.oliver
ben.oliver

Reputation: 27

getValue formatting is off when pulling a date from spreadsheet

I'm using a script to convert a spreadsheet to a PDF.

To define the title of the PDF, I'm pulling in data from a few specific cells, one of which contains a date. The problem is, the date comes out in a long format in the title. Here's a chunk of my code:

  var ss = SpreadsheetApp.openById("XXXXXXXXXXXXXXXXXXXXXXXXXXXXX"); //       Opens spreadsheet to pull name from, by ID.
  var sheet = ss.getSheetByName('PurchaseOrderForm'); // Sets the sheet
  var ponumber = sheet.getRange("G3:G3"); // Define ranges/cells here + below.
  var date = sheet.getRange("G2:G2") // Custom cell to pull from
  var companyname = sheet.getRange("A11:A11") // These are then added together in line 30

  var name = ponumber.getValue()+" | "+date.getValue()+" | "+companyname.getValue()+".pdf"; // Output of the final PDF name. A composition of the above vars, plus some custom text.

This creates the PDF name almost as I want it, except for the date part. This is an example of the PDF titles I get:

14PO051 | Tue Jan 28 2014 00:00:00 GMT-0000 (GMT) | Micronclean.pdf

The date comes out, but not in a format I want/need. In the spreadsheet the date cell, G2, is formatted DD/MM/YY. I need something like this to come out in my title, otherwise it's just too long to be of any use.

If anyone knows how to get it down to DD/MM/YY or equivalent, I'd be grateful to know!

One workaround would be to not have the dates formatted, and just manually input them into the spreadsheet. I hope it doesn't come to that, my colleagues like not having to type the whole thing out.

Upvotes: 2

Views: 9144

Answers (2)

Bryan Blackford
Bryan Blackford

Reputation: 196

To get the values from the sheet as they appear (using sheet formatting), use getDisplayValue or getDisplayValues

https://developers.google.com/apps-script/reference/spreadsheet/range#getdisplayvalues

(use the plural function anytime you're getting more than 1 cell, if possible; batch ops are faster!)

Upvotes: 6

Bryan P
Bryan P

Reputation: 5051

var date = Utilities.formatDate(sheet.getRange("G2:G2").getValue(), ss.getSpreadsheetTimeZone(), "dd/MM/YY");

?

Upvotes: 5

Related Questions