Background

In collaborative spreadsheet editing scenarios, there is often a need to protect certain worksheets or specific ranges to prevent accidental modifications. For instance, in a financial report, administrators may want to lock formula areas or allow specific users to edit only certain ranges. To address this need, Univer provides a robust permission management module that easily supports worksheet and range protection.

\> Univer is an isomorphic full-stack framework for creating and editing spreadsheets, docs, and slides across client and server.

\> Follow Univer Github

\>Official Website Univer Go

\>Video Tutorial https://youtu.be/H0HXaStfuKM?si=kZdkl7iqcxijhfcZ

Solution

Univer's permission module allows you to control permissions at various levels, including the entire workbook, individual worksheets, and specific cell ranges. With simple API calls, developers can set various permission rules, such as read-only, full editing restrictions, or custom permission configurations.

The following will explain how to use Univer to implement worksheet and range protection through two typical use cases.

\> Refer to Univer official document - Permission control

Implementation

1. Setting Worksheet Permissions

With the permission module, you can set permissions for an entire worksheet to control whether users can edit its contents. Here's an example of how to do this:

async function setWorksheetPermission() {
    const workbook = univerAPI.getActiveWorkbook(); // Get the active workbook
    const unitId = workbook.getId(); // Get the workbook ID
    const subUnitId = workbook.getActiveSheet().getSheetId(); // Get the active sheet's ID
    const permission = univerAPI.getPermission(); // Get the permission module

    const worksheetEditPermission = permission.permissionPointsDefinition.WorksheetEditPermission;

    // Create the basic worksheet permission
    const permissionId = await permission.addWorksheetBasePermission(unitId, subUnitId);

    permission.sheetRuleChangedAfterAuth$.subscribe((currentPermissionId) => {
        if(currentPermissionId === permissionId){
            // Set the worksheet as non-editable
            permission.setWorksheetPermissionPoint(unitId, subUnitId, worksheetEditPermission, false);
        }
    })
}

Key Points

- addWorksheetBasePermission is used to create the basic permission for the worksheet.

- setWorksheetPermissionPoint allows you to fine-tune specific editing permissions.

- The delay ensures the basic permission initialization completes before applying further settings.

2. Setting Range Permissions

In some cases, you may only need to protect specific ranges of cells rather than the entire worksheet. Here's how you can implement range protection for multiple ranges:

async function setRangePermission() {
    const workbook = univerAPI.getActiveWorkbook(); // Get the active workbook
    const sheet = workbook.getActiveSheet(); // Get the active sheet
    const unitId = workbook.getId(); // Get the workbook ID
    const subUnitId = sheet.getSheetId(); // Get the sheet ID

    const range1 = sheet.getRange('A1:B3').getRange(); // The first protected range
    const range2 = sheet.getRange('C4:D5').getRange(); // The second protected range
    const ranges = [range1, range2]; // Multiple ranges to protect

    const permission = univerAPI.getPermission(); // Get the permission module
    const rangeProtectionPermissionEditPoint = permission.permissionPointsDefinition.RangeProtectionPermissionEditPoint;

    // Add range protection and get the returned rule ID and permission ID
    const res = await permission.addRangeBaseProtection(unitId, subUnitId, ranges);
    const { permissionId, ruleId } = res;

    permission.rangeRuleChangedAfterAuth$.subscribe((currentPermissionId)=>{
        if(currentPermissionId === permissionId){
            // Set the range protection as non-editable
            permission.setRangeProtectionPermissionPoint(unitId, subUnitId, permissionId, rangeProtectionPermissionEditPoint, false);
        }
    })
}

Key Points

- addRangeBaseProtection allows you to set protection for multiple ranges.

- The return value includes ruleId (a unique identifier for the protection rule) and permissionId (used to combine the permission points).

- setRangeProtectionPermissionPoint is used to set the specific permissions for each range, such as edit permissions.

Summary

Univer provides a flexible and powerful permission management system that can accommodate a wide range of collaborative use cases. With the two methods outlined above, you can easily implement:

1. Protection for entire worksheets, suitable for global read-only or lock scenarios.

2. Range-specific protection, suitable for fine-grained permission management.

This flexibility and ease of use allow developers to quickly build more secure and efficient spreadsheet collaboration systems with Univer. For more details or use cases, please refer to Univer official website [univer.ai](http://univer.ai)\.

Get rid of excel and deploy a private spreadsheet system

Univer Go Launches AI-Powered API Writing Assistance

Facade API — How Univer Enhances the Development Experience

How to Implement Worksheet or Range Protection in Univer