Adding a Pop-Up Message in Google Sheets Using Apps Script

by

Script that displays a pop-up message if the user selects more than one cell at a time and tries to insert the date. This will help ensure users follow the intended workflow.

Step-by-Step Guide

1. Set Up the Script

  1. Open your Google Sheet.
  2. Navigate to Extensions > Apps Script to open the Apps Script editor.
  3. Delete any existing code and paste the following script:
function onEdit(e) {
  // Get the active spreadsheet and range
  const range = e.range;
  const sheet = e.source.getActiveSheet();

  // Check if the user has selected more than one cell
  if (range.getNumRows() > 1 || range.getNumColumns() > 1) {
    // Show a pop-up message
    SpreadsheetApp.getUi().alert("Please select a single cell to insert the date.");
    
    // Optional: Reset the range selection to a single cell
    range.getCell(1, 1).activate();
  } else {
    // Insert the current date if one cell is selected
    range.setValue(new Date());
  }
}

2. Save and Enable the Script

  1. Click the floppy disk icon or press Ctrl + S (Windows) / Cmd + S (Mac) to save the script. Name it something descriptive like Insert Date Validation.
  2. Navigate to Triggers:
    • Click on the clock icon in the Apps Script editor.
    • Add a new trigger:
      • Function: onEdit
      • Event Source: From spreadsheet
      • Event Type: On edit
  3. Save the trigger and grant the necessary permissions when prompted.

3. Test the Script

  1. Return to your Google Sheet.
  2. Select multiple cells and attempt to edit or use the script. A pop-up message should appear.
  3. Select a single cell and edit it. The script will automatically insert the current date.

How It Works

  • The script uses the onEdit trigger, which fires whenever a user edits the sheet.
  • It checks the size of the range (getNumRows and getNumColumns):
    • If more than one cell is selected, a pop-up alert is displayed.
    • If a single cell is selected, the current date is inserted.

Bonus: Customizing the Message

To make the message more specific or user-friendly, you can edit the text in the SpreadsheetApp.getUi().alert function. For example:

SpreadsheetApp.getUi().alert("You can only select one cell at a time to insert the date. Please try again.");

Use Cases

  • Data Validation: Ensuring users follow specific workflows.
  • Error Prevention: Avoid accidental edits that span multiple cells.
  • User Training: Teach best practices directly in the spreadsheet.

Conclusion

This simple script adds a layer of control to your Google Sheets and helps users avoid mistakes when editing. You can further customize the script to suit your specific needs, such as allowing date insertion only in certain columns or rows.