Loop that finds duplicates, and deletes values after 1st instance of duplicate from a range of cells

Question

Current Issue:

Hey everyone, appreciate any help here as I'm still beginning my journey in coding.

I'm trying to see if I can make a script that will:

1. Look for duplicates (in column D), and
2. delete any data from the following duplicates after the 1st match in columns E-L (see desired outcome if that doesn't make sense verbally).
3. The script would need to use the column header names (ex. "snacks") instead of hard-coded column references

*So for example, the script finds ABC001, deletes only the duplicates for ABC001 in the corresponding columns then moves on to ABC004 and performs the same action.

I'm not sure how to write a script that would do this, and keep going to find duplicates after the 1st set is found. I think I know how to do a for loop now, but it's not clear to me how to make it do a search loop and stop after it find the first match and keep going.

Current Data:

Desired Outcome:

Code so far below. I think I would need to incorporate something like JSmith showed in this example? Or would I need to incorporate some form of .length with the duplicate range in a for statement so that it can find the duplicates, get the # of them, and then only perform the action on everything past the 1st instance?

function duplicateRemoval() {
  ss = SpreadsheetApp.getActive().getSheetByName('Sheet1');//gets sheet by name
  const [aB,...cd] = ss.getDataRange().getValues();//literal assignment that assigns aB to the header array and the rest of the data to 'cd'

  let column = {}
  let iData = {};//index into the row array for each column header
  aB.forEach((a,i)=>{column[a] = i+1;iData[a]=i});//building column and iData so that headers can move anywhere
  
}//let & forEach & const const [aB,...cd] derived from (https://stackoverflow.com/questions/70101896/search-column-for-text-and-use-array-list-to-insert-text-in-another-cell) @Cooper

Raw Data:

Name	Owner	Snack	Transaction #	# of snacks requested	#2	#3	#4	#5	#6	#7	#8
Bill Example	Snacktown	celery	ABC001	4	1	2	3	4	5	6	4
Bill Example	Snacktown	celery	ABC001	4	1	2	3	4	5	6	4
Bill Example	Snacktown	celery	ABC001	4	1	2	3	4	5	6	4
Jane Doe	Snacktown	chips	ABC002	1	1	1	1	1	1	1	1
Jane Doe	Chipworld	chips	ABC003	1	1	1	1	1	1	1	1
Jane Doe	Chipworld	chips	ABC004	5	5	1	1	1	1	1	5
Jane Doe	Chipworld	chips	ABC004	5	5	1	1	1	1	1	5
Jane Doe	Chipworld	chips	ABC004	5	5	1	1	1	1	1	5
Jane Doe	Chipworld	chips	ABC004	5	5	1	1	1	1	1	5

Sources:

google app script array delete duplicate value from top

Google Script App Delete Duplicate Rows with a Specific Value in Specific Column in Google Sheet

How do I find and delete duplicate values in a range of cells while keeping the first occurrence of a duplicated value in Google Sheets?

Answer 1

Assuming transaction ids are always grouped, iterate through rows and delete all specified columns where previous transactionId is equal to current transactionId.

function duplicateRemovalOfColsToRemove() {
  const transactionsHeader = 'Transaction #',
    colsToRemoveHeaders = ['# of snacks requested', '#2'],//add column headers as necessary
    ss = SpreadsheetApp.getActive().getSheetByName('Sheet1'), //gets sheet by name
    range = ss.getDataRange(),
    [headers, ...values] = range.getValues(),
    colsToRemove = colsToRemoveHeaders.map((h) => headers.indexOf(h)),
    transactionsIdx = headers.indexOf(transactionsHeader);
  let currTransaction = '';
  values.forEach((row) =>
    row[transactionsIdx] === currTransaction
      ? colsToRemove.forEach((idx) => (row[idx] = ''))
      : (currTransaction = row[transactionsIdx])
  );
  range.setValues([headers, ...values]);
}

Answer 2

If you need a script you can try this:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();

  // get all data from the sheet
  var data = range.getValues();

  // get column headers
  var headers = data.shift();

  // get the list of transactions
  var transactions = data.map(x => x[headers.indexOf('Transaction #')]);

  // loop through all the transactions
  for (let transaction of transactions) {

    // get indexes of rows to process
    var rows = transactions.map((t, row) => t === transaction ? row : '' ).filter(String).slice(1);

    // process the rows
    for (let r of rows) {
      data[r][headers.indexOf('# of snacks requested')] = '';
      data[r][headers.indexOf('#2')] = '';
      data[r][headers.indexOf('#3')] = '';
      data[r][headers.indexOf('#4')] = '';
      data[r][headers.indexOf('#5')] = '';
      data[r][headers.indexOf('#6')] = '';
      data[r][headers.indexOf('#7')] = '';
      data[r][headers.indexOf('#8')] = '';
    }
  }
  
  // put the updated data back to the sheet
  range.setValues([headers, ...data]);
}

