I have an object.

{ C43: 'X13',
  C59: 'X13',
  C75: 'X14',
  C91: 'X14',
  C107: 'X16',
  C123: 'X17',
  C139: 'X17' }

Here, the keys of the object in an ordered manner are the cells where some data is present.

I want to identify where the same value repeats of one or more keys and strikethrough that row.

So, in the above object, X17 value is same for the keys C123 and C139. I want whichever is earlier/smaller key value's row to be striked through.

So here, strike through row 123as is belongs to C123 which is the smallest/earlier entry than C139.

Please help! Thank you

CodePudding user response：

To find if a value is a duplicate, but not the last one to appear in the list, use Array.filter() and Array.lastIndexOf(), like this:

function test() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const cellValueObject = {
    C43: 'X13',
    C59: 'X13',
    C75: 'X14',
    C91: 'X14',
    C107: 'X16',
    C123: 'X17',
    C139: 'X17',
    C991: 'X14', // test non-consecutive duplicate values
  };
  const dupCellsA1 = getKeysOfDuplicateValuesExceptLast(cellValueObject);
  const dupRowsA1 = getRowReferenceA1FromCellA1(dupCellsA1);
  const rowList = sheet.getRangeList(dupRowsA1);
  rowList.setFontLine('line-through');
}

function getKeysOfDuplicateValuesExceptLast(object) {
  const values = Object.values(object);
  return Object.keys(object).filter((_, index) =>
    index !== values.lastIndexOf(values[index])
  );
}

function getRowReferenceA1FromCellA1(cellsA1) {
  return cellsA1.map(rangeA1 => {
    const rowNumber = rangeA1.replace(/[^\d]/, '');
    return `A${rowNumber}:${rowNumber}`;
  });
}

rowList is a RangeList object. Formatting a RangeList is much more efficient that formatting individual Range objects one by one.

CodePudding user response：

Here is the code for your ideas. You need to replace the sheet name with yours. Let me know if it works in your spreadsheet.

function strikeThroughDuplicates(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName("Example"); //replace your sheet name

  var example = { C43: 'X13',
                  C59: 'X13',
                  C48: 'X14',
                  C50: 'X14',
                  C107: 'X16',
                  C123: 'X17',
                  C139: 'X17' };

  var valueCellsDict = {};
  
  //turn the object to a dictionary as value : cells
  for (const [key, value] of Object.entries(example)) 
  {
    if(value in valueCellsDict){
      valueCellsDict[value].push(key);
    } else {
      valueCellsDict[value] = [key];
    }
  }

  for(var key in valueCellsDict){
    //loop through the dictionary and only checks value has more than one cells
    if(valueCellsDict[key].length > 1) {
      //loop through cells and strike out cells except the last one.
      for(var index =0; index < valueCellsDict[key].length - 1; index  ) {
         var cellPos = valueCellsDict[key][index];
         var cell = targetSheet.getRange(cellPos);
         cell.setFontLine("line-through");
      }
    }
  }
}