Unlocking Password-Protected Worksheet in Microsoft Office Scripts

Question

Unlocking Password-Protected Worksheet in Microsoft Office Scripts

I am in the process of removing protection from my spreadsheet so that I can eliminate autofilter and delete certain rows. My ultimate goal is to create a table in power automate, allowing me to import it into Power Apps.

How do I go about unprotecting my sheet?

The code snippet below is not working as expected, likely because it does not include the necessary password. How can I modify it to accept a password?

function main(workbook: ExcelScript.Workbook) {

    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.getProtection().unprotect();
    // Turn off autofilter on selectedSheet
    selectedSheet.getAutoFilter().remove();
    // Delete rows 1 through 6 on selectedSheet
    selectedSheet.getRange("1:6").delete(ExcelScript.DeleteShiftDirection.up);
}

Error:

Line 4: WorksheetProtection unprotect: The argument is invalid or missing or has an incorrect format.

It seems like the issue may be related to the absence of the password in the script. I have the password but unsure where to input it.

I reviewed this example but could not find a place to enter the password.

Protect Worksheet in Office Scripts with Options

Updated error:

https://i.sstatic.net/4Dg6f.png

https://i.sstatic.net/3vY9R.png

Error message:

{ "message": "We were unable to run the script. Please try again.\nWorkbook not found.\r\nclientRequestId: b29bf86c-8a12-4ab3-b639-2350a0022b89", "logs": [] }

Any assistance would be greatly appreciated.

excel typescript office-scripts

Answer 1

Answer №1

If you want to utilize additional parameters, the key is to initiate the call from Power Automate. However, before doing so, ensure that you integrate the parameter into your script.

https://learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-parameters-returns

Here is an example of how to incorporate it into your script:

function main(workbook: ExcelScript.Workbook, password: string) {
    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.getProtection().unprotect(password);

    // Toggle auto filter on selectedSheet
    selectedSheet.getAutoFilter().remove();
    
    // Delete range 1:6 on selectedSheet
    selectedSheet.getRange("1:6").delete(ExcelScript.DeleteShiftDirection.up);
}

Once integrated into the script, you can easily refer to it from PowerAutomate or LogicApps.

https://i.sstatic.net/ISWOI.png

Note: When using workbook.getActiveWorksheet(), be cautious as it must be executed correctly within PA/LA, especially if there are multiple worksheets in the workbook.

Answer 2

If you want to utilize additional parameters, the key is to initiate the call from Power Automate. However, before doing so, ensure that you integrate the parameter into your script.

https://learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-parameters-returns

Here is an example of how to incorporate it into your script:

function main(workbook: ExcelScript.Workbook, password: string) {
    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.getProtection().unprotect(password);

    // Toggle auto filter on selectedSheet
    selectedSheet.getAutoFilter().remove();
    
    // Delete range 1:6 on selectedSheet
    selectedSheet.getRange("1:6").delete(ExcelScript.DeleteShiftDirection.up);
}

Once integrated into the script, you can easily refer to it from PowerAutomate or LogicApps.

https://i.sstatic.net/ISWOI.png

Note: When using workbook.getActiveWorksheet(), be cautious as it must be executed correctly within PA/LA, especially if there are multiple worksheets in the workbook.

Unlocking Password-Protected Worksheet in Microsoft Office Scripts

Answer №1

Similar questions

Delete information based on its unique identifier

Creating recursive interfaces in TypeScript allows for defining reusable structures that reference themselves within

Enforce numerical input in input field by implementing a custom validator in Angular 2

Tips on displaying hyperlinks within a text area in an Angular application

How to calculate the sum of all values in a FormArray in Angular

How can I center align my loader inside app-root in Angular2+?

Learn how to display data from the console onto an HTML page using Angular 2

Steps for assigning a value from an enumerated type

Typescript error in React: The element is implicitly of type any because a string expression cannot be used to index type {}

What is causing the failure of this polymorphic assignment within a typed observableArray in Knockout?

Angular TSLint: Proceed to the following stage despite any encountered errors

Error encountered when attempting to retrieve token from firebase for messaging

How to conditionally make a property optional in Typescript depending on the value of another property

Angular - Executing a function in one component from another

Simulating chained responses in Express using JEST

Customizing font color upon hover in Next.js and Tailwind.css

How to display a TypeScript map in a React JSX component

Having difficulty getting the useParams hook in React to update when the URL changes

Loop through JSON results in Ionic using Angular

Tips for showcasing JSON data in an HTML table or list