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;
}
}
}
LinkedIn
Twitter
