1. Spreadsheet docs
  2. Developers

Protection

Protect sheets and the workbook's structure from code, with or without a password, leave chosen cells editable, and check what users may do.

Protection keeps users from changing what they shouldn't: locked cells on a protected sheet, and the list of sheets of a protected workbook. It works as in Excel and travels with the saved file, so a form you protect in 8sheets stays protected in Excel, and the other way round.

Protection limits the user, not your code, and it is not encryption: anyone with the file can read its contents. To keep data from people, don't send it to their browser; to stop all edits in a view, see Read-only workbooks.

Protect a form

Unlock the cells people fill in, then protect the sheet. Everything else becomes read-only for the user.

JavaScript

import { mountWorkbook } from '@8sheets/core';

const wb = await mountWorkbook(document.querySelector('#sheet'));
const sheet = wb.activeSheet;
sheet.range('A1').values = [['Item', 'Price', 'Discount'], ['Laptop', 1200, 0], ['Monitor', 300, 0]];
sheet.range('C2:C3').style = { unlocked: true, fill: '#FFF2CC' };   // the only editable cells

await sheet.protectWithPassword('s3cret', { sort: '0', autoFilter: '0' });   // '0' allows the action
await wb.protectStructure();                                                 // no adding or deleting sheets

console.log(sheet.isProtected, sheet.protectionPasswordSet, sheet.protectionAllows('sort'));   // true true true
wb.events.on('invalid-operation', (e) => { if (e.reason === 'protection') console.log('blocked:', e.action); });

React

import { Sheet } from '@8sheets/react';

async function protectForm(wb) {
  const sheet = wb.activeSheet;
  sheet.range('C2:C20').style = { unlocked: true, fill: '#FFF2CC' };
  await sheet.protectWithPassword('s3cret');
  await wb.protectStructure();
}

export function PriceForm() {
  return (
    <div style={{ height: 480 }}>
      <Sheet onReady={protectForm} onInvalidOperation={(e) => console.log('blocked:', e.action, e.reason)} />
    </div>
  );
}

Vue

<script setup>
import { Sheet } from '@8sheets/vue';

async function protectForm(wb) {
  const sheet = wb.activeSheet;
  sheet.range('C2:C20').style = { unlocked: true, fill: '#FFF2CC' };
  await sheet.protectWithPassword('s3cret');
  await wb.protectStructure();
}
</script>

<template>
  <Sheet style="height: 480px" @ready="protectForm" @invalid-operation="(e) => console.log('blocked:', e.action, e.reason)" />
</template>

Angular

import { Component } from '@angular/core';
import { SheetComponent, SheetEventPayload, SheetsWorkbook } from '@8sheets/angular';

@Component({
  selector: 'app-price-form',
  imports: [SheetComponent],
  template: `<eight-sheet class="grid" (ready)="protectForm($event)" (invalidOperation)="blocked($event)" />`,
  styles: `.grid { height: 480px; }`,
})
export class PriceForm {
  async protectForm(wb: SheetsWorkbook) {
    const sheet = wb.activeSheet;
    sheet.range('C2:C20').style = { unlocked: true, fill: '#FFF2CC' };
    await sheet.protectWithPassword('s3cret');
    await wb.protectStructure();
  }

  blocked(e: SheetEventPayload) {
    console.log('blocked:', e['action'], e['reason']);
  }
}

Sheet protection

MemberWhat it does
sheet.protect(options)Protects the sheet with no password
await sheet.protectWithPassword(password, options)Protects it with a password ('' for none), as Review > Protect Sheet does
await sheet.unprotectWithPassword(password)Removes protection when the password is right, or when there is none; resolves to true or false
sheet.unprotect()Removes protection without asking for a password
sheet.isProtected, sheet.protectionPasswordSetWhether the sheet is protected, and with a password
sheet.protectionAllows(action)Whether users may do something now; true for anything on an unprotected sheet

Every cell is locked by default. Cells styled { unlocked: true } stay editable on a protected sheet.

Allowing actions. options takes the attribute names of the file format's sheet protection, which are also the boxes of the Protect Sheet dialog. Values follow the file format, as strings: '0' allows the action, '1' forbids it.

OptionDialog boxAllowed by default
selectLockedCells, selectUnlockedCellsSelect locked cells, Select unlocked cellsYes
formatCells, formatColumns, formatRowsFormat cells, columns, rowsNo
insertColumns, insertRows, insertHyperlinksInsert columns, rows, hyperlinksNo
deleteColumns, deleteRowsDelete columns, rowsNo
sort, autoFilter, pivotTablesSort, Use AutoFilter, Use PivotTable reportsNo
objects, scenariosEdit objects, Edit scenariosYes
sheet.protect({ sort: '0', autoFilter: '0', formatCells: '0' });   // users may still sort, filter and format
sheet.protectionAllows('sort');   // true

Workbook structure

MemberWhat it does
await wb.protectStructure(password)Protects the structure, as Review > Protect Workbook does: users can't add, move, rename, hide or delete sheets. The password is optional
await wb.unprotectStructure(password)Removes it; resolves to false when the password is wrong
wb.isStructureProtected, wb.structurePasswordSetWhether the structure is protected, and with a password

The API's own sheet calls, such as wb.addSheet, still work on a protected structure, because they act for your app.

What protection does and doesn't stop

  • The user can't change locked cells, or do what the options forbid, on a protected sheet; refused actions fire invalid-operation with reason: 'protection'.
  • Your code isn't stopped by protection: range.values, range.formulas, range.style and the other cell writes go through. The sheet-feature calls (validation, conditional formats, notes, links, sparklines) are refused on a protected sheet of a mounted workbook and return false; outline groups are refused in a headless one too.
  • Passwords are stored only as Excel's salted SHA-512 hash, as Excel stores them, so Excel accepts the same password.
  • Protection calls aren't undo steps, as in Excel.
  • Password to open (encryption of the whole file) is set by users under File > Password to Open; the API has no call for it yet.

In the app

Users protect sheets and workbooks on the Review tab; see Review and protection. From your own button, wb.commands.execute('ui.protectSheet') opens the Protect Sheet dialog.