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

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

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

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:

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

Step 4: Make a Schedule

Leave it as “Head.“

Step 5: Share the Sheet

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.