蠻力代碼合并間隔-Google表格appscript

我需要 appscript 中的蠻力代碼將間隔合并為一個或多個包含特定 ID 的所有范圍的間隔。我已經在研究它，但在實際狀態下我無法在 appscript 中編程，我希望盡快完成這項任務。

示例：對于 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}

結果必須是：

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

使用表：https ://docs.google.com/spreadsheets/d/1UR0xgjCHVxE2Vt0-teSK25f-Kej14Kwfhu5hyhXbDNg/edit?usp=sharing

uj5u.com熱心網友回復：

假設您的表格如下所示：

這是代碼：

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;
}

根據您的資料，它給出了范圍陣列：

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

它們看起來與您的結果非常接近。

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

標籤：javascript 谷歌应用脚​​本 谷歌表格 间隔 蛮力

上一篇：使用Apps腳本和API將資料從Coinmarketcap.com匯入Google表格

下一篇：谷歌表格水平轉換為垂直有間隙