Javascript "for" loop of cells data comparison in Google Sheets

Question

I just started with js and I'm trying to make a script in Google Sheets:

https://docs.google.com/spreadsheets/d/1mNBThO2gBpvqQxrLE_hDxLuV--cTc42K8q37ZQG9TCc/edit?usp=sharing

function makeVariants() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("VARIANTSOK");
var righe = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
var lastCell = sheet.getRange(righe, lastColumn);
Logger.log(lastCell.getValue());
for (i=0; i<righe; i++) {
if (sheet.getRange(righe[i], 2, 1, 1) = sheet.getRange(righe[i-1], 2, 1, 1)) {righe[i].clear();}
}
}

how to loop so that the second cell in B column "B3" gets compared with the above one "B2" and get deleted if it's identical?

I have tried to get the last row, increment the last row + 1 and compare with the above one and gets deleted eventually. I'm having difficulties putting this in a loop.

In the sheet attached B11 and B10, B8, B6, and B4 and B3 should get deleted.

Unknown Unknown · Accepted Answer · 2016-04-17T18:46:44

There are too many things wrong here to analyze in detail. I add brief comments below, but here is a correct way to do what you are trying to do.

function makeVariants() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("VARIANTSOK");
  var colValues = sheet.getRange(1, 2, sheet.getLastRow(), 1).getValues();
  for (var i = colValues.length - 1; i >= 1; i--) {
    if (colValues[i-1][0] == colValues[i][0]) {
      sheet.getRange(i+1, 2).clear();
    }
  }
}

This script gets the values in column B, then loops over them bottom to up, so that deletion does not affect following comparisons. For example, if one loops over

a
a
a
b

top-to-bottom, the result will be

a

a
b

where the third a is not deleted since it does not match the previous cell that was cleared. When looping from bottom to top, the result is correct:

a


b

Some errors in your script

a = b is an assignment, not comparison. Read about a == b and a === b
sheet.getLastRow(); returns a number, so righe[i] makes no sense
getRange returns a pointer to a range, not the values in that range. So it's pointless to use this in comparison. Apply getValue to a single-cell range to get its value. Better yet, use getValues to get a bunch at the same time, as I did above.

Javascript "for" loop of cells data comparison in Google Sheets

1 Answers

Some errors in your script