Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – I Want to remove duplicate data in google sheet including all it occurrences

This is the simple code which remove all the duplicate values and working fine.

function removeDuplicate2ndPhase() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Hourly Data');
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange('B3:M' + lastRow);
  
  range.removeDuplicates([3]);
}

all i want to remove duplicate data with it’s original values too.

I filter data on the basis of column C and i tried this code it get the desired results but problem if i manually change a value in column C and all other row data is same still it delete that row. Which I don’t want.

function keepUniqueValues() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Hourly Data');
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange('B3:M' + lastRow);
  
  // Get all values in the specified range
  var values = range.getValues();
  var uniqueCheck = {};
  var uniqueValues = [];
  
  // Loop through each row in the range
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    var key = row[2]; // Assuming the third column (index 2) is the column to check for duplicates
    
    // Check if the key is already in uniqueCheck
    if (!uniqueCheck[key]) {
      uniqueCheck[key] = true;
      uniqueValues.push(row);
    } else {
      // If the key is already in uniqueCheck, remove it from uniqueValues (if it exists)
      var indexToRemove = uniqueValues.findIndex(function(existingRow) {
        return existingRow[2] === key; // Check if the existing row has the same key value
      });
      if (indexToRemove !== -1) {
        uniqueValues.splice(indexToRemove, 1);
      }
    }
  }
  
  // Clear existing data in the range
  range.clearContent();
  
  // Set the unique values back to the range
  if (uniqueValues.length > 0) {
    range.offset(0, 0, uniqueValues.length, uniqueValues[0].length).setValues(uniqueValues);
  }
}
Tags:

2

Answers


  1. 0 
    function keepUniqueValues() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Hourly Data');
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange('B3:M' + lastRow);
  
  // Get all values in the specified range
  var values = range.getValues();
  var uniqueCheck = {};
  var uniqueValues = [];
  
  // Loop through each row in the range
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    var key = row[2]; // Assuming the third column (index 2) is the column to check for duplicates
    
    // Check if the key is already in uniqueCheck
    if (!uniqueCheck[key]) {
      uniqueCheck[key] = true;
      uniqueValues.push(row);
    } else {
      // If the key is already in uniqueCheck, remove it from uniqueValues (if it exists)
      var indexToRemove = uniqueValues.findIndex(function(existingRow) {
        return existingRow[2] === key; // Check if the existing row has the same key value
      });
      if (indexToRemove !== -1) {
        uniqueValues.splice(indexToRemove, 1);
      }
    }
  }
  
  // Clear existing data in the range
  range.clearContent();
  
  // Set the unique values back to the range
  if (uniqueValues.length > 0) {
    range.offset(0, 0, uniqueValues.length, uniqueValues[0].length).setValues(uniqueValues);
  }
}
    Login or Signup to reply.
  2. 0

    Use Array.map() and Array.filter(), like this:

    /** @OnlyCurrentDoc */
'use strict';

function keepUniqueValues() {
  const range = SpreadsheetApp.getActive().getRange('Hourly Data!B3:M');
  const uniques = getUniqueRows_(range.getValues(), 2);
  range.clearContent();
  if (uniques.length) {
    range.offset(0, 0, uniques.length, uniques[0].length).setValues(uniques);
  }
}

/**
* Gets rows where a key appears exactly once in a key column.
*
* @param {Object[][]} values The 2D array to filter.
* @param {Number} keyColumnIndex The zero-indexed key column index.
* @return {Object[][]} The rows where the key appears exactly once.
*/
function getUniqueRows_(values, keyColumnIndex) {
  const keys = values.map(row => row[keyColumnIndex]);
  const _count = (key) => keys.filter(k => String(k) && k === key).length;
  return values.filter(row => 1 === _count(row[keyColumnIndex]));
}

    See Array.map() and Array.filter().

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search