CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Julz
Julz

Reputation: 69

How to insert date into YYYY-MM-DD plain text into Google Spreadsheet with Apps Script

Essentially I would like to have a YYYY-MM-DD format inserted into a cell on a new spreadsheet when it is created as plain text. The trouble is that when the inserted value is inserted into the new sheet it appears as DD/MM/YYYY which is unreadable by the other program that is running in the background.

Here is the following code: 

function dateTest() 
{
var template = SpreadsheetApp.openById('ABC------1234')
var test = template.getSheetByName('TEST');
var database = SpreadsheetApp.getActive().getSheetByName('DASHBOARD');

//start & endDates are in YYYY-MM-DD format in these cells
var startDate = database.getRange('A10').getValue();
var endDate = database.getRange('A11').getValue();

//The results come up as DD/MM/YYYY when they are set into TEST
//Formatted to YYYY-MM-DD has already been done 
test.getRange('D1').setValue(startDate);
test.getRange('D2').setValue(endDate);

}

If necessary, the date could be split into YYYY + '-' + 'MM' + '-' + 'DD' and then inserted as plain text, however I am clueless as to how to perform this feat. Any advise would be very much appreciated.

Upvotes: 2

Views: 4935

Answers (4)

ScampMichael
ScampMichael

Reputation: 3728

It can be cleaned up but here are the basics.

//start & endDates are in YYYY-MM-DD format in these cells
var startDate = database.getRange('A10').getValue();
var strStartDate = startDate.getFullYear() + "-" + (startDate.getMonth() + 1) + "-" + startDate.getDate();
var endDate = database.getRange('A11').getValue();
var strEndDate = endDate.getFullYear() + "-" + (endDate.getMonth() + 1) + "-" + endDate.getDate();

//The results come up as DD/MM/YYYY when they are set into TEST
//Formatted to YYYY-MM-DD has already been done 
test.getRange('D1').setNumberFormat('@STRING@');
test.getRange('D1').setValue(strStartDate);

test.getRange('D2').setNumberFormat('@STRING@');
test.getRange('D2').setValue(strEndDate);

Upvotes: 4

Corey G
Corey G

Reputation: 7858

In Google Spreadsheets, a leading apostrophe means ignore formatting. Thus, this works:

range.setValue("'2007-09-12");

Alternatively, the text can be quotes and set as a "formula":

range.setFormula('"2007-09-12"');

(Make sure the string includes double quotes not single quotes - Apps Script is JavaScript and therefore supports either, but spreadsheet formulas don't understand single quotes the same way.)

Upvotes: 3

Bryan P
Bryan P

Reputation: 5051

This will reformat the date and set it as string:

  var tz = ss.getSpreadsheetTimeZone();
  var newDate = Utilities.formatDate(startDate, tz, 'yyyy-MM-dd'); // Requested format
  s.getRange('D1').setValue("'" + newDate);  // Set as string

Upvotes: 0

Marcello Scacchetti
Marcello Scacchetti

Reputation: 145

Try this workaround: in the sheet manually set a cell format to "Plain Text" that is not involved in the operation. Let's call this cell J1. Now before calling:

test.getRange('D1').setValue(startDate);
test.getRange('D2').setValue(endDate);

Do something like:

test.getRange('J1').copyFormatToRange(test,3,3,0,1); // This is D1 and D2
test.getRange('D1').setValue(startDate);
test.getRange('D2').setValue(endDate);

This should let you have clear text format on destination cells.

Upvotes: 0

Related Questions