CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Matt Coward
Matt Coward

Reputation: 7

Why is my Google Script adding 3 rows after every execution?

I have put together the below code to check if Column B <> Column A. And if TRUE, then paste Column A data into Column B.

This works fine except 3 rows are being added after every execution! And I just cannot work out why.

Could anyone shed some light on why this is happening? I have tried breaking this script down to understand the mechanics of each part but cannot draw a conclusion.

function RefreshSheets() {

 
  var ss = SpreadsheetApp.openById("1YWjysA9uuGez3grhvMR3_JaAuBp5AsigOmDbxlk7d6c");
  var sh = ss.getSheetByName("Front Sheet");

  var rA = sh.getRange(6, 1, sh.getLastRow() - 0, 1);
  var vA = rA.getValues();

  var rD = sh.getRange(6, 4, sh.getLastRow() - 0, 1);
  var vD = rD.getValues();

  var rB = sh.getRange(6, 2, sh.getLastRow() - 0, 1);
  var vB = rB.getValues();


  for (var i = 0; i < vB.length; i++) {
    if (vB[i][0] != vA) {
      vB[i][0] = vA;
    }
  }
  rB.setValues(vA)

}

Upvotes: 0

Views: 48

Answers (1)

Cooper
Cooper

Reputation: 64062

Try it this way:

The third parameter in a range is number of rows not the last row number. If you use the last row number but don't start with row 1 then you iterate right out of the bottom of your data.

vA and vB are 2 d arrays so they require two indices to specify a single cell.

function RefreshSheets() {
  var ss = SpreadsheetApp.openById("1YWjysA9uuGez3grhvMR3_JaAuBp5AsigOmDbxlk7d6c");
  var sh = ss.getSheetByName("Front Sheet");
  var rA = sh.getRange(6,1,sh.getLastRow()-5,1);
  var vA = rA.getValues();
  var rD = sh.getRange(6,4,sh.getLastRow()-5,1);
  var vD = rD.getValues();
  var rB = sh.getRange(6,2,sh.getLastRow()-5,1);
  var vB = rB.getValues();
  for (var i=0;i<vB.length;i++) {
    if (vB[i][0] != vA[i][0]) {
      vB[i][0] = vA[i][0];
    }
  }
  rB.setValues(vA)

}

Upvotes: 1

Related Questions