0
votes

I'm continually adding new rows to Google Sheets with numerical values in Column C. I need a script (not a formula!) to do 2 things with those numbers.

  1. Change the number format to remove commas from any numbers
  2. Add 200 to each number

I've got the script for part 1...

function setFormat(){SpreadsheetApp.getActiveSheet().getRange("C2:C").setNumberFormat('##########0');}

But I need help with part 2.

Both scripts need to occur immediately upon creation of the number in Column C.

I can't use formulas for these changes. I need to use scripts.

Thank you!

1

1 Answers

0
votes

You can write whole code within onEdit() function

like this:

function onEdit(e){  
  var range = e.range;
  if(range.getColumn() == 3){  //Column C is 3rd column
    SpreadsheetApp.getActiveSheet().getRange("C2:C").setNumberFormat('##########0');
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var  r = e.source.getActiveRange();
    var cell = r.getA1Notation();
    var tempval = sheet.getRange(cell).getValue();
    var numval = parseFloat(tempval)+200;
    sheet.getRange(cell).setValue(numval);
  }
}

Hope this will help you.

Thanks.