Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have created a Google spreadsheet (acting as a database) that has numerous columns, name being one of them, and I wanted to make sure the name field is always unique and no row can be created if the name field is the same as another row.

Essentially I want to create a primary key for a database. Does anyone know how to do this in Google Spreadsheets?

If it helps, I created a form to go with the Google Spreadsheet (database) that will enter the data in to the sheet and would love to ensure a user does not enter the same name as someone else in the list already.

share|improve this question

migrated from stackoverflow.com Oct 18 '11 at 1:46

This question came from our site for professional and enthusiast programmers.

1  
OK, this needs to be said: Just like Excel, Google Spreadsheets aren't a database engine. You might want to consider some kind of real database backend that could do this correctly. We have 20+ years of very bad experience with people using Excel as a database, and I'd hate to see everyone have to learn those lessons all over YET AGAIN. Spreadsheet != database. Learn it, live it, love it. –  Michael Kohne Dec 7 '11 at 0:41
add comment

1 Answer 1

You are asking for the opposite of data validation from a list. In effect you want the data validation to fail, instead of succeed, if the value is in the list. This isn't possible with data validation, but a script can do it.

Consider the following script. This script monitors all edits, and pops a message box when a cell value duplicates any other cell value in the same column.

function onEdit( event )
{
  // Store the edited sheet.
  var sheet_active = event .source .getActiveSheet() ;

  // Store the edited range.
  var range_active = event .source .getActiveRange() ;

  // Store the row, column, and value of the edited cell.
  var row_edited = range_active .getRow() ;
  var column_edited = range_active .getColumn() ;
  var value_edited = range_active .getValue() ;

  // Store a range consisting of the column containing the edited cell.
  var range_column_edited = sheet_active .getRange(
    1 , column_edited ,
    sheet_active .getMaxRows() , 1
  ) ;

  // Store an array consisting of the values in the column.
  var values_column_edited = range_column_edited .getValues() ;

  // Compare each value to the edited cell.
  for( var r = 0 ; r < values_column_edited .length ; r++ )
  {
    if( r+1 == row_edited ) continue ;
    if( values_column_edited[r] == value_edited )
      Browser .msgBox(
        'value_edited="'
        + value_edited
        + '" values_column_edited['
        + r
        + ']="'
        + values_column_edited[r]
        + '"'
      ) ;
  }
}

There will be various practical refinements needed. For example, you might elect to monitor only certain columns, and you might elect to take additional action, such as blanking out the cell value. You may need special handling for blank (missing) values. But this gives you the basic technique that will let you validate.

Update:

To elaborate on the original answer I thought I'd add a few of the validations I personally use that were mentioned in the answer.

// Here's a function I use to assure that only one cell is being edited.

function isRangeSingleCell(range) {
  if(range.getRow() === range.getLastRow() && range.getColumn() === range.getLastColumn()) { return true; }
}

To use it just skip the validation if more than one cell is edited

if(!isRangeSingleCell(range_active)) { return; }

You can also skip the validation if the row is not the first row:

if(range_active.getRowIndex() != 1) { return; }

Note: I can't remember off the top of my head if the row counting starts at 0 or 1 so this code may have a bug

The key to onEdit validations is to exit as early as possible to save from unnecessary computation. The quickest exit from a function is an empty return statement.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.