Google sheets Scenario

[OUTMAN]

I’ve recently encountered an issue that I believe many of you could provide some valuable insights on. I have two Google Sheets, both containing around a thousand email addresses. The problem is that many of these email addresses are duplicated across the two sheets, and I’m looking for a way to efficiently manage and clean this data.

Here’s the scenario:

Issue:

I have two Google Sheets with a large number of email addresses, and many of these emails are duplicated in both sheets. I want to create an automated scenario that helps me identify these duplicates, either remove them from one sheet and keep them in the second one or replace them with the word “Exist,” and also add the unique emails that are not duplicated to another Google Sheet.

Solution Needed: I’m seeking assistance in creating a scenario that will allow me to perform the following tasks:

1. Identify Duplicates: The scenario should be able to scan both Google Sheets and identify duplicate email addresses.
2. Remove or Replace Duplicates: Once duplicates are identified, I want to have the option to either remove them from one sheet and keep them in the other one or replace them with a specific word like “Exist.”
3. Transfer Unique Emails: The scenario should also be able to extract the unique email addresses that are not duplicated in either of the two sheets and transfer them to another Google Sheet.

I would greatly appreciate it if anyone could help me set up a scenario for this specific task. If you’ve had experience dealing with similar data management issues and can provide guidance, please share your insights, tips, or even a step-by-step guide on how to achieve this. Any support is highly appreciated!

[Abdulkarim_Ouaydah]

Hello outman ,
i believe there are many quick ways to remove duplicates from google sheets that are easier than making a scenario.
so unless u have many sheets or this is a repetitive task for you, youre better off finding a 2 min tutorial on youtube.

[Rafael_Sanchez]

Hello @OUTMAN maybe you could make an API call to Google Sheets and process the DeleteDuplicatesRequest

Here more information

https://developers.google.com/resources/api-libraries/documentation/sheets/v4/java/latest/com/google/api/services/sheets/v4/model/DeleteDuplicatesRequest.html

[samliew]

You’ll need to use Google Sheets “Make an API Call” module to call the spreadsheets.batchUpdate endpoint to request for a “DeleteDuplicatesRequest”.

Removes rows within this range that contain values in the specified columns that are duplicates of values in any previous row. Rows with identical values but different letter cases, formatting, or formulas are considered to be duplicates.

You will get something that looks like this (you fill in the GridRange and DimensionRange JSON by following the documentation)