Automatically Protect Finalized Rows in Google Sheets with Apps Script

https://github.com/lsvekis/Apps-Script-Code-Snippets

Accidentally overwriting data is one of the most common problems in shared Google Sheets.

Someone edits a completed row.
A formula gets replaced.
A “final” value quietly changes.

Google Sheets has Protected Ranges, but managing them manually doesn’t scale.

In this tutorial, you’ll build a Google Apps Script that:

✅ Watches for edits
✅ Detects when a row is marked Complete
✅ Automatically protects that entire row
✅ Still allows sheet owners to edit if needed

This turns your spreadsheet into a workflow-aware system, not just a table.

---

When This Is Useful

This pattern is ideal for:

• Task trackers
• Student submissions
• Expense approvals
• Content publishing schedules
• Inventory or asset logs
• Any sheet with a “finalized” state

If a row is done — it should stop changing.

---

How the Automation Works

1. A user edits a Status column
2. If the value becomes "Complete":
   • The script protects the entire row
3. If the value is changed away from "Complete":
   • The protection is removed

All of this happens automatically on edit.

---

Example Sheet Structure

Row	Task Name	Owner	Status	Notes
1	Header
2	Video edit	Alex	In Progress
3	Blog post	Sam	Complete	Final

When row 3 becomes Complete, it locks.

---

Step 1: Open Apps Script

1. Open your Google Sheet
2. Go to Extensions → Apps Script
3. Replace any code with the script below
4. Save the project

---

Step 2: The Full Apps Script Code

/**
 * Auto-Protect Rows When Status Is "Complete"
 *
 * - Watches edits in a specific column
 * - Protects the entire row when finalized
 * - Removes protection if status changes
 */

// ========= CONFIG =========
const CONFIG = {
  sheetNames: [], // empty = all sheets, or ["Tasks", "Tracker"]
  statusColumn: 4, // Column number where status lives (A=1, B=2, ...)
  headerRows: 1,
  finalizedValue: "complete", // comparison is case-insensitive
  descriptionPrefix: "Auto-protected (status complete)"
};

// ========= TRIGGER =========
function onEdit(e) {
  if (!e || !e.range) return;

  const range = e.range;
  const sheet = range.getSheet();
  const row = range.getRow();
  const col = range.getColumn();

  // Only single-cell edits
  if (range.getNumRows() !== 1 || range.getNumColumns() !== 1) return;

  // Sheet filter
  if (CONFIG.sheetNames.length > 0 &&
      !CONFIG.sheetNames.includes(sheet.getName())) {
    return;
  }

  // Ignore headers
  if (row <= CONFIG.headerRows) return;

  // Only watch the status column
  if (col !== CONFIG.statusColumn) return;

  const newValue = String(range.getValue()).trim().toLowerCase();

  if (newValue === CONFIG.finalizedValue) {
    protectRow_(sheet, row);
  } else {
    unprotectRow_(sheet, row);
  }
}

// ========= HELPERS =========
function protectRow_(sheet, row) {
  const range = sheet.getRange(row, 1, 1, sheet.getMaxColumns());
  const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  // Avoid duplicate protections
  for (const p of protections) {
    if (p.getRange().getA1Notation() === range.getA1Notation()) {
      return;
    }
  }

  const protection = range.protect();
  protection.setDescription(`${CONFIG.descriptionPrefix} – Row ${row}`);

  // Only owner can edit by default
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

function unprotectRow_(sheet, row) {
  const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  for (const p of protections) {
    const r = p.getRange();
    if (r.getRow() === row && r.getNumRows() === 1) {
      if (p.getDescription()?.startsWith(CONFIG.descriptionPrefix)) {
        p.remove();
      }
    }
  }
}

---

Step 3: Customize the Script

Change the Status Column

statusColumn: 3, // Column C

Use a Different Trigger Word

finalizedValue: "approved"

Restrict to Specific Sheets

sheetNames: ["Tasks", "Approvals"]

---

Permissions & Triggers

This script uses a simple onEdit trigger, which:

• Runs automatically
• Requires authorization the first time
• Works best when you’re the owner

💡 For shared team use, you can convert it to an installable trigger:

1. Apps Script → Triggers (⏰ icon)
2. Add trigger
3. Function: onEdit
4. Event source: From spreadsheet
5. Event type: On edit

---

Why This Pattern Is Powerful

This isn’t just a “script” — it’s a design pattern:

• Data state → system behavior
• Spreadsheet → lightweight app
• Manual rules → automated governance

Once you learn this approach, you can extend it to:

• Lock columns instead of rows
• Lock based on user role
• Display warnings instead of locking
• Send emails when rows finalize
• Log changes to another sheet

---

Next Ideas (If You Want to Extend It)

• 🔔 Show a toast notification when a row locks
• 🧾 Add an “Unlocked by Owner” override
• 🔄 Re-check the whole sheet with a menu item
• 📊 Log finalized rows to an audit sheet