CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Silveress
Silveress

Reputation: 3

Condensing similar and repeating code

I am a fairly amateur coder but I have searched for the solution and didn't come up with anything.

I am working in Google sheets and what I am trying to do is add the values on the same row in two different columns together. I have tried arrays but couldn't get the first Array A to add to the first element in array B so I created the code bellow, It works OK but it seems far too wasteful (I have 21 rows to add together).

Would you be able to at least guide me to the right place to slim this down?

var bd1  = data.getRange("K3");
var bn1  = data.getRange("H3").getValue();
var bo1  = data.getRange("K3").getValue();
var bs1  = bn1+bo1;
bd1.setValue(bs1); 

var bd2  = data.getRange("K4");
var bn2  = data.getRange("H4").getValue();
var bo2  = data.getRange("K4").getValue();
var bs2  = bn2+bo2;
bd2.setValue(bs2);
...

Upvotes: 0

Views: 81

Answers (2)

AdamL
AdamL

Reputation: 24659

I know you have accepted a best answer, but when making spreadsheet API calls in Google Apps Script, it is best practice to do them in batches wherever possible.

You mentioned that you are doing this for 21 rows; I would therefore getValues() of all 21 rows first (which will return a Javascript 2D array), do the processing on that array with pure Javascript, and then setValues() in one go:

var values1 = data.getRange("H3:H23").getValues();
var range2 = data.getRange("K3:K23");
var values2 = range2.getValues();
for (var i = 0, length = values1.length; i < length; i++)
  values2[i][0] += values1[i][0];
range2.setValues(values2);

Upvotes: 1

Alfredo Delgado
Alfredo Delgado

Reputation: 689

Here's one way to go about it:

var add2Cells = function(labelA, labelB) {
    var cellA  = data.getRange(labelA);
    var cellB  = data.getRange(labelB);

    cellA.setValue(cellB.getValue() + cellA.getValue());
};

add2Cells("K3", "H3");
add2Cells("K4", "H4");

At this point you can make an array of cell pairs that you loop through and feed into your add2Cells function.

Upvotes: 0

Related Questions