I have a script that converts and Excel file to a Google Sheet. However, the newly created Google Sheet defaults to the Pacific timezone. We already have our Company set to Eastern timezone in Google Admin. I also have the local machine that is executing the script set to Eastern timezone and the user is also set to Eastern timezone. The script project is set to Eastern timezone as well. If I goto Google Apps and create a sheet from scratch, it defaults to Eastern timezone as desired. But when the script creates the new sheet, it always defaults to Pacific timezone. Since it seems there's no way to set the timezone via a script function, is there anyway to make it default to Eastern timezone? Note as well that here in the Eastern timezone, we have the stupid daylight savings time. So it changes between -4 and -5, which is such a pain!
My reason for this is that after I convert the Excel to Google Sheet, I have to import the records into another import sheet. The imported data has dates and times that keep getting sku'd after or during the import. Since I import using the getRange.getValues function, it always performs the new Date(). I have tried the Utilities.format function as well. But I don't know if I can be sure that the timezone will stay the same. And since I can't read what timezone the created sheet has, it becomes a gamble.
I would appreciate any help that could be offered in the way of scripting fixes. It is not feasible for me to have to go in each time and change the timezone manually within the sheet itself.
Thanks for any help!
Doug
The conversion code:
function convertExceltoGoogleSheet(fileName, newFileName) {
try {
var fileName = fileName || "microsoft-excel.xlsx";
var excelFile = DriveApp.getFilesByName(fileName).next();
var fileId = excelFile.getId();
var folderId = Drive.Files.get(fileId).parents[0].id;
var blob = excelFile.getBlob();
//If no newFileName passed, create one
if (newFileName.trim() == '') {
//Generate formatted filename
var dateInfo = getDateInfo(-20);
var mo = dateInfo[2].toString();
if (mo.length < 2) {
var mo = "0" + mo;
}
var newFileName = dateInfo[0].toString() + "-" + mo + "-" + filename;
}
var resource = {
//title: excelFile.getName().replace(/.xlsx?/, ""), //use original filename
title: newFileName, //Use formatted filename from above
key: fileId,
parents: [{"id": folderId}]
};
var newFile = Drive.Files.insert(resource, blob, {
convert: true
});
return newFile.id;
} catch (f) {
Logger.log(f.toString());
}
}