Brute force code to merge intervals - Google sheets appscript

Question

I need a bruteforce code in appscript to merge the intervals into one or more intervals that contain all the ranges for an specific ID. I'm already working on it but in the actual state i cannot program in appscript and i'm hoping to get this task done as soon as possible.

Example: for ID 11403

{43896,44463} 
{44245,44245}
{44257,44257}
{44258,44258}
{44258,44258}
{44265,44316}
{44271,44271}
{44277,44279}
{44300,44326}
{44363,44363}
{44363,44363}
{44376,44376}
{44265,44316}
{44271,44271}
{44410,44410}
{44537,44537}
{44540,44553}
{44544,44547}

The results must be:

{43896,44410}
{44537,44537}
{44540,44553}

Sheet of use: https://docs.google.com/spreadsheets/d/1UR0xgjCHVxE2Vt0-teSK25f-Kej14Kwfhu5hyhXbDNg/edit?usp=sharing

Answer 1

Suppose your table looks like this:

ID	start	end
11403	43896	44463
11403	44245	44245
11403	44257	44257
11403	44258	44258
11403	44258	44258
11403	44265	44316
11403	44271	44271
11403	44277	44279
11403	44300	44326
11403	44363	44363
11403	44363	44363
11403	44376	44376
11403	44265	44316
11403	44271	44271
11403	44410	44410
11403	44537	44537
11403	44540	44553
11403	44544	44547
12345	43896	44463
12345	44245	44245
12345	44257	44257

Here is the code:

function myFunction() {
  var sh = SpreadsheetApp.getActiveSheet();
  var data = sh.getRange('A2:C').getValues();

  var obj = {}
  while (data.length) {
    var [key, start, end] = data.shift();
    var range = {'start': start, 'end': end};
    try { obj[key].push(range) } catch(e) { obj[key] = [range] };
  }

  var ranges = merge_ranges(obj['11403']); // get the merged ranges for id 11403

  console.log(ranges); // <-------- here are the results (for id 11403)
}


function merge_ranges(obj) {
  var range = obj.shift();
  var end = range.end;
  var ranges = [range];
    
  while (obj.length) {
    var range = obj.shift();
    if (range.start > end) {
      ranges.push(range)
      if (range.end > end) end = range.end;
    }
  }
  return ranges;
}

From your data it gives the array of ranges:

[ { start: 43896, end: 44463 },
  { start: 44537, end: 44537 },
  { start: 44540, end: 44553 } ]

They look quite close to your results.

But it's not clear from your question what exactly you want to do with these results. Loop through all the IDs an put on the sheet?

Update

Probably it should be something like this:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Página1'); // <----- a name of the source sheet
  var data = sh.getRange('A2:C').getValues();

  // create the object {id1: [range, range, range], id2: [range], ...}
  var obj = {}
  while (data.length) {
    var [id, start, end] = data.shift();
    var range = {'start': start, 'end': end};
    try { obj[id].push(range) } catch(e) { obj[id] = [range] };
  }

  // make the table from the object [id, range.start, range.end]
  var table = [];
  for (var id in obj) {
    var ranges = merge_ranges(obj[id]);
    ranges.forEach(range => table.push([id, range.start, range.end]));
  }
  
  // create or select the sheet 'Output'
  try { var output = ss.getSheetByName('Output'); output.clear(); }
  catch(e) { var output = ss.insertSheet(); output.setName('Output') }

  // add the header to the table and put the table on the sheet
  var header = ['ID', 'Start', 'End'];
  table = [header, ...table];
  var range = output.getRange(1, 1, table.length, table[0].length);
  range.setValues(table);
}

function merge_ranges(obj) {
  var range = obj.shift();
  var end = range.end;
  var ranges = [range];
    
  while (obj.length) {
    var range = obj.shift();
    if (range.start > end) {
      ranges.push(range)
      if (range.end > end) end = range.end;
    }
  }
  return ranges;
}

It puts the results as a plain 3-colon table on the sheet 'Output'.