Sheet protection: "Format Cells" available in UI but not programmatically

Sheet protection: "Format Cells" available in UI but not programmatically

Posted by: eberridge on 3 June 2025, 2:07 pm EST

• 

  eberridge

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 3 June 2025, 2:07 pm EST

  When I protect a sheet, I see an option to allow formatting cells in the Protect Sheet dialog. I need the ability to do this programmatically, but I see nothing related to ‘formatCells’ in the publicly available properties of sheet.options.protectionOptions. Is this possible?

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 4 June 2025, 6:40 am EST - Updated 4 June 2025, 6:45 am EST

  Hi,

  It is possible to set the Format Cells option of Protect Sheet Dialog programmatically. Please refer to the code snippet below that illustrates the same:

  spread.getActiveSheet().options.protectionOptions.formatCells = true/false;

  You can further refer to the attached sample that uses the above code line and control the Format Cells option programmatically (see below).

  Please feel free to reach out if you require any additional guidance.

  Sample: https://jscodemine.mescius.io/share/4wLZGgdJLk2eYT3_n0yw5A/?defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fapp.js"]%2C"ActiveFile"%3A"%2Fapp.js"}

  Gif:

  Regards,

• 

  eberridge

  • Post Options:
  • Link
    Copy

  Posted 4 June 2025, 12:50 pm EST

  Ok thank you. It appears that the TypeScript definition is missing this property. I’ll try doing it like this:

  (sheet.options.protectionOptions as any).formatCells = true; // allow formatting cells

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 5 June 2025, 2:07 am EST - Updated 5 June 2025, 2:12 am EST

  Hi,

  The protectionOptions is an empty object and is not typed for any of the properties available publicly, therefore setting the formatCells option to a boolean value will work similar in TypeScript as in JavaScript.

  However, to remove the type errors, the formatCells can be specified as:

  (sheet.options.protectionOptions as any).formatCells = true/false;

  You can further refer to the attached sample that uses the above code snippet and set the formatCells as required (see below).

  Please feel free to reach out if you encounter any further issues or require additional guidance.

  Sample: https://jscodemine.mescius.io/share/H0t-IUutZ0WKz9t3PActLg/?defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.ts"%2C"%2Findex.html"%2C"%2Fpackage.json"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.ts"}

  GIF:

  Regards,