IssueTracker

Before opening a new issue, please search for other related issues, click the ★ to subscribe to updates, and click +1 to vote.

Description

Even using Europe/Warsaw time zone of the script and project, when trying to create Google Sheet by Drive.Files.create( { title: Temp_${freshestFile.getName()}, mimeType: MimeType.GOOGLE_SHEETS }, blob );

I have it always by default in Los Angeles time zone.

Code

function appendDataFromFreshestSheet() {

var scriptTimeZone = Session.getScriptTimeZone();

Logger.log("Running Script to save gmail attachment...");

SaveGmailAttachment.saveAttachmentsToDrive();

const targetSpreadsheetId = "1d4L1xY0lzv_G1RmvIN9zNdjAEiNdrh5H4OlWn0gk-QQ"; // Target Google Sheet ID const targetSheetName = "Data"; // Target sheet name const folderNameOrId = "1Ie3-AVKTHEy8_-UcUJLBKcNrJjB0K6i3"; // Folder name or ID

Logger.log(Target Spreadsheet ID: ${targetSpreadsheetId}); Logger.log(Target Sheet Name: ${targetSheetName});

let folder; try { folder = DriveApp.getFolderById(folderNameOrId); } catch (e) { const folders = DriveApp.getFoldersByName(folderNameOrId); if (folders.hasNext()) { folder = folders.next(); } else { throw new Error(Folder with name or ID "${folderNameOrId}" not found.); } }

const files = folder.getFilesByType(MimeType.MICROSOFT_EXCEL); let freshestFile = null; let latestModifiedTime = 0;

while (files.hasNext()) { const file = files.next(); if (file.getLastUpdated().getTime() > latestModifiedTime) { freshestFile = file; latestModifiedTime = file.getLastUpdated().getTime(); } }

if (!freshestFile) { throw new Error("No Microsoft Excel files found in the specified folder."); } Logger.log(Freshest file found: ${freshestFile.getName()} ID: ${freshestFile.getId()} and latestModifiedTime: ${new Date(latestModifiedTime).toString()});

const blob = freshestFile.getBlob();

const convertedFile = Drive.Files.create( { title: Temp_${freshestFile.getName()}, mimeType: MimeType.GOOGLE_SHEETS }, blob ); const convertedFileId = convertedFile.id; Logger.log(Converted File ID: ${convertedFileId}); Logger.log(Attempting to open converted file: ${convertedFileId});

const maxRetries = 10; const retryDelay = 2000; let retries = 0; let convertedSpreadsheet;

while (retries < maxRetries) { try { convertedSpreadsheet = SpreadsheetApp.openById(convertedFileId); break; } catch (e) { retries++; Logger.log(Retry ${retries}/${maxRetries}: Waiting for the converted file to be ready...); Utilities.sleep(retryDelay); } }

if (!convertedSpreadsheet) { throw new Error(Error processing the converted file: Unable to access the converted file after ${maxRetries} retries.); }

Logger.log(Successfully opened converted file: ${convertedSpreadsheet.getName()} with Spreadsheet Time Zone: ${convertedSpreadsheet.getSpreadsheetTimeZone()});

convertedSpreadsheet.setSpreadsheetTimeZone("Europe/Warsaw");

Logger.log(Successfully opened converted file: ${convertedSpreadsheet.getName()} with Spreadsheet Time Zone: ${convertedSpreadsheet.getSpreadsheetTimeZone()});

const sourceSheet = convertedSpreadsheet.getSheets()[0]; Logger.log(Using Source Sheet: ${sourceSheet.getName()});

const targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);

Logger.log(Using Target Spreadsheet ID: ${targetSpreadsheetId});

const targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);

if (!targetSheet) { throw new Error(Target sheet "${targetSheetName}" not found in the target spreadsheet.); }

Logger.log(Using Target Spreadsheet ID: ${targetSpreadsheetId}); Logger.log(Using Target Sheet Name: ${targetSheetName});

const sourceData = sourceSheet.getDataRange().getValues(); const sourceHeader = sourceData[0]; const sourceRows = sourceData.slice(1);

Logger.log(Source sheet row count (including header): ${sourceData.length});

const targetData = targetSheet.getDataRange().getValues(); const targetHeader = targetData[0]; const targetRows = targetData.slice(1);

Logger.log(Target sheet row count (including header): ${targetData.length});

if (JSON.stringify(sourceHeader) !== JSON.stringify(targetHeader)) { throw new Error("Headers in the source and target sheets do not match."); }

const targetRowSet = new Set(targetRows.map(row => JSON.stringify(row))); const newRows = sourceRows.filter(row => !targetRowSet.has(JSON.stringify(row)));

Logger.log(Replacing target sheet data with source data...);

// Clear all rows except the header if (targetData.length > 1) { targetSheet.getRange(2, 1, targetData.length - 1, targetData[0].length).clearContent(); }

// Insert new data (excluding the header) if (sourceRows.length > 0) { targetSheet.getRange(2, 1, sourceRows.length, sourceRows[0].length).setValues(sourceRows); Logger.log(Successfully replaced ${sourceRows.length} rows in the target sheet.); } else { Logger.log("No new data available."); }

deleteDuplicates(targetSheet); sortSheetByColumnDescending(targetSheet, 3); // Sort by column C (index 3 in Sheets)

DriveApp.getFileById(convertedFileId).setTrashed(true);

Logger.log("Running SNOWNotifications...");

SNOWNotifications.sendChatNotifications(false); }

