Hey everyone, I’m stuck on a problem and could use some help. I’m trying to write an Apps Script function that merges overlapping intervals for specific IDs in Google Sheets. I’m not great at Apps Script yet, so I’m looking for a simple solution.
Here’s what I’m dealing with:
For ID 12345, I have intervals like:
{1000,1500}
{1200,1300}
{1400,1600}
{1800,2000}
{2100,2200}
I need the output to be:
{1000,1600}
{1800,2000}
{2100,2200}
Can anyone point me in the right direction or share some code that could help? I’ve been trying to figure this out for hours and I’m totally stuck. Thanks in advance for any help!
I’ve dealt with interval merging in Apps Script before. My approach is to begin by sorting the intervals by the start time, then work through them sequentially. At each step, compare the current interval with the last one added to your result. If they overlap, update the end time to be the maximum of both intervals; if not, add the current interval to your result. This method, while straightforward, handles edge cases such as completely overlapping intervals and has worked well in my experience.
I encountered a similar challenge some time ago, and my approach was to first sort the intervals based on their starting values. Then I ran through the sorted list and checked if the current interval overlapped with the previous one. When they did, I simply updated the ending value to the maximum of both. This strategy worked reliably in my previous projects with Apps Script. It’s important to test multiple scenarios, especially with fully overlapping intervals, to ensure the script handles every edge case properly.
hey there FlyingEagle! i faced this issue too. heres what worked for me: sort intervals by start time, then loop through em. compare each interval with the previous one. if they overlap, merge em by updating the end time. keeps it simple n effective. good luck with ur script!