从Big Query API导入Google Sheet的日期格式显示异常问题

我正在使用Google Workspace中的Google Drive库存报告功能，通过Apps Script将数据拉取至Google Sheet，但日期时间显示为1.61E+09格式。需要修改脚本，使日期时间显示为指定格式：2022-03-11 15:02:38.393 UTC。

当前使用的脚本如下：

function getReportFromBigQuery() {
const projectId = 'drive-inventory-464015-s6';
const query = `
SELECT id, title, mime_type as file_type, owner.user.email as owner, last_modified_time_micros as last_modified_time, create_time_micros as created_time, creator.user.email as created_by, owner.shared_drive.id as shared_drive_id
FROM drive-inventory-464015-s6.drive_inventory_reporting.inventory 
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(access.permissions) AS permission
  WHERE permission.type IN ('ANYONE')
) 
order by title asc`;
 
const request = {
query: query,
useLegacySql: false
};
 
const queryResults = BigQuery.Jobs.query(request, projectId);
const rows = queryResults.rows;
 
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("report_on_file_link_sharing") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("report_on_file_link_sharing"); sheet.clear();
sheet.appendRow(["id", "title", "file_type", "owner", "last_modified_time", "created_time", "created_by", "shared_drive_id"]);
 
for (let i = 0; i < rows.length; i++) {
sheet.appendRow([rows[i].f[0].v, rows[i].f[1].v, rows[i].f[2].v, rows[i].f[3].v, rows[i].f[4].v, rows[i].f[5].v, rows[i].f[6].v, rows[i].f[7].v]);
}
}

方法1：在BigQuery查询中直接转换日期格式（推荐） #

BigQuery支持将微秒级时间戳直接格式化为目标字符串，无需脚本额外处理，直接输出格式化后的日期：

修改后的查询语句如下，使用FORMAT_TIMESTAMP函数将微秒时间戳转换为指定格式：

SELECT 
  id, 
  title, 
  mime_type as file_type, 
  owner.user.email as owner, 
  FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%E3S UTC", TIMESTAMP_MICROS(last_modified_time_micros)) as last_modified_time,
  FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%E3S UTC", TIMESTAMP_MICROS(create_time_micros)) as created_time,
  creator.user.email as created_by, 
  owner.shared_drive.id as shared_drive_id
FROM drive-inventory-464015-s6.drive_inventory_reporting.inventory 
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(access.permissions) AS permission
  WHERE permission.type IN ('ANYONE')
) 
order by title asc

替换原脚本中的query变量内容后，直接运行原脚本即可，BigQuery会返回已经格式化好的日期字符串。

方法2：在Apps Script中处理日期格式 #

如果需要在脚本层面处理日期，可将微秒时间戳转换为Date对象后再格式化，修改循环部分代码即可：

function getReportFromBigQuery() {
const projectId = 'drive-inventory-464015-s6';
const query = `
SELECT id, title, mime_type as file_type, owner.user.email as owner, last_modified_time_micros as last_modified_time, create_time_micros as created_time, creator.user.email as created_by, owner.shared_drive.id as shared_drive_id
FROM drive-inventory-464015-s6.drive_inventory_reporting.inventory 
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(access.permissions) AS permission
  WHERE permission.type IN ('ANYONE')
) 
order by title asc`;
 
const request = {
query: query,
useLegacySql: false
};
 
const queryResults = BigQuery.Jobs.query(request, projectId);
const rows = queryResults.rows;
 
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("report_on_file_link_sharing") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("report_on_file_link_sharing"); 
sheet.clear();
sheet.appendRow(["id", "title", "file_type", "owner", "last_modified_time", "created_time", "created_by", "shared_drive_id"]);

// 定义日期格式化函数
function formatTimestamp(micros) {
  const date = new Date(micros / 1000); // 微秒转毫秒
  const year = date.getUTCFullYear();
  const month = String(date.getUTCMonth() + 1).padStart(2, '0');
  const day = String(date.getUTCDate()).padStart(2, '0');
  const hours = String(date.getUTCHours()).padStart(2, '0');
  const minutes = String(date.getUTCMinutes()).padStart(2, '0');
  const seconds = String(date.getUTCSeconds()).padStart(2, '0');
  const milliseconds = String(date.getUTCMilliseconds()).padStart(3, '0');
  return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}.${milliseconds} UTC`;
}
 
for (let i = 0; i < rows.length; i++) {
  const lastModified = formatTimestamp(rows[i].f[4].v);
  const created = formatTimestamp(rows[i].f[5].v);
  sheet.appendRow([
    rows[i].f[0].v, 
    rows[i].f[1].v, 
    rows[i].f[2].v, 
    rows[i].f[3].v, 
    lastModified, 
    created, 
    rows[i].f[6].v, 
    rows[i].f[7].v
  ]);
}
}

关键说明 #

• BigQuery返回的last_modified_time_micros和created_time_micros是微秒级时间戳，需除以1000转换为JavaScript Date对象支持的毫秒级时间戳。
• 使用getUTC*方法确保日期基于UTC时区，符合需求格式要求。
• padStart方法用于补零，保证月份、日期、时分秒为两位格式，毫秒为三位格式。

内容的提问来源于stack exchange，提问作者Mark Fraher