Google app script - loop and add through rows

Question

I essentially want to activate a script that says

b[i]=b[i]+f[i]

For each value of [i] in a row. Is there a way for me to do this in a loop? Below is the super inefficient code I currently have (I have a small data set of 5, so I can get away with it for now) and I want it to be in a loop format instead so that it can support scalability and etc.

function Update() {

      //update A
      var Ax = SpreadsheetApp.getActiveSheet().getRange('B2').getValue();
      var Ay = SpreadsheetApp.getActiveSheet().getRange('D2').getValue();
      SpreadsheetApp.getActiveSheet().getRange('B2').setValue(Ax+Ay);

      //update B
      var Bx = SpreadsheetApp.getActiveSheet().getRange('B3').getValue();
      var By = SpreadsheetApp.getActiveSheet().getRange('D3').getValue();
      SpreadsheetApp.getActiveSheet().getRange('B3').setValue(Bx+By);
      ....
    }

EDIT: I also have functions on many cells within the range that I do not want to remove.

score 0 · Answer 1 · answered Sep 03 '20 at 18:55

0

Use forEach:

const sh = SpreadsheetApp.getActiveSheet(),
  rg = sh.getRange(`B2:D${sh.getLastRow()}`),
  values = rg.getValues();
values.forEach(row=>row[0]+=row[2]);
rg.setValues(values);

To read:

What does the range method getValues() return and setValues() accept?

answered Sep 03 '20 at 18:55

TheMaster

45,448
6
62
85

Thanks for the solution! The code itself works fine but unfortunately it overwrites the function I have in place for the D row to get its values (which I probably should have mentioned), as well as the function in the C row in between them. Is there a solution that doesn't remove these functions? – Touya Kami Sep 10 '20 at 02:10

score 0 · Answer 2 · answered Sep 24 '20 at 11:19

I modified @TheMaster answer so it would set values only on the B column.

function Update() {
  const sh = SpreadsheetApp.getActiveSheet(),
    changeColumn = sh.getRange(`B2:B${sh.getLastRow()}`),
    changeColumnValues = changeColumn.getValues(),
    valuesToAdd = sh.getRange(`D2:D${sh.getLastRow()}`).getValues(),
    valuesToSet = changeColumnValues.map((value, i)=> [value[0] + valuesToAdd[i][0]]);

  changeColumn.setValues(valuesToSet);
}

Google app script - loop and add through rows

2 Answers2

To read: