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
  
}

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

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?

CodePudding user response：

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 row, use this spreadsheet formula:

=unique(A2:L)

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

=arrayformula( 
  { 
    A2:D, 
    array_constrain( 
      if( 
        A2:A & B2:B & C2:C & D2:D <> A1:A & B1:B & C1:C & D1:D, 
        E2:L, 
        iferror(1/0) 
      ), 
      rows(A2:L), columns(A2:L) 
    ) 
  } 
)

CodePudding user response：

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

CodePudding user response：

Assuming transaction ids are sorted, 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]);
}