Google Sheets is a very convenient tool for organizing and analyzing data. However, there may be times when you want to display an alert if cell values are duplicated under certain conditions. In this article, we will introduce a method to display an alert message when duplicate values are selected in cells using Google Apps Script.

Contents
  1. Purpose
  2. Step 1: Prepare the Spreadsheet
  3. Step 2: Start Google Apps Script
  4. Step 3: Write the Script
  5. Step 4: Save and Test the Script
  6. Summary

Purpose

The purpose of this script is to warn the user when the same value is selected two or more times within a specified range in Google Sheets. This is useful, for example, when you want to avoid duplicates in scheduling or survey aggregation.

Step 1: Prepare the Spreadsheet

First, open Google Sheets and set the range that contains the dropdown list. In this example, we assume a sheet named “Dropdown Sheet” with a dropdown list set in the range “A1:K4”. The method to set up a dropdown list is as follows:

  1. Open “Dropdown Sheet” and select the range “A1:K4”.
  2. From the menu bar, select “Data” > “Data validation”.
  3. In the data validation settings screen, select “List from a range” in the “Criteria” section.
  4. In the “List range” input box, specify the range that contains the values you want to use for the dropdown list (e.g., “List Sheet!A1:A10”).
  5. Click the “Save” button.

Step 2: Start Google Apps Script

From the spreadsheet menu bar, select “Extensions” > “Apps Script” to open the script editor. A new project will open, so delete any existing code if present.

Step 3: Write the Script

Copy and paste the following code into the script editor.

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  if (sheet.getName() === 'プルダウンシート' && range.getRow() >= 1 && range.getRow() <= 4 && range.getColumn() >= 1 && range.getColumn() <= 11) {
    var currentValue = e.value;
    var currentCell = range.getA1Notation();
    
    // 同じ範囲の他のセルの値を取得
    var values = sheet.getRange('A1:K4').getValues();
    var count = 0;
    for (var i = 0; i < values.length; i++) {
      for (var j = 0; j < values[i].length; j++) {
        if (values[i][j] === currentValue && ('A' + (i+1) + ':' + 'K' + (j+1)) !== currentCell) {
          count++;
        }
      }
    }
    
    if (count > 1) {
      SpreadsheetApp.getUi().alert('エラー: この項目は2回以上選択されています');
      range.setValue(''); // 2回以上重複があればセルをクリア
    }
  }
}

　
This script runs automatically every time a cell is edited. If the same value is selected two or more times within the specified range, an alert message is displayed and the cell value is cleared.

Step 4: Save and Test the Script

Save the script and return to the spreadsheet to test it. Try selecting the same value two or more times within the specified range. The alert message should appear, and the cell value should be cleared.

Summary

In Google Sheets, you can use Google Apps Script to display an alert when cell values are duplicated under specific conditions. This feature is especially useful when you want to avoid duplicates or maintain data integrity.

　
*Please use this at your own risk.*

AppsScript Duplicate Alert