Calculating Broadcast Dates and Week Numbers, Google Apps Script for Google Sheets Using Imported Clockify Data via Zapier

Question

I am new to Google Apps Script and mostly using Gemini to write. I'm using Zapier to import Clockify Data into Google Sheets. My client needs a specific format based on broadcast calendar billing dates. I'm trying to save myself time by automating the process to record my time on a calendar date in Clockify to broadcast dates and week numbers in order to send it to a Quickbooks invoice. This was working for several months, then a few weeks ago I started getting an invalid start date error.

The start date comes to Zapier as 2024-08-06T15:30:00Z but populates a new row in Google Sheets (Column J) as 2024-08-06 15:30:00 without "T" or "Z". The script below won't recognize it as a date. It seems like this would be a simple fix, but I cannot get around this error.

Error: Invalid start date: undefined calculateBroadcastStartDate @ calcBCastDatesWeeks.gs:10

I created another date column (M) to see if reformatting the date in column J helps, but I've not had any luck so far. F(x)=DATEVALUE(TEXT(INDIRECT("J"&ROW()), "mm/dd/yyyy"))

I'm sure it's due to my ignorance of Apps Script and date formatting in Sheets.

Obviously, if there is an easier way to do this I am open to considering a new approach.

TIA for helping me get my time back.

// Use sheet variables globally for better performance
const sheet = SpreadsheetApp.openById(" [purposefully withheld] "); // Replace with your actual sheet ID
const activeSheet = sheet.getSheetByName("Imported Clockify Data");

// Function to calculate broadcast start date
function calculateBroadcastStartDate(startDateString) {
// Convert to Date object and handle potential errors
const startDateObj = new Date(startDateString);
if (isNaN(startDateObj.getTime())) {
throw new Error(`Invalid start date: ${startDateString}`);
}

const broadcastYear = startDateObj.getFullYear();
const broadcastMonth = 0; // Always use January

return new Date(broadcastYear, broadcastMonth, 1);
}

function calculateWeekNumber(startDateString) {
const startDate = new Date(startDateString);
const broadcastStartDate = calculateBroadcastStartDate(startDateString);

// Find the first Monday of the month
const firstDayOfMonth = new Date(startDate.getFullYear(), startDate.getMonth(), 1);
const firstMondayOfMonth = new Date(firstDayOfMonth);
while (firstMondayOfMonth.getDay() !== 1) { // 1 represents Monday
firstMondayOfMonth.setDate(firstMondayOfMonth.getDate() - 1);
}

const daysSinceFirstMonday = Math.floor((startDate - firstMondayOfMonth) / (1000 * 60 * 60 * 24));
const weekNumber = Math.floor(daysSinceFirstMonday / 7) + 1; // Add 1 to start from 1

return { WeekNumber: weekNumber };
}

// Function to update cells with broadcast date and week number
function updateCells() {
const startDateRange = activeSheet.getRange("M:M");

for (let i = 2; i <= startDateRange.getNumRows(); i++) {
const startDateCell = startDateRange.getCell(i, 1);
const startDateString = startDateCell.getValue();

if (!startDateString) {
continue;
}

try {
const broadcastStartDate = calculateBroadcastStartDate(startDateString);
const weekNumber = calculateWeekNumber(startDateString).WeekNumber;

activeSheet.getRange(`AB${i}`).setValue(broadcastStartDate);
activeSheet.getRange(`AG${i}`).setValue(weekNumber);
} catch (error) {
Logger.log(`Error processing row ${i}: ${error}`);
// Optionally set a value in the error column or handle differently
}
}
}

// Call the updated function
updateCells()

I created another date column (M) to see if reformatting the date in column J helps, but I've not had any luck so far. F(x)=DATEVALUE(TEXT(INDIRECT("J"&ROW()), "mm/dd/yyyy"))