Date format showing incorrectly in Google sheet from Big Query API

Im using the Google Drive inventory reporting in Google workspace. Im using Apps Script to pull the data into a Google sheet, but the date and time is coming into the sheet in this format: 1.61E+09

See attached pic of the sheet.

Is there a way to modify the script so that the date time shows in the correct format of 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]);
}
}

From Is there a way to modify the script so that the date time shows in the correct format of 2022-03-11 15:02:38.393 UTC of your question, I guessed that the values of rows[i].f[4].v and rows[i].f[5].v might be the number values like 1647010958.393. If my understanding is correct, how about the following modification?

Modified script:

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);

  // --- I modified the below script.
  const rows = [
    ["id", "title", "file_type", "owner", "last_modified_time", "created_time", "created_by", "shared_drive_id"],
    ...queryResults.rows.map(({ f }) => f.map(({ v }, j) => [4, 5].includes(j) ? new Date(v * 1000) : v))
  ];
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("report_on_file_link_sharing") || ss.insertSheet("report_on_file_link_sharing");
  sheet.clear().getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
  
  // ss.setSpreadsheetTimeZone("UTC");
}

In this modification, the numeric values of columns "E" and "F" are converted to the date object. And, the rows including the header row are put into Google Sheets using setValues. When appendRow is used in a loop, the process cost becomes high.

Note:

• About shows in the correct format of 2022-03-11 15:02:38.393 UTC, if you want to show it with UTC, please add ss.setSpreadsheetTimeZone("UTC"); to the last line of the script and test it again.

References:

• map()
• setValues(values) of Class Range