Workbook and Worksheet Protection - Oracle Cloud Loaders

API Wizard supports Excel's native workbook and worksheet protection features. These tools enable administrators to lock down both entire workbooks and specific worksheet areas to prevent users from making unintended or unauthorized changes.

This document covers:

• Workbook Protection: To hide and lock sheets not meant for user access (e.g., LOV Data, Column Mapping).
• Worksheet Protection: To lock critical loader elements while maintaining usability for data entry.

1. Workbook Protection

Workbook protection is useful for locking supporting sheets such as the LOV Data and Column Mapping worksheets. Once hidden and protected, users cannot access or edit these sheets. Additional supporting sheets (e.g., for lookup values) can also be protected.

1.1 Steps to Protect a workbook

1) At the bottom, right-hand side of Excel, hover over the worksheet name to hide, perform a right-mouse-click, and then select the 'Hide' option.

In the screenshot below, we are hiding the LOV Data worksheet.

2) Repeat for each worksheet to be hidden.  API Wizard suggests minimally hiding the:

• • "LOV Data" worksheet
  • "Column Mapping" worksheet

3) After hiding the desired worksheets, navigate to the "Review" tab in Excel and select the "Protect Workbook" toolbar icon.

 4) Excel will provide a prompt to enter a workbook password.  Enter the password and protect the workbook for "Structure" as shown below.

⚠️ Important: If the password is lost, API Wizard cannot recover it. You will need to revert to your original ("Gold") version.

2. Worksheet Protection

Starting with version 3.04 of Cloud Support, API Wizard supports Excel worksheet protection to prevent accidental or intentional modifications that could compromise loader functionality. This feature is designed to protect structural components such as header parameters, detail rows, and formula rows, while preserving usability for end users.

Why Use Worksheet Protection?

Protecting a worksheet prevents users from:

• Modifying header parameter definitions
• Altering the detail parameter row
• Changing or breaking formulas in the formula row
• Deleting rows or columns necessary for loader operation

This allows organizations to lock down critical areas while still allowing day-to-day data entry.

Note:

This setup is intended to be done once by an admin or IT team member. End users do not need to manage protection or passwords.

To protect the worksheet, follow the steps below:

Step 1 – Unlock all cells

1) Press Ctrl + A to select the entire worksheet.

2) With all cells highlighted, right-mouse-click and choose "Format Cells"

3) Navigate to the "Protection" tab in the Format Cells form

4) Click the Locked checkbox until it appears blank (unchecked and not filled).

To be clear, there should be no value in the Locked toggle box before clicking OK.

5) Click OK

Step 2 – Lock Critical Elements

Identify and select the following elements for protection:

• Header parameter names
• Detail parameter row
• Parameter grouping row (typically above the detail row)
• LOV and mandatory field tags (typically one or two rows above the detail row)
• Formula row

1) To lock the header parameter names, highlight the header parameters, right-mouse-click, select "Format Cells", go to the "Protection" tab and then click on the Locked toggle box so that it shows a checkmark.

In the screenshot below the parameters in column B and C and in rows 3,4, and 5 have been highlighted and are being locked:

 

Note that if you want to enforce certain parameters values, such as the Environment name or defaulting the user name to DeviceFlow, that can also be done.

2) To lock the detailed parameter row, the formula row, and any related rows, highlight both rows, right-mouse-click, select "Format Cells", go to the "Protection" tab and then click on the Locked toggle box so that it shows a checkmark.

3) You can additionally lock any other cells as needed

Step 3 – Enable Sheet Protection

1) Go to Excel’s Review tab and select Protect Sheet. This will open a form where you choose the elements to be protected. 

2) Enter a password

3) Fill out as shown in the screenshot below and click OK

 ⚠️ Important: This password cannot be recovered by API Wizard. If lost, you must revert to an original unprotected version.

Step 4 – Sync API Wizard

1) Enter keyboard shortcut combination CTRL ALT P.  This will open a prompt which asks you to enter the "Sheet protection password".  Enter the value from Step 3 and click OK.

2) Save the workbook

Functional Considerations

This worksheet protection setup offers a balance between control and usability. It safeguards critical areas while preserving user flexibility where it matters most.  While protection enhances stability, some trade-offs are necessary:

Users Can Insert Rows

Possible Issue: Users might insert a row in the wrong place (e.g., between the detail parameter row and the formula row), which could affect functionality.

Why It’s Allowed: Row insertion is a common user task, especially in the data entry area. Excel does not support restricting row insertion to specific regions.

Users Cannot Insert Columns

Possible Issue: Users cannot create reference-only columns within the loader.

Why It’s Disallowed: Inserting columns is a frequent cause of loader breakage. Column insertion is globally disabled to preserve layout integrity.