How can I check for duplicates in multiple Google Sheets workbooks in the same directory?

I have a Google Sheet that I use to enter data, and I want to check if the entered data is a duplicate in any of the other Google Sheets workbooks in the same directory. If the data is found in any other workbook, I want to highlight the duplicate cell to show that it's a duplicate.

I found a script online that works for checking duplicates in the same sheet, but I'm not sure how to modify it to check for duplicates in other workbooks in the same directory.

Here's the script I found:

function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var value = range.getValue();
var data = sheet.getDataRange().getValues();
var row = range.getRow() - 1;
var col = range.getColumn() - 1;

// loop through all cells in the sheet
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j++) {
  if (i == row && j == col) {
    continue; // skip the edited cell
  }
  if (data[i][j] == value) {
    // highlight the cell if a duplicate is found in the same sheet
    sheet.getRange(i + 1, j + 1).setBackground("#ff0000"); //change the color code to whatever color you prefer
  }
  }
  }
  }

Any help on how to modify this script to check for duplicates in other workbooks in the same directory would be greatly appreciated! Thanks in advance.

I believe your goal is as follows.

• When a value is put into a cell on the active Spreadsheet, you want to set the background color of the cell of the duplicated value to #ff0000.
• As an additional function, you want to reflect this in all Spreadsheets in the same folder of the active Spreadsheet.

In this case, how about the following sample script?

Sample script:

In this sample, in order to use the Drive service (DriveApp) and SpreadsheetApp.open, please install the OnEdit trigger to function installedOnEdit. Ref

When this script is run, please edit a cell. By this, the script is run by the installable OnEdit trigger.

function installedOnEdit(e) {
  const { range, source } = e;
  const a1Notation = range.getA1Notation();
  const sheetName = source.getActiveSheet().getSheetName();
  const ssId = source.getId();
  const value = range.getValue();

  // For active Spreadsheet.
  const sheckDuplicate = spreadsheet => {
    const ranges = spreadsheet.createTextFinder(value).matchEntireCell(true).findAll();
    ranges.forEach(r => {
      if (r.getSheet().getSheetName() != sheetName || r.getA1Notation() != a1Notation) {
        r.setBackground("#ff0000");
      }
    });
  };
  sheckDuplicate(source);

  // For Spreadsheets except for active Spreadsheet in the same folder.
  const parentFolder = DriveApp.getFileById(ssId).getParents().next();
  const files = parentFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (files.hasNext()) {
    const file = files.next();
    if (file.getId() != ssId) {
      sheckDuplicate(SpreadsheetApp.open(file));
    }
  }
}

• In this script, when you put a value to a cell, the inputted cell value is searched from all Spreadsheets in the same folder of the active Spreadsheet. And, when the duplicated values are found, the background of the cells is changed to #ff0000. In order to search the values, in this sample, I used TextFinder.

Note:

• In this sample script, the inputted value is searched from all sheets in each Spreadsheet. If you want to search it from the specific sheet, please modify sheckDuplicate(source); to sheckDuplicate(source.getActiveSheet()); and sheckDuplicate(SpreadsheetApp.open(file)); to sheckDuplicate(SpreadsheetApp.open(file).getSheetByName("###sheetName###"));.

References:

• Installable Triggers
• createTextFinder(findText) of Class Spreadsheet
• createTextFinder(findText) of Class Sheet

Added:

From your reply, I understood your expected result is as follows.

• From your reply, when you put a value to the active sheet, you want to search the inputted value from all sheets in the active Spreadsheet, and also, you want to search all sheets in all Spreadsheets under the same parent folder of the active Spreadsheet. When the inputted value is found from other sheets, you want to set the background color of the inputted cell and you don't want to change the background color of all other sheets.

In this case, the sample script is as follows.

Sample script:

In this case, also, the installable OnEdit trigger is used. So, please install it to installedOnEdit, and put a value in the cell.

function installedOnEdit(e) {
  const { range, source } = e;
  const ssId = source.getId();
  const value = range.getDisplayValue();
  if (!value) return;

  // For active Spreadsheet.
  let check = source.createTextFinder(value).matchEntireCell(true).findAll().length > 1 ? true : false;

  // For Spreadsheets except for active Spreadsheet in the same folder.
  if (!check) {
    const parentFolder = DriveApp.getFileById(ssId).getParents().next();
    const files = parentFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
    while (files.hasNext()) {
      const file = files.next();
      if (file.getId() != ssId) {
        check = SpreadsheetApp.open(file).createTextFinder(value).matchEntireCell(true).findNext();
        if (check) break;
      }
    }
  }
  if (!check) return;
  range.setBackground("#ff0000");
}