Clear just data from Web Excel worksheet with formulas using Office Script

[dgold]

I am trying to clear all data from a worksheet in a web version of Excel. The VBA code I use on my desktop version is
Sub sbClearEntireSheetOnlyData()
Sheets("Sheet4").Cells.ClearContents
End Sub
Can someone "translate" this to Office Scripts? Thanks in advance.
Dave

 

[severynm]

I think you want to look to Range.Clear(applyTo). Using the example from that link, it was pretty easy to translate it to your requirement:

function main(workbook: ExcelScript.Workbook) {
    let range = workbook.getWorksheet("Sheet4").getRange();
    range.clear(ExcelScript.ClearApplyTo.contents);
}

 

[dgold]

For some reason, this script works to clear everything including the formulas. Is that expected?

 

[severynm]

Yes. That is what ClearContents does. See VBA documentation. Formulas are part of a cell's contents.

 

[dgold]

OK, thanks. I'm assuming that there isn't an easy way just to clear the data and retain the formulas. I guess one option would be to have a blank template and automate duplication and deleting.

 

[severynm]

I'm assuming that there isn't an easy way just to clear the data and retain the formulas

That depends if you know the data you want to clear will be in predictable locations each time (i.e. if there is some sort of template or table users are filling in). If there will be consistent locations with values, you can look at clearing only those ranges instead of the entire sheet.

If you don't expect consistency of where you are going to find the values, you could still loop over every cell in the workbook to check if it has a formula. However, for obvious reasons that is likely not going to be a performant solution.