Google sheets Script to add a precentage to entire column

Question

Hey I can't seem to be able to write this script sucssefully.

I want that when I press a button a certain percentage value will be added to an entire column. https://docs.google.com/spreadsheets/d/1KLvgW0HBZg0spWp8osskl1LgR3sslxScI4E9w1OBKfg/edit?usp=sharing Link to the sheet itself.

what i want is to add the value in K2 to the entire B column by clicking the button on L2

Here is the code I tried to do:

function  increase(){
    ModifyCell("B2:B","L2","I1")
}

function ModifyCell(CellName, increase,precentage) {
   var s = SpreadsheetApp.getActiveSheet();
   var r = s.getActiveCell();
      if( r.getColumn() == 2 && r.getValue()>0)
      var nextCell = r.offset(0, 1);
      nextCell.setValue(r.getValue()+r.getValue()*increase-r.getValue() *increase*precentage);
}

Could you please give more details on your purpose? What button, what percentage and what column are you talking about? I don't think you've made that clear. — Iamblichus

Iamblichus Iamblichus · Accepted Answer · 2020-06-12T08:23:03

If I understand you correctly, you want to do the following:

When the button in L2 is pressed, increase each Amount in column B with the percentage specified on K2. That is to say. If the original amount in B2 is 3,000 and K2 is 10%, the new amount would be 3,300.

If that's the case, you can do this:

function increase() {
  var s = SpreadsheetApp.getActiveSheet();
  var destRange = s.getRange(2, 2, s.getLastRow() - 1); // Column B
  var percentage = s.getRange("K2").getValue();
  var destValues = destRange.getValues();
  destValues = destValues.map(row => [row[0] * (1 + percentage)]);
  destRange.setValues(destValues);
}

Note:

Make sure the button in L2 is attached to increase.
In the code you provided, you were providing the A1 notations of the different ranges (B2:B, L2), but these are not the ranges themselves. You should have used getRange(a1Notation).

Reference:

getRange(row, column, numRows)

Google sheets Script to add a precentage to entire column

1 Answers

Note:

Reference: