CODEWITHSUNDEEP
javascriptgoogle-apps-script
Mask
Mask

Reputation: 573

How to apply multi-loop in a google spreadsheet

I have Idea of simple loop for example if I have a cell value in "A1" = "c" and I have another set of values that I want to match in "B:B".

var lastRow = targetSheet.getLastRow();
var match = sheet.getRange("A1").getValue();

var dataRange = targetSheet.getRange(B1:B5).getValues(); //doubt here too
for(var i=0;i<dataRange.length;i++) {

    if(dataRange[i][0] == match) 
{ Do something }
                        }

    A     B
------------
1   c     x
2         c
3         b

However, I have very less idea on how to perform the same if I have multiple values to match.

    A     B
1   z     x
2   x     c
3   c     b
4   v
5   b

Any help is appreciated!

Upvotes: 0

Views: 65

Answers (1)

Vytautas
Vytautas

Reputation: 2286

If I understood this correctly you want to loop through column A and if there is a set value there, you want to look for the same value in column B. This is a simple matter of a loop inside of a loop. Let's say you have the 2 columns in 1 array

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var values = sheet.getRange(1, 1, sheet.getLastRow(), 2).getValues() //this will get columns A and B until the last row with data into a 2D array

  var i, ii;

  for (i = 0; i < values.length; i++) {
    if (values[i][0] == '') break;  // if first column is empty we stop looking for the next one, you can get rid of this

    for (ii = 0; ii < values.length; ii++) {
      if (values[ii][1] == '') break; //if last entry reached we stop, same as before

      if (values[i][0] == values[ii][1]) {
        /* This is the part where you do what you want
        to do when you find a match, for example change background to red */
        sheet.getRange(ii + 1, 2).setBackground('red')
      }
    }
  }
}

EDIT: as per requester I have amended the function. This is essentially a difference in your if statement and where it is as we are now only concerned with checking if something exists in B column, we no longer need an if in the first loop. The one I have put in there is for situations where you intend to let's say have 3 entries in column A, however 7 entries in column B. Same thing for the reverse scenario. Otherwise you can get rid of the if statements immediately after the for statement. That is done because getLastRow() will take the last row that has data on the sheet. Keep in mind that if you have only 3 values in column A and 7 in column B, but you have a value in C10 then getLastRow() = 10.

Upvotes: 3

Related Questions