Alan Stevens
Alan Stevens

Reputation: 1

Using importDailyPhoneCSVFromGmail, columns with durations import as a date

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

Answers (0)

Related Questions