Loop not working for column match

Question

I have a loop that work to match row value from bottom and it goes like this:-

var lastRow = s3.getLastRow();
var dataRange = s3.getRange(1, 1,lastRow).getValues();
for(var k=0;k<dataRange.length;k++)
{doing something}

However, I am getting no result when I am trying to do the same thing with column match, here is my loop for column match that does not do anything.

var lastColumn = s3.getLastColumn();
var match2 = s3.getRange(1, 1,lastColumn).getValues();
for (var b = 0; b < match2.length; b++) 
{if (range[j][0] == match2[0][b])
{ do something } 
}

Please suggest what I am missing.

This is taken right out of the documentation:

getRange(row, column, numRows)  Range   Returns the range with the top left cell at the given coordinates, and with the given number of rows.

Cooper · Accepted Answer · 2017-08-22T15:29:25.260

1

match2.length is the number of rows in the array or the range.

This array [[x,x,x],[y,y,y],[z,z,z]...] has three x's in the first row, 3 y's in the second row and so on. So in s3.getRange(1, 1,lastColumn).getValues(); lastColumn is the number of rows in that range. Essentially it's easier to read each row and then get the column one at a time. Or you could transpose your data like a matrix and then read the columns that are now rows.

A loop looking for "Big Macs':

function myFunction() 
{
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getActiveSheet();
    var rg=ss.getRange("A1:Z1");
    var vA=rg.getValues();
    for(var i=0;i<vA.length;i++)
    {
      for(j=0;j<vA[0].length;j++)
      {
        if(vA[i][j]=="Big Mac")
        {
          SpreadsheetApp.getUi().alert('Do not eat this burger as it has massive amounts of fat in it.');
          break;
        }
      }
    }
}

In these two dimensional arrays obtained by commands such as var data = range.getValues(); data.length = the number of rows and data[0].length = the number of columns. So total number of array elements data.length x data[0].length some of which may be null. Many programmers new to Google Apps Scripting have problems in this area. In fact I had a lot of trouble with it so I ended up doing some extra work to help bolster my understanding and you can read about it here.

These arrays look like the following: [[0,1,2,3,4,5...],[0,1,2,3,4,5...],[0,1,2,3,4,5...]...]. So vA is an array of arrays and so the term vA.length is equal to the number of elements in vA and simply put it's also equal to the number rows.

edited Aug 22 '17 at 15:29

answered Aug 20 '17 at 16:10

Cooper

59,616
6
23
54

Basically I am trying to match data through "A1:Z1" what should my for loop be like? – Mask Aug 20 '17 at 16:19
@Mask There's a simple loop. – Cooper Aug 20 '17 at 16:27
[here](https://docs.google.com/spreadsheets/d/1Ec0m2wEGeZq6qBsitRLMAsrtJrj_GaGSQdR1IleIZVs/edit?usp=sharing) is the link to the spreadsheet can you tell me what is wrong. the column loop is in line 27. – Mask Aug 20 '17 at 16:30
1

Just because you decided to put the number of columns into the range function where the number rows belongs does not change how the function works. – Cooper Aug 20 '17 at 16:32
If you want to search through the columns then you might consider transposing the data. Take a look at [this link](https://stackoverflow.com/a/16705104/7215091). Transpose the data and columns become rows. – Cooper Aug 20 '17 at 16:36
Length - gives you a total number of elements!!! To get number of rows use extension method .GetLength(0) To get number of columns - .GetLength(1) – Gleb B Aug 22 '17 at 09:54
@GlebB I believe in this case length also is the number of rows. I don't believe there is a GetLength method in either arrays or ranges. I've looked [here](https://developers.google.com/apps-script/reference/spreadsheet/range) and [here](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array) and there is length property in array but I can't find any GetLength() method in either ranges or arrays. Perhaps you could shed some light on the issue by providing a reference that we could read. Thank You – Cooper Aug 22 '17 at 13:48
Yes. I didn't mention that you use not smth like C# - there you have such Array methods. In your case it should work. Usually when you work with 2-dimensional array we use [ , ] not [ ] [ ] - it is for jagged arrays. – Gleb B Aug 22 '17 at 16:15
Well I don't see any c# tags here so I think it's better to keep the thread focused on the question at hand. – Cooper Aug 22 '17 at 18:36

Loop not working for column match

1 Answers1