Google sheets, trying to add a cells value to multiple cells - Stack Overflow

admin管理员组

文章数量:1388929

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);

}

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);

}

• google-sheets
• google-apps-script

Share Improve this question Follow edited Mar 15 at 1:49 Hilory 2,13777 gold badges1414 silver badges3030 bronze badges asked Mar 15 at 0:11 czerkaczerka 311 silver badge11 bronze badge 3

• Related questions: Add value to each cell in range (Google apps script) and getValues is not a function : How to add multiple ranges in google app script? How to implement it to the code? – Tedinoz Commented Mar 15 at 1:44
• but it would only add to cell U3, this is because there are six contiguous rows assigning values to targetCell. Only the value assigned in the last row ("U3") is processed. – Tedinoz Commented Mar 15 at 2:05
• var targetCell = sheet.getRange("B11:B14","F11:F12","J11:J14","N11:N14","R11:R13","U3"); results in this error => "The parameters (String,String,String,String,String,String) don't match the method signature for SpreadsheetApp.Sheet.getRange." This is because even though there are five "sheet" methods for getRange, none allow a series of discrete ranges described as strings. The most common solution is to put the ranges in an array, then loop through the array and process one range as a time. – Tedinoz Commented Mar 15 at 2:11

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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

• const ranges = ["B11:B14","F11:F12","J11:J14","N11:N14","R11:R13","U3"]: build an array to hold the ranges
  • then loop through the array and get each range and value

Add value to each cell in a range

• vs[i][j]=vs[i][j]+sourceValue

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

AFTER

本文标签： Google sheetstrying to add a cells value to multiple cellsStack Overflow