function deleteDuplicates(targetSheet) { const targetData = targetSheet.getDataRange().getValues(); const seen = new Set(); let rowsToDelete = [];

Logger.log("Starting duplicate check based on column B..."); for (let i = 1; i < targetData.length; i++) { const row = targetData[i]; const columnBValue = row[1];

Logger.log(`Checking row ${i + 1} - Column B Value: ${columnBValue}`);

if (seen.has(columnBValue)) {
  rowsToDelete.push(i + 1);
} else {
  seen.add(columnBValue);
}

}

Logger.log(Found ${rowsToDelete.length} duplicate rows based on column B.);

for (let i = rowsToDelete.length - 1; i >= 0; i--) { const rowIndex = rowsToDelete[i]; targetSheet.deleteRow(rowIndex); }

Logger.log(Deleted ${rowsToDelete.length} duplicate rows based on column B.); }

// Function to sort the sheet by column C in descending order function sortSheetByColumnDescending(sheet, columnIndex) { Logger.log(Sorting sheet by column ${columnIndex} in descending order.); const dataRange = sheet.getDataRange(); dataRange.sort([{ column: columnIndex, ascending: false }]); Logger.log("Sheet successfully sorted by column C in descending order."); }

Output

Feb 24, 2025, 12:53:22 PM Info Running Script to save gmail attachment... Feb 24, 2025, 12:53:22 PM Info Starting script execution at: Mon Feb 24 2025 12:53:22 GMT+0100 (Central European Standard Time) Feb 24, 2025, 12:53:22 PM Info Searching for emails with query: from:generalicarehub@service-now.com has:attachment subject:Daily Webellian Incidents Feb 24, 2025, 12:53:23 PM Info Found 33 email threads. Feb 24, 2025, 12:53:59 PM Info Target folder: SNOWReports Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 19491f66de9b65c3 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194971d006ccd8dc Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 1949c43130ba6a9e Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194a169488426b78 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194a68f7c60314d4 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194abb5e45505755 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194b0dc2fe6a043c Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194b602f948b698e Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194bb294d5f20dcc Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194c04fbd175aae0 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194c57595098c841 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194ca9c10d755106 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194cfc28bccdf6ce Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194d4e8a5ceb5303 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194da0f60f5003e8 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194df35f1aa89fc9 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194e45bb96042251 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194e98258732b737 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194eea8c66062998 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194f3ced059dae02 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194f8f5af0e725f2 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 194fe1c34e7f6b5b Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 1950341ea9d04fde Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 1950868d53ccba6c Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 1950d8eb2e0c7422 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 19512b50260c2a8f Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 19517db837a26475 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 1951d01bacfb3868 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 19522280ecf976d6 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 195274ea3a19671e Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 1952c751c2550478 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 195319b945fbb715 Feb 24, 2025, 12:53:59 PM Info Skipping already processed thread: 19536c205a969fae Feb 24, 2025, 12:54:01 PM Info Script execution completed at: Mon Feb 24 2025 12:54:01 GMT+0100 (Central European Standard Time) Feb 24, 2025, 12:54:01 PM Info Target Spreadsheet ID: 1d4L1xY0lzv_G1RmvIN9zNdjAEiNdrh5H4OlWn0gk-QQ Feb 24, 2025, 12:54:01 PM Info Target Sheet Name: Data Feb 24, 2025, 12:54:02 PM Info Freshest file found: Webellian_all_INC_2025-02-24_08-00-38.xlsx ID: 10J5MrgOMZce4B9TzOVDrhTtbvnTWoRq8 and latestModifiedTime: Mon Feb 24 2025 08:29:25 GMT+0100 (Central European Standard Time) Feb 24, 2025, 12:54:08 PM Info Converted File ID: 1iguW8YpsCg2elhjKvtgZ_54Jbo0Ox3FXmfDcUtl-38w Feb 24, 2025, 12:54:08 PM Info Attempting to open converted file: 1iguW8YpsCg2elhjKvtgZ_54Jbo0Ox3FXmfDcUtl-38w Feb 24, 2025, 12:54:09 PM Info Successfully opened converted file: Untitled with Spreadsheet Time Zone: America/Los_Angeles Feb 24, 2025, 12:54:09 PM Info Successfully opened converted file: Untitled with Spreadsheet Time Zone: Europe/Warsaw Feb 24, 2025, 12:54:09 PM Info Using Source Sheet: Page 1 Feb 24, 2025, 12:54:12 PM Info Using Target Spreadsheet ID: 1d4L1xY0lzv_G1RmvIN9zNdjAEiNdrh5H4OlWn0gk-QQ Feb 24, 2025, 12:54:12 PM Info Using Target Spreadsheet ID: 1d4L1xY0lzv_G1RmvIN9zNdjAEiNdrh5H4OlWn0gk-QQ Feb 24, 2025, 12:54:12 PM Info Using Target Sheet Name: Data Feb 24, 2025, 12:54:12 PM Info Source sheet row count (including header): 104 Feb 24, 2025, 12:54:12 PM Info Target sheet row count (including header): 104 Feb 24, 2025, 12:54:12 PM Info Replacing target sheet data with source data... Feb 24, 2025, 12:54:12 PM Info Successfully replaced 103 rows in the target sheet. Feb 24, 2025, 12:54:14 PM Info Starting duplicate check based on column B... Feb 24, 2025, 12:54:14 PM Info Found 0 duplicate rows based on column B. Feb 24, 2025, 12:54:14 PM Info Deleted 0 duplicate rows based on column B. Feb 24, 2025, 12:54:14 PM Info Sorting sheet by column 3 in descending order. Feb 24, 2025, 12:54:14 PM Info Sheet successfully sorted by column C in descending order. Feb 24, 2025, 12:54:15 PM Info Running SNOWNotifications... Feb 24, 2025, 12:54:15 PM Info Notification sending skipped.

Steps

• Run the Script
• Check Execution Log