Checking for data duplication without direct access

Question

I have set up a spreadsheet on Google Drive that multiple users can access and edit, but I also need users to be alerted when the data they enter already exists elsewhere (this could be another sheet, another file etc.) without giving users direct access to the existing data (i.e. users can check if their data is duplicated, but can not view the initial data).

Is this possible on Google Drive?

Answer 1

Yes, this is possible using an installable trigger, which runs as the user who created the trigger (not as the user at keyboard). Here is a simple example which compares the content of cell A1 in the current sheet with the content of cell A1 in the sheet "Sheet1" of some master spreadsheet. If they match, the background of A1 is set to red.

function compare(e) {
  var range = e.range;
  if (range.getA1Notation() == 'A1') {
    var masterSheet = SpreadsheetApp.openByUrl( your URL ).getSheetByName('Sheet1');
    if (range.getValue() == masterSheet.getRange('A1').getValue()) {
      range.setBackground('red');
    }
    else {
      range.setBackground('white');
    }
  }
}

After entering this script, add a trigger via Resources > Current Project's trigger. Under Events, select "From spreadsheet", "On edit".

As said above, this means that when triggered, the function compare will run as if it's run by you, and have the permissions that you have: in particular, the permission to access the master spreadsheet.