rlphyg
rlphyg

Reputation: 3

Removing time and timezone from the date retrieved from Google Sheets

I have a Google Sheets spreadsheet linked up with a form on a Squarespace site. There is a script I had found and edited to my needs attached to the sheet that automatically populates and sends an email based on new information entered into the spreadsheet via the form.

Google seems to add "00:00:00 GMT+0100 (BST)" to the end of every date on the email when it's sent, is there any way to stop this occuring? All I need is the day, month, and year as thats all the client enters on the form.

Code below:

function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var numCurrent = sheet.getRange(2,10).getValue();
var numOld = sheet.getRange(2,11).getValue();
var numRecord = numCurrent+1;
// MailApp.sendEmail("[email protected]", "Debug", "Current subscribers: " + numCurrent + "Old subscribers: " + numOld);
if (numCurrent > numOld){
 var strSubject = sheet.getRange(numRecord,4).getValue();
 var strVenue = sheet.getRange(numRecord,5).getValue();
 var strEnquirerEmail = sheet.getRange(numRecord,3).getValue();
 var strEnquirerName = sheet.getRange(numRecord,2).getValue();
 var strEnquirerMessage = sheet.getRange(numRecord,6).getValue();
 var strMessageBody = "Name: " + strEnquirerName + "\n\n Email: " + strEnquirerEmail + "\n\n Venue: " + strVenue + "\n\n Event Date: " + strSubject + "\n\n Message: " + strEnquirerMessage; 
 MailApp.sendEmail("[email protected]", strEnquirerEmail, "Event Date: " + strSubject, strMessageBody);
 sheet.getRange(2,11).setValue(numCurrent);
}
}

Upvotes: 0

Views: 3596

Answers (1)

user3717023
user3717023

Reputation:

To have the date displayed in the format you prefer, use Utilities.formatDate method. Example:

var strSubject = Utilities.formatDate(sheet.getRange(numRecord,4).getValue(), Session.getScriptTimeZone(), "yyyy-MM-dd");

This formats the date as 2016-07-20, using the current timezone. Examples of other formats can be found on SimpleDateFormat page.

Upvotes: 1

Related Questions