Reputation: 1
I am using this script to import an xls phone report that has some funky data structures. If I copy/paste the data, the duration is correct. If I import and format the column as duration (hh:mm:ss) it shows as a time, but the hours are increased by 3.
I need a way to automatically and correctly format the duration as duration.
function importDAILYPhoneCSVFromGmail() {
var sheetName = "Daily Phone Report"; // Please set the tab name you want to append the data.
var threads = GmailApp.search("from:[email protected] label:Phone Report Daily"); // Please set here.
var messages = threads\[0\].getMessages();
var message = messages\[messages.length - 1\];
var attachment = message.getAttachments()\[0\];
attachment.setContentTypeFromExtension(); // This might be required for your situation.
var data = \[\];
if (attachment.getContentType() == MimeType.CSV) {
data = Utilities.parseCsv(attachment.getDataAsString(), ",");
else if (attachment.getContentType() == MimeType.MICROSOFT_EXCEL || attachment.getContentType() == MimeType.MICROSOFT_EXCEL_LEGACY) {
var tempFile = Drive.Files.insert({title: "temp", mimeType: MimeType.GOOGLE_SHEETS}, attachment).id;
data = SpreadsheetApp.openById(tempFile).getSheets()\[0\].getDataRange().getValues();
Drive.Files.trash(tempFile);
- - }
if (data.length \> 0) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data\[0\].length).setValues(data);
}
}
Upvotes: 0
Views: 19