Help needed: Converting Clockify time entries to broadcast calendar format in Google Sheets

Other Questions Zapier
1

I’m struggling with a Google Apps Script that’s meant to convert Clockify time entries into a broadcast calendar format for invoicing. The script was working fine but recently started throwing an error about invalid start dates.

The time data comes into my Google Sheet as ‘2024-08-06 15:30:00’ in column J. I’ve tried reformatting it in column M using this formula:

=DATEVALUE(TEXT(INDIRECT("J"&ROW()), "mm/dd/yyyy"))

But the script still won’t recognize it as a date. Here’s a snippet of the problematic part:

function calculateBroadcastStartDate(startDateString) {
  const startDateObj = new Date(startDateString);
  if (isNaN(startDateObj.getTime())) {
    throw new Error(`Invalid start date: ${startDateString}`);
  }
  // Rest of the function...
}

The error I’m getting is:

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

Any ideas on how to fix this date recognition issue? Or is there a better way to handle this entire process? I’m open to suggestions as I’m new to Apps Script. Thanks for any help!

2

I’ve encountered similar issues with date formatting in Google Sheets and Apps Script. The problem likely stems from how the date is being passed to your script. Instead of relying on the DATEVALUE function, try modifying your sheet formula to:

=TEXT(J2, “yyyy-MM-dd’T’HH:mm:ss”)

This will format the date in ISO 8601 format, which JavaScript (and by extension, Apps Script) can reliably parse. Then, in your script, you can use:

const startDateObj = new Date(startDateString + ‘Z’);

The ‘Z’ ensures it’s treated as UTC. This approach should resolve the ‘Invalid start date’ error you’re experiencing. If you’re still having issues, double-check that the data in column J is consistent and actually contains dates. Let me know if this helps or if you need further assistance!

3

I’ve dealt with similar date conversion headaches in Google Sheets before. One approach that’s worked well for me is to use the ARRAYFORMULA function combined with SPLIT to handle the date formatting in bulk. Try this in your sheet:

=ARRAYFORMULA(IF(LEN(J:J), DATEVALUE(LEFT(J:J,10)) + TIMEVALUE(RIGHT(J:J,8)), ))

This splits the datetime string, converts it to a proper date and time, and should work across all your rows. In your Apps Script, you can then use:

const startDateObj = new Date(startDateString.getTime() - startDateString.getTimezoneOffset() * 60000);

This adjustment accounts for timezone differences. Also, consider using try-catch blocks in your script to handle any unexpected data gracefully. Hope this helps solve your issue!

4

hey there, had similar probs. try using the ISDATE() function to check if ur dates r valid b4 passing em to the script. like =IF(ISDATE(J2), TEXT(J2, “yyyy-MM-dd’T’HH:mm:ss”), “Invalid”) this’ll flag any wonky dates. also, make sure ur time zone settings r correct in both sheets n script. hope this helps!