- Spreadsheet docs
- 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
| Member | What 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.protectionPasswordSet | Whether 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.
| Option | Dialog box | Allowed by default |
|---|---|---|
selectLockedCells, selectUnlockedCells | Select locked cells, Select unlocked cells | Yes |
formatCells, formatColumns, formatRows | Format cells, columns, rows | No |
insertColumns, insertRows, insertHyperlinks | Insert columns, rows, hyperlinks | No |
deleteColumns, deleteRows | Delete columns, rows | No |
sort, autoFilter, pivotTables | Sort, Use AutoFilter, Use PivotTable reports | No |
objects, scenarios | Edit objects, Edit scenarios | Yes |
sheet.protect({ sort: '0', autoFilter: '0', formatCells: '0' }); // users may still sort, filter and format
sheet.protectionAllows('sort'); // true
Workbook structure
| Member | What 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.structurePasswordSet | Whether 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-operationwithreason: 'protection'. - Your code isn't stopped by protection:
range.values,range.formulas,range.styleand 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 returnfalse; 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.