0

I'm trying for days already to do this but still to no avail. I'm trying to validate the testID and the email address entered in gsheet "testslip" with the ones entered in gsheet "students". If both matches, then the URL from gsheet "testslip" shall be updated in gsheet "students" at that matched row. Else, the error will be emailed to the email address in gsheet "testslip" to notify them to do necessary action. When I run the code below, even both entries in both gsheets are matched correctly, the output is still the error being emailed. If I delete the checking on "non-matched" values, then the output will be as expected, which is the URL will be updated. But doing that means my script doesn't handle negative scenarios and that would defeat the purpose then. Please help me to correct my code below. Thank you in advance:-

    function testslipupdate(){
      var psheet=SpreadsheetApp.openById("1O2-xzPCjQO0cj_6LjehFnKmmTL519gXEpRYwRoEwIFA").getSheetByName("testslip");
      var msheet=SpreadsheetApp.openById("1yLXRoV4MH6p_5F6rX3MnGnruDQVr4dqK-6GhRjRv3ns").getSheetByName("students");
     
      var lrow = psheet.getLastRow();
      var testID = psheet.getRange(lrow,2).getValue();
      var uemail = psheet.getRange(lrow,3).getValue();
      var slip = psheet.getRange(lrow,4).getValue();
      
      
      var data = msheet.getDataRange().getValues();
      var dtlen = data.length;
    
      
      for(var i =1; i<data.length;i++){
         if(data[i][0] == testID && data[i][2]==uemail){
         msheet.getRange(i+1,20).setValue(slip);  
         break;
         }
         //else if(data[i][0]==testID && data[i][2]!==uemail){ 
         else if(data[i][2]!==uemail){
              MailApp.sendEmail(""+uemail+"","Email address is not matched with Test ID","","Please change to valid email address");     
              break;     
              }   
         //else if(data[i][0]!==testID){
         else {
              MailApp.sendEmail(""+uemail+"","Record not found","","Please see Principal for a new registration.");
              break;
              }
      }
    }
dell
  • 171
  • 13
  • It is hard to troubleshoot without seeing your data - you provided the spreadsheet ids in yur code, but not access to teh spreadsheets. Please provide access to sample spreadsheets for troubleshooting. – ziganotschka Jul 21 '20 at 07:54
  • Is you problem related to the line `else if(data[i][2]!==uemail){` or `else if(data[i][0]==testID && data[i][2]!==uemail){ `? When you say `even both entries in both gsheets are matched correctly, the output is still the error being emailed` - does it mean that you do not enter the case `if(data[i][0] == testID && data[i][2]==uemail)`? – ziganotschka Jul 21 '20 at 08:45
  • Thanks for your input. After searching and trying for solutions, I finally managed to manipulate a solution provided earlier by MWaheed (Method 2: using Array.prototype.findIndex())for a question raised in StackOverFlow which we can refer via below link:- https://stackoverflow.com/questions/32565859/find-cell-matching-value-and-return-rownumber I kinda appreciate a lot on this Method 2 not only it has given me the result correctly with the if-then-else statement, but the searching method is way faster than other methods that commonly suggested. – dell Jul 21 '20 at 13:51
  • I wish to share my reviewed code but I don't know how can I do that in here as this is the 2nd time I posted a question since 2014. – dell Jul 21 '20 at 13:53
  • I am glad you found a solution! To share your uodated code, you can modify your question by clicking on `edit`. – ziganotschka Jul 21 '20 at 14:02
  • Thanks, ziganotschka . I've done it. :) – dell Jul 21 '20 at 14:23
  • Excuse me, I did not quite understand that the code you want to update is already the final solution and you do not need further assistance. In this case you should better post the new code as an answer rather than an edit. – ziganotschka Jul 21 '20 at 14:39
  • 1
    ok @ziganotschka Sorry, I'm not familiar with this platform yet. So, I just made the changes as per advised. thanks. – dell Jul 21 '20 at 15:18

1 Answers1

1

Below is my reviewed code after I found the solution:-

function testslipupdate(){
var psheet=SpreadsheetApp.openById("1O2-xzPCjQO0cj_6LjehFnKmmTL519gXEpRYwRoEwIFA").getSheetByName("testslip");
var msheet=SpreadsheetApp.openById("1yLXRoV4MH6p_5F6rX3MnGnruDQVr4dqK-6GhRjRv3ns").getSheetByName("students");
var lrow = psheet.getLastRow();
var testID = psheet.getRange(lrow,2).getValue();
var uemail = psheet.getRange(lrow,3).getValue();
var slip = psheet.getRange(lrow,4).getValue();
var data = msheet.getDataRange().getValues();
var dtlen = data.length;

var qrow = data.findIndex(qID=> {return qID[0] == quoteID});  
var mrow = data.findIndex(mailID=> {return mailID[2] == uemail}); 

var mqrow=qrow+1;
var mmrow=mrow+1;

if(mqrow==0){
MailApp.sendEmail(""+uemail+","Record not found","","Please see Principal for a new registration.");
}
else if(mqrow!=0&&mmrow==0){
     MailApp.sendEmail(""+uemail+","Email address is not matched with Test ID","","Please change to valid email address");
     }else{
          msheet.getRange(mqrow,20).setValue(slip);
      }
}
dell
  • 171
  • 13