Excel Automate Script (Add Password)

Question

Hello,&nbsp;I have created a simple Power Query to combine a number of tabs together. I would also like users to be able to refresh when they update data. But I also want to Password Protect the Combined Data Tab.&nbsp;I have recorded a script to refresh, but I don't know where I should add the Password into the Script below, can anyone offer a solution.&nbsp;&nbsp;Many Thanks, Sandra&nbsp;function&nbsp;main(workbook:&nbsp;ExcelScript.Workbook)&nbsp;{&nbsp;&nbsp;let&nbsp;selectedSheet&nbsp;=&nbsp;workbook.getActiveWorksheet();&nbsp;&nbsp;//&nbsp;Unprotect&nbsp;sheet&nbsp;Combined&nbsp;List&nbsp;&nbsp;selectedSheet.getProtection().unprotect();&nbsp;&nbsp;//&nbsp;Refresh&nbsp;all&nbsp;data&nbsp;connections&nbsp;&nbsp;workbook.refreshAllDataConnections();&nbsp;&nbsp;//&nbsp;Protect&nbsp;sheet&nbsp;Combined&nbsp;List.&nbsp;Passwords&nbsp;are&nbsp;not&nbsp;recorded.&nbsp;Add&nbsp;a&nbsp;password&nbsp;below&nbsp;if&nbsp;needed&nbsp;&nbsp;selectedSheet.getProtection().protect({&nbsp;allowAutoFilter:&nbsp;false,&nbsp;allowDeleteColumns:&nbsp;false,&nbsp;allowDeleteRows:&nbsp;false,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;allowEditObjects:&nbsp;true,&nbsp;allowEditScenarios:&nbsp;true,&nbsp;allowFormatCells:&nbsp;false,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;allowFormatColumns:&nbsp;false,&nbsp;allowFormatRows:&nbsp;false,&nbsp;allowInsertColumns:&nbsp;false,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;allowInsertHyperlinks:&nbsp;false,&nbsp;allowInsertRows:&nbsp;false,&nbsp;allowPivotTables:&nbsp;false,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;allowSort:&nbsp;false,&nbsp;selectionMode:&nbsp;ExcelScript.ProtectionSelectionMode.normal&nbsp;},&nbsp;"");}

jkpieterse · Answer

Rozz246&nbsp;That is very simple:
function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Unprotect sheet Combined List
  selectedSheet.getProtection().unprotect("Password");
  // Refresh all data connections
  workbook.refreshAllDataConnections();
  // Protect sheet Combined List. Passwords are not recorded. Add a password below if needed
  selectedSheet.getProtection().protect({ allowAutoFilter: false, allowDeleteColumns: false, allowDeleteRows: false,
            allowEditObjects: true, allowEditScenarios: true, allowFormatCells: false,
            allowFormatColumns: false, allowFormatRows: false, allowInsertColumns: false,
            allowInsertHyperlinks: false, allowInsertRows: false, allowPivotTables: false,
            allowSort: false, selectionMode: ExcelScript.ProtectionSelectionMode.normal }, "Password");
}

Forum Discussion

Excel Automate Script (Add Password)

1 Reply

Resources