google-sheetsgoogle-apps-script

Google sheets, trying to add a cells value to multiple cells

I am trying to put a number into a cell, then click a button that adds that number to multiple other cells. I don't know anything about how to do this and am just muddling thru modifying other scripts I've found.

I tried this script but it would only add to cell U3.

function addValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var sourceCell = sheet.getRange("K3");
  var targetCell = sheet.getRange("B11:B14");
  var targetCell = sheet.getRange("F11:F12");
  var targetCell = sheet.getRange("J11:J14");
  var targetCell = sheet.getRange("N11:N14");
  var targetCell = sheet.getRange("R11:R13");
  var targetCell = sheet.getRange("U3");
  var sourceValue = sourceCell.getValue();
  var targetValue = targetCell.getValue();
  var newValue = Number(targetValue) + Number(sourceValue);
  targetCell.setValue(newValue);
}

I tried combining all the target cells together and I am getting this error.

Error: The parameters (String,String,String,String,String,String)
don't match the method signature for SpreadsheetApp.Sheet.getRange.

function addValue() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var sourceCell = sheet.getRange("K3");
  var targetCell = sheet.getRange("B11:B14","F11:F12","J11:J14","N11:N14","R11:R13","U3");
  var sourceValue = sourceCell.getValue();
  var targetValue = targetCell.getValue();
  var newValue = Number(targetValue) + Number(sourceValue);
  targetCell.setValue(newValue);

}

Solution

  • You want to enter a value in a given cell, click a button and have that value added to the values in given ranges.

    Try this script for adding values to each cell in multiple ranges. The OP should add their own code to "click a button" to run the code.

    function iterateThroughMultipleRanges() {
  const ss=SpreadsheetApp.getActiveSpreadsheet()
  const sheet=ss.getSheetByName('Sheet1');
  var sourceCell = sheet.getRange("K3");
  var sourceValue = sourceCell.getValue();
  const ranges = ["B11:B14","F11:F12","J11:J14","N11:N14","R11:R13","U3"]
  for (var s=0;s<ranges.length;s++){
  const rg=sheet.getRange(ranges[s]);  
  const vs=rg.getValues();
  vs.forEach(function(r,i){
    r.forEach(function(c,j){
        // Logger.log("DEBUG: i:"+i+", j:"+j+", value = :"+vs[i][j])
      vs[i][j]=vs[i][j]+sourceValue
    });
  })
  rg.setValues(vs);
  }
}

    Processing Logic

    Ranges

    Add value to each cell in a range

    Credit @Cooper for the elegant code to Add value to each cell in range (Google apps script)

    Sample Data

    B C D E F G H I J K L M N O P Q R S T U
    Source=> 3 1000
    10 50 70 5 25
    20 60 80 10 30
    30 90 15 35
    40 100 20 40

    BEFORE

    Before

    AFTER

    After