Update

Here is the improved variant of the same code. It still loops through all the rows, but it skips already processed transactions:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var [headers, ...data] = range.getValues();
  
  var transactions = data.map(x => x[headers.indexOf('Transaction #')]);
  
  var cols_to_clean = ['# of snacks requested','#2','#3','#4','#5','#6','#7','#8'];

  var processed_transactions = [];

  for (let transaction of transactions) {

    // skip already processed transactions
    if (processed_transactions.includes(transaction)) continue; 

    var rows_to_clean = transactions.map((t, row) => t === transaction ? row : '' )
      .filter(String).slice(1);

    for (let r of rows_to_clean) {
      cols_to_clean.forEach(c => data[r][headers.indexOf(c)] = '');
    }

    processed_transactions.push(transaction);
  }
  
  range.setValues([headers, ...data]);
}

Thanks to @TheMaster for the noted deficiencies.

Update 2

Sorry for spamming, just figured out the final solution that has no redundant iterations (I hope):

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var [headers, ...data] = range.getValues();
  var cols_to_clean = ['# of snacks requested','#2','#3','#4','#5','#6','#7','#8'];
  
  // get all transactions (9 items for this data)
  var all_transactions = data.map(x => x[headers.indexOf('Transaction #')]);

  // get the short list of unique transaction (4 items for this data)
  var uniq_transactions = [... new Set(all_transactions)];

  for (let transaction of uniq_transactions) {

    // get all indexes of rows with given transaction
    var rows_to_clean = all_transactions.map((t, row) => t === transaction ? row : '')
      .filter(String).slice(1);

    // clean the rows
    for (let r of rows_to_clean) {
      cols_to_clean.forEach(c => data[r][headers.indexOf(c)] = '');
    }

  }
  range.setValues([headers, ...data]);
}

I didn't remove my first update, I think this can be useful for educational purposes.

Answer 3

It is unclear why you want to use a script here, as this seems doable with a plain vanilla spreadsheet formula. It is also unclear whether you really need to repeat the values in A2:D many times with nothing in columns E2:L.

To remove duplicate rows, and get just one copy of each unique transaction, choose Insert > Sheet and put this spreadsheet formula in cell A1:

=unique(Sheet1!A2:L)

To get the expected result you show, including rows that are mostly blank, use this:

=arrayformula( 
  { 
    Sheet1!A2:D, 
    array_constrain( 
      if( 
        Sheet1!D2:D <> Sheet1!D1:D, 
        Sheet1!E2:L, 
        iferror(1/0) 
      ), 
      rows(Sheet1!E2:L), columns(Sheet1!E2:L) 
    ) 
  } 
)

To determine row uniqueness based on all columns A2:D instead of just the transaction ID in column D2:D, replace the if() condition with A2:A & B2:B & C2:C & D2:D <> A1:A & B1:B & C1:C & D1:D, inserting the proper sheet reference.