Google Script Editor - Clearing Cells (Except those with Formulas)

Question

I'm new to this. I need help in clearing cells of a google spreadsheet.

However, the cells that I want to clear are those containing numbers only. In other words, cells with strings and formulas will remain uncleared after running the macro. So that I do not need to input those formulas into the cells again after clearing the entire sheet.

Can anyone please help me with this?

I tried this: but it clears all the content which is not what I want.

function myFunction() {
  var sheet = SpreadsheetApp.getActive();
  sheet.getRange('A1:S1500').clearContent();
}

And the reason why I did not want to specify those cells with no formula to clear instead is that it is not an ideal way of simplifying the code since I will need to go and list out all the cells without formulas in it to clear. Also, should there be any changes in the cells with formulas (e.g. A10 (with formula) now becomes A11 instead), I need to go into the script editor and edit the cell references again changing it to clear cell A10 instead of cell A11.

Hence I am thinking if there is a way to work around it instead?

Thank you.

Get one array with getValues() and another array with getFormulas() and look for cells where formula array is null and value array is a number — Cooper

Cooper Cooper · Accepted Answer · 2020-05-03T18:11:45

Try this:

function myFunction() {
  var sheet = SpreadsheetApp.getActive();
  sheet.getRange('A1:S1500').clearContent();
  var fA=sheet.getRange('A1:S1500').getFormulas();
  var vA=sheet.getRange('A1:S1500').getValues();
  vA.forEach(function(r,i){
    r.forEach(function(c,j){
      //if no formula and a number
      if(!fA[i][j] && !isNaN(c)) {
        sheet.getRange(i+1,j+1).setValue('');
      }
    });
  }); 
}

isNan()

Google Script Editor - Clearing Cells (Except those with Formulas)

1 Answers