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

CodePudding user response：

Suppose your table looks like this:

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'.