0

I am trying to unprotect my sheet so I can remove autofilter and delete some rows. My plan from there is to create a table in power automate so i can read it into Power Apps.

How can I unprotect my sheet?

I am running the below, I assume it isnt working due to not having the password in the code. How can I format this to accept a password?

function main(workbook: ExcelScript.Workbook) {

    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.getProtection().unprotect();
    // Toggle auto filter on selectedSheet
    selectedSheet.getAutoFilter().remove();
    // Delete range 1: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. Again, I am assuming this is due to me not having the password in the script. I have the password but not sure where to input it.

I look at this example but no where to enter password .

Protect Worksheet in Office Scripts with Options

Updated error:

enter image description here

enter image description here

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

any help would be appreciated.

Jonnyboi
  • 505
  • 5
  • 19

1 Answers1

2

In order to use additional parameters, you need to make the call from Power Automate but obviously, you first need to add the parameter to your script.

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

So for 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 you've added it to your script, you can then reference it from PowerAutomate/LogicApps ...

Run Script

Note: Your code to use the workbook.getActiveWorksheet() may bring you unstuck, I'd be more specific if I were you given it needs to be executed from PA/LA. Obviously, I am making an assumption that you have more than one worksheet in your workbook.

Skin
  • 9,085
  • 2
  • 13
  • 29
  • thank you! Just curious - with power automate is there a way to insert the script to a workbook before running it? I copy the file using power automate, then I want to run the script in the newly copied file. Is this possible without having the script in the original file? – Jonnyboi Mar 21 '23 at 18:34
  • Looks like i am getting error `We were unable to run the script. Please try again. Workbook not found.` I wonder if this is because im copying the file over and the script is getting lost. I also put my password in the allocated area in power automate. – Jonnyboi Mar 21 '23 at 18:49
  • Scripts aren’t owned by workbooks, it’s not like VBA where they’re (typically) embedded in the workbook. They’re saved in your OneDrive and can be referenced by any workbook. Update your question to show us your flow. Be sure to include everything that’s relevant to assisting with the resolution of your issue. – Skin Mar 21 '23 at 19:46
  • thanks didnt know they are saved in onedrive. ive isolated the runscript power automate ot see if it would work, but it didnt. see updated error. – Jonnyboi Mar 21 '23 at 20:34
  • How did you select the workbook in the runscript action? Did you use the file selectors or just type it in? – Skin Mar 21 '23 at 20:59
  • I use the folder icon selector to find the file. – Jonnyboi Mar 21 '23 at 23:40
  • This is bizarre, it wont work for like 3 times, then 4th time i run it , it works. and repeat. I notice when i go in and select the file using the file selector it works. 2nd tiime runnin after that, fail. – Jonnyboi Mar 21 '23 at 23:52
  • Does the script change the state of the workbook to the point where the script fails? – Skin Mar 22 '23 at 00:14
  • No it does not. Could it to do with file type? It is using .xlsb. – Jonnyboi Mar 22 '23 at 13:01
  • Stumbled across this answer, it seemed to work : https://powerusers.microsoft.com/t5/Building-Flows/Running-a-script-is-hit-and-miss/td-p/929233 – Jonnyboi Mar 22 '23 at 13:49