How to Create a Custom Lock Menu in Google Sheets - The Magic of Automation

Introduction:

In many collaborative environments, managing access to specific data in Google Sheets is critical for maintaining data accuracy and privacy. This article demonstrates how to create a system where users can edit specific cells only after providing their details.

We'll achieve this using Google Apps Script, a powerful automation tool for Google Sheets.

Setting Up the Google Sheet:

To get started, create a Google Sheet, and structure it to include user details. For this example, we'll use columns for "Name," "Email," and "Details." The "Details" column will be used to indicate whether users have provided their details.

The Role of Google Apps Script:

Google Apps Script is at the heart of our solution. This scripting tool allows us to automate tasks in Google Sheets. In this case, it will help control cell access and track user edits.

Script to Manage Cell Access: We'll begin by creating a script that checks if users have provided their details. This script will grant edit access to specific cells once a user's details are confirmed.

function onEdit(e) {
  var sheet = e.source.getSheetByName("YourSheetName");
  var editedRange = e.range;
  var username = Session.getActiveUser().getEmail();
  var timestamp = new Date();

  // Check if user's details are in place.
  var detailsRange = sheet.getRange("B2:B10"); // Adjust to your specific details range.
  var userRowIndex = detailsRange.createTextFinder(username).findNext().getRow();

  if (userRowIndex > 0) {
    // Grant edit access to specific cells (e.g., columns C and D).
    var editRange = sheet.getRange(userRowIndex, 3, 1, 2);
    var protection = editRange.protect();
    protection.removeEditors(protection.getEditors());
    protection.addEditor(username);
  }
}

This script, when triggered by an edit event, checks if a user's details are in the "Details" column. If found, it grants edit access to specific cells (e.g., columns C and D). Users can edit only after their details are confirmed.

Making a Special Google Sheet With a "Lock Cells" Button

Imagine you have a special Google Sheet where you want to let people fill in some information, but only after they tell us who they are. This article will show you how to do it, and we'll even add a "Lock Cells" button to make it easier!

Step 1: Create Your Google Sheet

• First, open Google Sheets.
• Click on extensions
• Make a new sheet, and add some columns for names, emails, and other stuff.

Step 2: Use a Magic Script (Google Apps Script)

• Google Apps Script is like magic for your Google Sheet. It can do things for you automatically.

• Here's a special script we'll use. It checks if someone has shared their name and email.

  
  

THE NAME OF THE SHEET MUST MATCH WITH THE “YourSheetName“ IN THE CODE

function onEdit(e) {
  var sheet = e.source.getSheetByName("YourSheetName");
  var editedRange = e.range;
  var username = Session.getActiveUser().getEmail();
  var timestamp = new Date();

  // Check if someone shared their details.
  var detailsRange = sheet.getRange("B2:B10");
  var userRowIndex = detailsRange.createTextFinder(username).findNext().getRow();

  if (userRowIndex > 0) {
    // Let them edit special cells (e.g., columns C and D).
    var editRange = sheet.getRange(userRowIndex, 3, 1, 2);
    var protection = editRange.protect();
    protection.removeEditors(protection.getEditors());
    protection.addEditor(username);
  }
}

Step 3: Add a "Lock Cells" Button

• We'll make it even easier. Let's add a "Lock Cells" button to your Google Sheet. So, when someone wants to lock their cells, they just need to click the button.

  
  

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Lock Cells Menu')
    .addItem('Lock Your Cells', 'lockYourCells')
    .addToUi();
}

function lockYourCells() {
  // This is where we lock the cells when someone clicks the button.
  // They can only click it after sharing their details.
}

Here is the complete code if you do not want to create two separate codes.

Save the script, and click on “Run.“ The system will give you the prompt below.

The complete code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var currentUserEmail = Session.getActiveUser().getEmail();
  
  if (currentUserEmail === "[email protected]") {
    ui.createMenu('Lock Menu')
      .addItem('Lock Cells', 'showDialog')
      .addToUi();
  }
}

function showDialog() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt(
    'Lock Cells',
    'Enter the cell range to lock (e.g., A1:A10):',
    ui.ButtonSet.OK_CANCEL
  );

  if (result.getSelectedButton() == ui.Button.OK) {
    var rangeToLock = result.getResponseText();
    lockCells(rangeToLock);
  }
}

function lockCells(rangeToLock) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = spreadsheet.getActiveSheet();

  // Lock the specified cell range.
  var protection = activeSheet.getRange(rangeToLock).protect();
  protection.removeEditors(protection.getEditors());
  protection.addEditor(Session.getActiveUser());

  // Apply a background color to indicate locked cells.
  var lockedColor = '#e6e6e6'; // Light gray color.
  var range = activeSheet.getRange(rangeToLock);
  range.setBackground(lockedColor);
  
  // Create a time-based trigger to unlock the cells after 3 minutes.
  ScriptApp.newTrigger("unlockCells")
    .timeBased()
    .after(3 * 60 * 1000)  // 3 minutes in milliseconds
    .create();
}

function unlockCells() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = spreadsheet.getActiveSheet();
  
  // Define the range of cells to unlock, matching the locked range.
  var rangeToUnlock = "A1:A10";  // Adjust this to your specific range.

  // Remove the protection and clear the background color.
  var protection = activeSheet.getRange(rangeToUnlock).protect();
  protection.remove();
  activeSheet.getRange(rangeToUnlock).setBackground('');
}

In this script:

• The admin can click "Lock Cells" to lock the specified cells and apply the background color.

• After locking, a time-based trigger is created to run the unlockCells function after 3 minutes.

• Set the background color after effectively unlocking the cells.

• Only the admin/you will see the LOCK MENU

Replace "[email protected]" With the Actual Admin's Email Address in the Code.

Step 4: Make a Schedule

• You want the cells to lock themselves at a certain time, like bedtime.

• We'll use a special clock (time-driven trigger) to do that.

Leave it as “Head.“

Step 5: Share the Sheet

• Now, you can share your Google Sheet with your friends or team members.
• They can use the "Lock Cells" button to lock their cells.

Conclusion:

With this magic system and the "Lock Cells" button, you can control who gets to edit your Google Sheet. It's like inviting friends to your party, but they need to tell you their names before they can play with your toys.

I would be glad to help if you need any assistance.