A Script to Convert Google Sheets Dates to Salesforce DateTime Format

If you are a Salesforce user, you have probably faced the need to convert dates data to ISO format before importing the data to SFDC. The ISO DateTime format that Salesforce uses is structured in this format: yyyy-mm-dd”T”hh:mm:ss”+03:00″

• yyyy = full year number
• mm = 2-digits month
• dd = 2-digits day of the month
• T = symbols the beginning of the Time in the DateTime string
• hh = hour
• mm = minutes
• ss = seconds
• +03:00 (or the relevant number for you) = GMT + the time zone you are at

While converting from ISO to a readable date in Google Sheets is pretty easy, the other way around is not much so. There are a few ways to convert a date to ISO that can be done manually, but honestly, I found that boring… So instead, I wrote a short script that takes a column of dates and returns the ISO DateTime so I can easily apply it on a file and upload it to Salesforce. 

The basic date conversion is done with this simple function:

function convertDateToIso(date){
 date = new Date(date)
 date = Utilities.formatDate(date, "GMT+3","yyyy-MM-dd'T'HH:mm:ss'Z'")
 return date;
}

The function takes a date, creates an instance of a date object, and then with the Utilities class, converts the date to an ISO string. Note that the formatDate accepts 3 parameters: the date we wish to convert, the timeZone, and the new Date format. Here I input a timezone which is plus 3 hours from the Greenwich Mean Time. 

And here is the code used in another function that takes a column of dates, and returns a column of ISO-formatted DateTime values on the column next to it. These values can be then used when uploading data to Salesforce:

// The function takes a column with dates and appends the ISO DateTime to the column next to it
function setIsoDate() {
 let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 let dates_colomn = 1; // change according to your sheet
 let max_rows = sheet.getMaxRows();
 
 let dates = sheet.getRange(1, dates_colomn,max_rows).getValues();
 let iso_dates = [];
  for (let i=0; i<dates.length; i++){
   let date_val = dates[i];
   date_val = convertDateToIso(date_val)
   iso_dates.push([date_val])
 }
 sheet.getRange(1, dates_colomn+1, max_rows).setValues(iso_dates)
}
 
// A function that takes a date and converts it to ISO DateTime format
function convertDateToIso(date){
 date = new Date(date)
 date = Utilities.formatDate(date, "GMT+3","yyyy-MM-dd'T'HH:mm:ss'Z'")
 return date;
}