CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
dereks
dereks

Reputation: 564

How to highlight duplicates faster in google sheets script?

I wrote function markDupes1Col which highlights all duplicates in column. It works great, but when number of rows has passed 50k it became really slow. Is there anything I can do to make it faster?

function removeEmptyCells(values) {
  values = values.filter(function (el) {
    return el != null && el[0] !== '' && el[0] != null;
  });
  return values;
}

function findDupes(arr) {
  var sortedData = arr.slice().sort();
  var duplicates = [];
  for (var i = 0; i < sortedData.length; i++) {
    if (sortedData[i] && sortedData[i] !== '' && sortedData[i + 1] == sortedData[i]) {
      duplicates.push(sortedData[i]);
    }
  }
  return duplicates;
}

function markDupes1Col() {
  var ss = SpreadsheetApp.openById(appId);
  var sheetName = arguments[0];
  var sheet = ss.getSheetByName(sheetName);
  for(var n = 1; n < arguments.length; n++) {
    var lastRow = sheet.getLastRow();
    if (lastRow == 0) lastRow = 1;
    var rangeArray = sheet.getRange(1, arguments[n], lastRow);

    var valuesArray = rangeArray.getValues();
    valuesArray = removeEmptyCells(valuesArray);

    // Convert to one dimensional array
    valuesArray = [].concat.apply([], valuesArray);

    var duplicates = findDupes(valuesArray);

    rangeArray.setBackground(null);

    if (duplicates.length > 0) {
      for (var i = 0; i < valuesArray.length; i++) {
        for (var j = 0; j < duplicates.length; j++) {
          if (valuesArray[i] == duplicates[j]) {
            sheet.getRange(i + 1, arguments[n]).setBackground("#b7e1cd");
            break;
          }
        } 
      }
    }
  }
}

Upvotes: 1

Views: 2359

Answers (1)

TheMaster
TheMaster

Reputation: 50624

Issue(Slow performance):

  • Use of setBackground in a loop for each cell.
  • Use of arrays to store duplicates.

Solution:

  • Create a output array and use setBackgrounds() instead.
  • Use Objects {} to store duplicates
  • If the above solutions are still slow, use sheets api to batch set backgrounds

Snippet:

function findDupes(arr){
  var valObj = {};
  var duplicates = {};
  arr.forEach(function(row){
    var el = row[0];
    if(el in valObj){ 
      duplicates[el] = 1 
    } else {
      valObj[el] = 1;
    }
  })
  return duplicates;
}

//....

valuesArray = removeEmptyCells(valuesArray);
//valuesArray = [].concat.apply([], valuesArray); Removed
var duplicates = findDupes(valuesArray);

//.....
rangeArray.setBackgrounds(
  valuesArray.map(function(row){
    return [(row[0] in duplicates) ? "#b7e1cd" : null]
  })
)

References:

Upvotes: 4

Related Questions