SOLVED

How to add Parameters in Worksheet Protection? Office Scripts

%3CLINGO-SUB%20id%3D%22lingo-sub-1788267%22%20slang%3D%22en-US%22%3EHow%20to%20add%20Paremeters%20in%20Worksheet%20Protection%3F%20Office%20Scripts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788267%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20allow%20formatting%20of%20columns%3F%20I%20tried%20to%20insert%20the%20WorksheetProtectionOptions%20but%20I%20keep%20failing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3Efunction%20main(workbook%3A%20ExcelScript.Workbook)%20%7B%0A%20%20%20%20let%20sheet%20%3D%20workbook.getWorksheet(%22By%20Item%22)%3B%0A%20%20%20%20sheet.getProtection().protect(ExcelScript.WorksheetProtectionOptions.allowFormatColumns)%3B%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20link.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fjavascript%2Fapi%2Foffice-scripts%2Fexcelscript%2Fexcelscript.worksheetprotection%3Fview%3Doffice-scripts%23protect-options--password-%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fjavascript%2Fapi%2Foffice-scripts%2Fexcelscript%2Fexcelscript.worksheetprotection%3Fview%3Doffice-scripts%23protect-options--password-%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1788267%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1788655%22%20slang%3D%22de-DE%22%3ESubject%3A%20How%20to%20add%20Paremeters%20in%20Worksheet%20Protection%3F%20Office%20Scripts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1788655%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727575%22%20target%3D%22_blank%22%3E%40Erut313%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI'm%20not%20familiar%20with%20Excel%20Online%2C%20but%20here%20is%20a%20little%20bit%20of%20information%20that%20might%20help%20you%20in%20your%20plans.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EIf%20not%20please%20just%20ignore%20it.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EYou%20cannot%20create%20a%20sheet%20protection%20in%20the%20WebApp%20and%20you%20cannot%20remove%20any%20existing%20sheet%20protection.%20%3CSPAN%3EHowever%2C%20you%20can%20now%20upload%20folders%20with%20protected%20sheets%20and%20since%20this%20update%20they%20can%20also%20be%20opened.%3C%2FSPAN%3E%20%3CSPAN%3EEditing%20is%20possible%20as%20far%20as%20the%20set%20protection%20allows.%3C%2FSPAN%3E%20I%20have%20learned%20that%20so%20far%2C%20but%20if%20it%20is%20different%2C%20I%20am%20very%20happy%20to%20be%20taught.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CH1%20id%3D%22toc-hId-559888551%22%20id%3D%22toc-hId-559888551%22%20id%3D%22toc-hId-559888551%22%20id%3D%22toc-hId-559888551%22%20id%3D%22toc-hId-559888551%22%20id%3D%22toc-hId-559888551%22%3E%3CFONT%20size%3D%223%22%3EEdit%20a%20workbook%20that%20contains%20features%20unsupported%20by%20Excel%20for%20the%20web%3C%2FFONT%3E%3C%2FH1%3E%3CP%3E%3CFONT%20size%3D%223%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fedit-a-workbook-that-contains-features-unsupported-by-excel-for-the-web-7220105d-97ad-4c35-95c8-58d4bbfd14af%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fedit-a-workbook-that-contains-features-unsupported-by-excel-for-the-web-7220105d-97ad-4c35-95c8-58d4bbfd14af%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1789336%22%20slang%3D%22de-DE%22%3ESubject%3A%20How%20to%20add%20Paremeters%20in%20Worksheet%20Protection%3F%20Office%20Scripts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1789336%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727575%22%20target%3D%22_blank%22%3E%40Erut313%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ECertainly%20not%20the%20right%20person%20to%20help%20you%20with%20Javascript.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EBut%20I%20would%20like%20to%20provide%20you%20with%20information%20that%20might%20help%20you.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EIf%20not%20please%20just%20ignore%20my%20post.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EThank%20you%20for%20your%20time%20and%20patience%20and%20wish%20you%20much%20success%20in%20your%20project.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22hljs-keyword%22%3Evar%3C%2FSPAN%3E%20sheet%20%3D%20SpreadsheetApp.getActiveSpreadsheet().getSheetByName(%3CSPAN%20class%3D%22hljs-string%22%3E%22TRAVAUX%22%3C%2FSPAN%3E)%3B%0A%3CSPAN%20class%3D%22hljs-keyword%22%3Evar%3C%2FSPAN%3E%20debug%20%3D%20sheet.getName()%3B%0A%3CSPAN%20class%3D%22hljs-keyword%22%3Evar%3C%2FSPAN%3E%20protections%20%3D%20sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)%3B%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F60092877%2Fsheet-getprotections-no-longer-works%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F60092877%2Fsheet-getprotections-no-longer-works%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1791031%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20Parameters%20in%20Worksheet%20Protection%3F%20Office%20Scripts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F727575%22%20target%3D%22_blank%22%3E%40Erut313%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20for%20the%20post.%20I%20replied%20here%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F64393820%2Fprotect-worksheet-in-office-scripts-with-options%2F64395382%2364395382%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F64393820%2Fprotect-worksheet-in-office-scripts-with-options%2F64395382%2364395382%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

How can I allow formatting of columns? I tried to insert the WorksheetProtectionOptions but I keep failing.

 

 

 

 

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getWorksheet("By Item");
    sheet.getProtection().protect(ExcelScript.WorksheetProtectionOptions.allowFormatColumns);
}

 

 

 

 

Here is the link.

 

https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.worksheetprot...

5 Replies
Highlighted

@Erut313 

I'm not familiar with Excel Online, but here is a little bit of information that might help you in your plans. If not please just ignore it.

 

You cannot create a sheet protection in the WebApp and you cannot remove any existing sheet protection. However, you can now upload folders with protected sheets and since this update they can also be opened. Editing is possible as far as the set protection allows. I have learned that so far, but if it is different, I am very happy to be taught.

Edit a workbook that contains features unsupported by Excel for the web

https://support.microsoft.com/en-gb/office/edit-a-workbook-that-contains-features-unsupported-by-exc...

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

 

Highlighted

@Erut313 

Certainly not the right person to help you with Javascript. But I would like to provide you with information that might help you. If not please just ignore my post.

Thank you for your time and patience and wish you much success in your project.

 

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TRAVAUX");
var debug = sheet.getName();
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

 https://stackoverflow.com/questions/60092877/sheet-getprotections-no-longer-works

 

Nikolino

I know I don't know anything (Socrates)

Highlighted
Best Response confirmed by Erut313 (Occasional Contributor)
Highlighted
I was pleased that I could help you at least indirectly.
I wish you a nice day / night with lots of health, joy and love.

Thank you for your patience and time.

Nikolino
I know I don't know anything (Socrates)