使用importDailyPhoneCSVFromGmail导入时长列格式异常的技术解决请求

问题描述 #

使用importDAILYPhoneCSVFromGmail脚本从Gmail导入xls格式的电话报告时，手动复制粘贴时长数据显示正常，但脚本导入后将列设置为duration（hh:mm:ss）格式时，时长会被识别为时间点且小时数多3小时，需要实现时长列的自动正确格式化。

问题原因 #

Google Sheets默认将导入的时长数据按时间点处理，结合时区偏移（如UTC+3时区）导致小时数被叠加偏移量。而手动粘贴时系统会自动识别为纯时长，因此显示正常。我们需要在导入后将时长列的数值转换为纯时长格式，抵消时区影响。

修正后的完整脚本 #

function importDAILYPhoneCSVFromGmail() {
  var sheetName = "Daily Phone Report"; // 目标工作表名称
  var threads = GmailApp.search("from:notify4@tetravx.com label:Phone Report Daily"); // Gmail搜索条件
  var durationColumnIndex = 4; // 时长列的索引（示例为第5列，数组从0开始计数）

  if (threads.length === 0) return; // 无匹配邮件时直接返回

  var messages = threads[0].getMessages();
  var message = messages[messages.length - 1];
  var attachment = message.getAttachments()[0];
  attachment.setContentTypeFromExtension();
  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);
    var startRow = sheet.getLastRow() + 1;

    // 处理时长列：将时间点转换为纯时长数值
    for (var i = 1; i < data.length; i++) { // 跳过表头行，按需调整
      var timeValue = data[i][durationColumnIndex];
      if (timeValue instanceof Date) {
        // 获取当天0点的时间对象
        var startOfDay = new Date(timeValue.getFullYear(), timeValue.getMonth(), timeValue.getDate());
        // 计算纯时长：时间点数值 - 当天0点数值（转换为Excel/Sheets的小数格式）
        var durationValue = (timeValue.getTime() - startOfDay.getTime()) / (1000 * 60 * 60 * 24);
        data[i][durationColumnIndex] = durationValue;
      }
    }

    // 写入数据并设置时长格式
    sheet.getRange(startRow, 1, data.length, data[0].length).setValues(data);
    sheet.getRange(startRow, durationColumnIndex + 1, data.length - 1).setNumberFormat("[h]:mm:ss");
  }
}

关键说明 #

1. 语法修复：补全原脚本中缺失的大括号，修正错误的-- }语法
2. 时长列配置：根据实际表格调整durationColumnIndex值（比如时长列是第3列则设为2）
3. 时长转换逻辑：通过计算时间点与当天0点的差值，得到纯时长的小数数值（Google Sheets中1天=1，1小时=1/24）
4. 格式设置：使用[h]:mm:ss格式支持超过24小时的时长，若仅需常规格式可改为hh:mm:ss

内容的提问来源于stack exchange，提问作者Alan Stevens