How to get the cell position of a button or rather how to get a variable for the execution of a button?

Question

With the function I woud like to do the following:

Flow

1.) Get the current User ID

2.) Finding the user's lines where the states are on the alert icon

3.) Change the date in column K to the current date using the format "dd.MM.yyyy"

4.) Generate a time stamp in column L using the format "HH:mm:ss"

5.) Generate a time stamp in the column P using the format "dd.MM.yyyy' 'HH:mm"

Therefore I used the code below.

Unfortunately the code doesn't work. I did not got any error.

What am I doing wrong?

    function CheckAll(){


  var userID=getUserId();
    if(userID) {    
      var timezone = "GMT+2";
      var TimestampFormat1 = "dd.MM.yyyy' 'HH:mm";
      var TimestampFormat2 = "HH:mm:ss";
      var ss=SpreadsheetApp.getActiveSpreadsheet();
      var tickerCheck=ss.getSheetByName('WATCHLIST-Pflege');
      var State=tickerCheck.getRange("O1:O2").getCell(2,1).getValues();
      var startRow=16;
      var lastRow=tickerCheck.getLastRow()-startRow+1;
      var rngStateCheck=tickerCheck.getRange(startRow,15,lastRow,1);
      var StateCheck=rngStateCheck.getValues();
      var userIds=tickerCheck.getRange(startRow,1,lastRow,1).getValues();
      var rngLastCheck=tickerCheck.getRange(startRow,11,lastRow,1);
      var rngTimeStamp1=tickerCheck.getRange(startRow,12,lastRow,1);
      var rngTimeStamp2=tickerCheck.getRange(startRow,16,lastRow,1);
      var date=new Date();


      for(var i=0;i<StateCheck.length;i++){

         if(StateCheck[i][14]==State && userIds[i][0]==userID){
             rngLastCheck.getCell(i+1, 1).setValue(date);
             rngTimeStamp1.getCell(i+1, 1).setValue(Utilities.formatDate(date, timezone, TimestampFormat2));
             rngTimeStamp2.getCell(i+1, 1).setValue(Utilities.formatDate(date, timezone, TimestampFormat1));
          }

      } 
}else{
    SpreadsheetApp.getUi().alert('Falsche User ID');
  }
}

function getUserId() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Startseite');
  var rg=sh.getRange(35,3,sh.getLastRow()-3,3);
  var vA=rg.getValues();
  var userEmail=Session.getEffectiveUser().getEmail();  
  for(var i=0;i<vA.length;i++) {
    if(vA[i][0]==userEmail) {
      return vA[i][1];
      break;
    }
  }
  return null;
}

Answer 1

You could use this approach:

function CheckAll1(){
  var userID=getUserId();
  if(userID) {        
    var ss=SpreadsheetApp.getActive();
    var tsh=ss.getSheetByName('Tickerprüfung');
    var startRow=12;
    var lastRow=tsh.getLastRow()-startRow+1;
    var range=tsh.getRange(startRow,12,tsh.getLastRow()-startRow+1,1);
    var tvA=range.getValues();
    var userIds=tsh.getRange(startRow,1,tsh.getLastRow()-startRow+1,1).getValues();
    var lcrg=tsh.getRange(startRow,9,tsh.getLastRow()-startRow+1,1);
    var tsrg=tsh.getRange(startRow,10,tsh.getLastRow()-startRow+1,1);
    var today=new Date();
    var dateInMs=new Date(today.getFullYear(), today.getMonth(), today.getDate()).getTime();
    for(var i=0;i<tvA.length;i++){
      var nextDate=new Date(tvA[i][0]);
      if(Object.prototype.toString.call(nextDate) === '[object Date]' && userIds[i][0]==userID) {
        if(dateInMs>=nextDate.getTime()){
          lcrg.getCell(i+startRow, 1).setValue(today);
          tsrg.getCell(i+startRow, 1).setValue(new Date().toLocaleTimeString());
        }
      }
    } 
  }else{
    SpreadsheetApp.getUi().alert('Invalid UserId');
  }
}

function getUserId() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Startseite');
  var rg=sh.getRange(20,2,sh.getLastRow()-19,2);
  var vA=rg.getValues();
  var userEmail=Session.getEffectiveUser().getEmail();  
  for(var i=0;i<vA.length;i++) {
    if(vA[i][0]==userEmail) {
      return vA[i][1];
      break;
    }
  }
  return null;
}
  

And then in the Startseite sheet you could associate user emails with UserId's in this way.

Look closely at the code as I have corrected some errors pertaining to the calculation of rows that did not account for startRow=12 also third parameter in a range is the number of rows not the last row.

Answer 2

I solved the problem by adding the variable var StateToCheck=StateCheck[i][0] to the for loop.

Now the code looks like this:

function CheckAll(){


  var userID=getUserId();
    if(userID) {    
      var timezone = "GMT+2";
      var TimestampFormat1 = "dd.MM.yyyy' 'HH:mm";
      var TimestampFormat2 = "HH:mm:ss";
      var ss=SpreadsheetApp.getActiveSpreadsheet();
      var tickerCheck=ss.getSheetByName('WATCHLIST-Pflege');
      var State=tickerCheck.getRange("O1:O2").getCell(2,1).getValues();
      var startRow=16;
      var lastRow=tickerCheck.getLastRow()-startRow+1;
      var rngStateCheck=tickerCheck.getRange(startRow,15,lastRow,1);
      var StateCheck=rngStateCheck.getValues();
      var userIds=tickerCheck.getRange(startRow,1,lastRow,1).getValues();
      var rngLastCheck=tickerCheck.getRange(startRow,11,lastRow,1);
      var rngTimeStamp1=tickerCheck.getRange(startRow,12,lastRow,1);
      var rngTimeStamp2=tickerCheck.getRange(startRow,16,lastRow,1);
      var date=new Date();


      for(var i=0;i<StateCheck.length;i++){
         var StateToCheck=StateCheck[i][0]
         if((StateToCheck==State)&&(userIds[i][0]==userID)){

             rngLastCheck.getCell(i+1, 1).setValue(date);
             rngTimeStamp1.getCell(i+1, 1).setValue(Utilities.formatDate(date, timezone, TimestampFormat2));
             rngTimeStamp2.getCell(i+1, 1).setValue(Utilities.formatDate(date, timezone, TimestampFormat1));
          }

      } 
}else{
    SpreadsheetApp.getUi().alert('Falsche User ID');
  }
}

function getUserId() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Startseite');
  var rg=sh.getRange(35,3,sh.getLastRow()-3,3);
  var vA=rg.getValues();
  var userEmail=Session.getEffectiveUser().getEmail();  
  for(var i=0;i<vA.length;i++) {
    if(vA[i][0]==userEmail) {
      return vA[i][1];
      break;
    }
  }
  return null;
}