Google Spreadsheets - Resetting cells while keeping mathematical functions?

Question

I am working in Google Spreadsheets and I am wanting to create a button that, when activated, will reset the data in a range of cells but keep any math functions assigned to those cells.

I have experimented with the basic clearContent(); scripting but I found that this deletes all data within a cell (text and functions).

Is there a way to keep the functions while just clearing the text?

Answer 1

There is a good reason to want to delete the data from a cell and keep the formula. I have created sheets that accomplish computational tasks my principal wants. The structure requiored I make the workbook page for a week at a time. If I copy the sheet for the next week, then delete the data, I am all set for the next week. As I input data the formulas are used to compute the new calculations. Excel has a CLEAR button which allows this to be done. Select the cells to delete the data and remove it while leaving the formulas.

Answer 2

There is no function to do what you ask - and for good reason.

A spreadsheet formula exists to calculate the value of a cell or range of cells.

If you clear the value currently in a cell, but keep the formula, then the value will be recalculated... and you'll have a value displayed again.

So it makes sense that if you want a cell's value cleared, you also want it's formula cleared, so Range.clearContent() does both.

If you really, really, really want to do this, though, it's a three-liner (without error checking).

function clearValues(range) {
  var formulas = range.getFormulas();
  range.clearContent();
  range.setFormulas(formulas);
}

Answer 3

I know this is an old post; for others that want to delete a range of data using Google Apps Script in your spreadsheet yet want to retain your formula, try the following.

 var sheet = SpreadsheetApp.getActive().getSheetByName('YourSheetName');
 sheet.getRange('G2:G1000').clear({commentsOnly: true});

Use case... My Menu clears two columns of data (A and G). Column "A" contains a user entered "X" in some cases. Column "G" has a formula that looks for the value in row/column "A" If the cell is changed to contain an "X" then the formula inserts the current date in row/column "G".