0

I'm not really good at this kind of things and googling didn't return any kind of results. Does anyone know how a to write a script that clears all the cells in a specific column if they are not numbers? And if they are it just clears the formatting.

I would also like to apply the clear formatting part of the script for multiple cells that contain text. For these ones there is no need to check what the cells contain. I just want it to reset to plain text.(The default being 10, Arial)

So far this is all that I have

function onEdit()
{
  var sheet = SpreadsheetApp.getActive().getSheetByName('Market');

  var isNumber = sheet.getRange('E2:E1000').getValue()
  if (isNumber

};

I do not know how the rest of the phrasing should look like. And I am mainly interested in the first part.

Thanks for the help!

David
  • 57
  • 2
  • 8
  • Relevant? [Clear Formatting](https://stackoverflow.com/questions/37010333/how-to-clear-format-in-google-sheets-via-apps-script) and [Is Number](https://stackoverflow.com/questions/35252684/if-var-isnumber-for-script) – Confuzing Nov 09 '17 at 18:56

1 Answers1

1

This will remove the text. And if you want you can set the remaining numbers to text by removing the commented out script.

function onEdit(){
var sheet = SpreadsheetApp.getActive().getSheetByName('Market');
var lr=sheet.getLastRow()
var vals = sheet.getRange(2,5,lr,1).getValues()
 var newVals=[]// New array of numbers only and blank where text.
 for(i=0;i<vals.length;i++){
    if(typeof vals[i][0]=="number"){//If a number keep the number.
       newVals.push([vals[i][0]])}
     else{ //If not a number create a blank value.
        vals[i][0]=""
    newVals.push([vals[i][0]])}
     }
 var set= sheet.getRange(2,5,newVals.length,1).setValues(newVals)//Reset column E.
 // sheet.getRange(2,5,newVals.length,1).setNumberFormat('@STRING@')//If ypu want the numbers formated as a string remove //
  Logger.log(newVals)
};
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
  • This works perfectly for what I need it to do. Thank you so very much, you're a real life saver!!! – David Nov 10 '17 at 09:22