Remove Duplicates

[Nadhirah]

Hi everyone,

I have a problem with duplicate uniqueid. So my system used adalo as one of the platform to pass value to google sheets and appsheet will use google sheet as the database.
The issue is adalo keep sending duplicates to google sheet because user pressing submit button multiple times hence affecting appsheet view as well. I tried using formula to recognize duplicate and want to filter using slice but it will only work on the row once the row is updated which is not reliable since adalo only send data to google sheet and not appsheet directly.

Does anyone have any idea on what I should do to remove these duplicates?

[xeonavn]

If you dont want duplicate entry, use uniqueid as a key.

[Nadhirah]

I did use uniqueid as key but adalo is sending duplicate to appsheet through make and i want to stop that

[scott192]

I looked up the ADALO docs and found this...

https://help.adalo.com/integrations/accessing-record-id

[Nadhirah]

It is true that in adalo table the duplicate each have different record id but in make, my team used the timestamp as uniqueid and send it to google sheet thus the duplicate. I tried adding filter and aggregator in make but my knowledge is limited, it didnt work so Im trying in appsheet instead. 

I saw that automation bot would go through each row, do you know whether it will took a very long time for it to reach around 30000 row? 

[scott192]

This problem really sounds more like an Adalo problem don't you think?

In Appsheet there are mechanisms that make buttons appear or disappear depending on conditionals...for instance it is possible to make the Add button disappear if the table being added to already has a Ref to a parent.

Isn't there something similar in Adalo?  Can you not for instance add a column to your Adalo data source that when the button that triggers Make's workflow to send the data to your sheet also sets the flag as TRUE in that column?  Then you can make the Adalo button disappear after the first time the record is sent to sheets?

[Nadhirah]

Yeah this is correct, I already set that if user press submit it will go to another page but they keep on pressing on it (maybe because of internet issue so they think it isnt submitted yet) thus sending the duplicates. But I will try to make the button disappear in adalo. Thankss

[Nadhirah · Accepted solution]

I gave up looking on adalo and make I just tried again using appsheet and it works. I just used apps script to check on for 2 column on rows having same data will just change the data to duplicate instead. below is what i used.

function checkAndMarkDuplicatesTwoColumns() {

  // Replace with your actual spreadsheet ID or URL

  var spreadsheetId = 'SHEET ID'; // or use openByUrl

  // var spreadsheetUrl = 'YOUR_SPREADSHEET_URL'; // Uncomment this if using URL instead

  // Open the spreadsheet by ID or URL

  var spreadsheet = SpreadsheetApp.openById(spreadsheetId); // or use openByUrl(spreadsheetUrl)

  // Specify the sheet name you want to work with (replace 'Sheet1' with your actual sheet name)

  var sheet = spreadsheet.getSheetByName('Work Order'); // Use the name of the sheet

  if (!sheet) {

    Logger.log('Sheet not found!');

    return;

  }

  // Set the column indices to check for duplicates (e.g., 1 = Column A, 2 = Column B)

  var columnA = 1; // Column A

  var columnB = 2; // Column B

  // Set the column index for the "status" column (e.g., 3 = Column C)

  var statusColumn = 3; // Change this to the column where you want to mark 'Duplicate' (e.g., 3 for Column C)

  // Get all the values from the columns to check (skip the first row if it has headers)

  var dataRangeA = sheet.getRange(2, columnA, sheet.getLastRow() - 1).getValues(); // Column A values

  var dataRangeB = sheet.getRange(2, columnB, sheet.getLastRow() - 1).getValues(); // Column B values

  // Object to track seen combinations

  var valueMap = {};

  // Loop through the data to find duplicates based on both Column A and Column B

  for (var i = 0; i < dataRangeA.length; i++) {

    var currentValueA = dataRangeA[i][0]; // Get the value from Column A

    var currentValueB = dataRangeB[i][0]; // Get the value from Column B

    // Create a combined key for both Column A and Column B values

    var combinedValue = currentValueA + "-" + currentValueB;

    // Check if the combined key has already been encountered

    if (valueMap[combinedValue]) {

      // If it's a duplicate, mark 'Duplicate' in the status column

      sheet.getRange(i + 2, statusColumn).setValue('Duplicate');

    } else {

      // If it's not a duplicate, store the combined value in the map

      valueMap[combinedValue] = true;

    }

  }

}