How get the row number in certain column in google apps script

Question

In my spreadsheet I have 12 sheets. Every sheet is called by the name of a city. It also appears in the column A in every sheet with its corresponding name.

To localize the row number in vba, I use this code.

Q = 0

 For Each Sheet In ThisWorkbook.Worksheets 

    //Result is the last sheet
   If Sheet.Name <> "Result" Then

    With Sheet      
        number = .Range("A:A").Find(Sheet.Name).Row + 1
    totalnumber = .Range("A" & Rows.Count).End(xlUp).Row

Q = Q + (number - totalnumber + 1) * 31
          //I use this for get the date from a month
          For i = 1 To 12
            If arr(i) = .Range("a2") Then    
               YY = Year(Now)
               MM = Month(CDate("01/" & i & "/" & YY))
               Exit For
            End If
          Next
      End With
   End If
Next

I need this code, in format javascript of google apps script.

I tried this

var Hc = SpreadsheetApp.getActiveSpreadsheet();
var Hn= SpreadsheetApp.getActiveSheet().getName();

number = Hc.getRange('A:A'), Find(Hn), Row + 1;
totalnumber = Hc.getRange('A' & Rows.Count), Hc.End(xlUp), Hc.Row

Answer 1

1. First, you need a function:

   function myFunction() {
     // next code will be written here, line by line
   }
   

2. Then, you need to open your spreadsheet.

   var ss = SpreadsheetApp.openById("1lqLHXt15SKamuGTnIjQoAAiWuuxEJOUcY1ZdamxCk_0"); // you can actually use this id. Link below the post.
   

   From here on, ss will be our spreadsheet

3. Then you need to get all your sheets

   var allMySheets = ss.getSheets();
   

   allMySheets is now an array - each element being a sheet from our spreadsheet.

4. Now we need to go through each of the sheet. There are various ways to iterate in Javascript, but we will use a forEach method in this example:

   allMySheets.forEach(function(sheet){
     //                         ^ note
     // next code will be written here, line by line
   })
   

   What this does is calls the inner function for each element in allMySheets and provides that function the element as an argument. From here on, sheet is our sheet.

5. You can do all sorts of stuff on a Sheet object. We need its name using getName:

   var sheetName = sheet.getName();
   

6. We also need the range that contains the data:

   var dataRange = sheet.getDataRange();
   

7. And the actual values:

   var dataValues = dataRange.getValues();
   

8. dataValues is now a bidimensional array and looks something like this:

   [
     ["Column 1", "Column 2", "Column 3"],
     ["Some val", "Some val2", 15],
     [3,8,0],
   ]
   

   And translates into the actual table that you see in that sheet:

   +----------+-----------+----------+
   | Column 1 | Column 2  | Column 3 |
   +----------+-----------+----------+
   | Some val | Some val2 |       15 |
   | 3        | 8         |        0 |
   +----------+-----------+----------+
   

   We need to loop over this. We'll use two for loops. First one will represents rows, and the other for columns:

   for (var i=0;i<dataValues.length;i++){
     var rowArray = dataValues[i];
     for (var j=0;j<rowArray.length;j++){
       var columnValue = rowArray[j];
     }
   }  
   

   If i is 0, means we're at 1^st row, if it's 1 2^nd row, etc. because arrays in most programming languages, JavaScript included, are 0 based indexed.

   Same applies for columns. j being 0 means we're at first column.

   Here's the same thing visualized

             j = 0      j = 1      j = 2
         +----------+-----------+----------+
   i = 0 | Column 1 | Column 2  | Column 3 |
         +----------+-----------+----------+
   i = 1 | Some val | Some val2 |       15 |
   i = 2 | 3        | 8         |        0 |
         +----------+-----------+----------+
   

9. Enough theory. Back to our needs, we care about first column containing the sheet name. That is, if j is 0, we need it's data. That means:

   if (j===0) {
     // we are at first column
     // let's check if the value is our sheetName:
   
     if (columnValue === sheetName){
       // if we got here, means we have the needed row. 
       var rowNumber = i + 1; // remember that in array, i being 0 meneans row one
       Logger.log(rowNumber);
   
       // now you can do whatever you want to this number.
     }
   }
   

   Or, another approach (I recommend this one, because it's easier to use other column values from the same row:

   if (rowArray[0] === sheetName){
     // We're at the right row 
     var rowNumber = i+1;
   
     // Do something with columnValue
   
   }
   

And that's it. Here's the whole code:

function myFunction() {
  var ss = SpreadsheetApp.openById("1lqLHXt15SKamuGTnIjQoAAiWuuxEJOUcY1ZdamxCk_0");
  var allMySheets = ss.getSheets();
  allMySheets[0].getRange(1, 1, allMySheets[0].getLastRow(), allMySheets[0].getLastColumn()).getValues()
  allMySheets.forEach(function(sheet){
    var sheetName = sheet.getName();
    var dataRange = sheet.getDataRange();
    var dataValues = dataRange.getValues();
    for (var i=0;i<dataValues.length;i++){
      var rowArray = dataValues[i];
      for (var j=0;j<rowArray.length;j++){
        var columnValue = rowArray[j];

        if (rowArray[0] === sheetName){
          // We're at the right row 
          var rowNumber = i+1;
          Logger.log("Row number: %s, sheet name: %s, column value: %s", rowNumber, sheetName, columnValue);
          // Do something with columnValue

        }

      }
    }
  })
}

Here's the spreadsheet with the code embedded.