Why I'm still getting result as FALSE even for matching values between 2 gsheets?

33 views
Skip to first unread message

Dell

unread,
Jul 21, 2020, 5:32:54 AM7/21/20
to Google Apps Script Community
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;
         
}
 
}
}

Alan Wells

unread,
Jul 21, 2020, 9:04:50 AM7/21/20
to Google Apps Script Community
Do some debugging.
Log some values out.

Create a function for logging
file: GS_Logs

function ll(a,b) {
 
Logger.log(a + ": " + b)
 
}


Partial code with modifications for logging:

  for(var i =1; i<data.length;i++){

    ll
('data[i][0]',data[i][0])
    ll
('data[i][2]',data[i][2])
   
    ll
('data[i][0] == testID',data[i][0] == testID)
    ll
('data[i][2] == uemail',data[i][2] == uemail)


Study the logs to see what is actually happening.

Dell

unread,
Jul 21, 2020, 9:47:24 AM7/21/20
to Google Apps Script Community
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:-


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. 

Below I'm sharing my reviewed code incase it'd be helpful to others who has similar scenario like mine:-

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);
     
}
}

It's a big relief for me as I have been stucked to finish my project because of this validation.

Anyway, thanks again for making the effort on my question.

DZ

Kim Nilsson

unread,
Jul 22, 2020, 4:03:35 AM7/22/20
to Google Apps Script Community
It doesn't seem like you are using dtlen. You only set it, but I don't see it used anywhere.

סער וינברג

unread,
Jul 22, 2020, 4:07:03 AM7/22/20
to google-apps-sc...@googlegroups.com
Hey there, Thank you for the reply,
What is dtlen?

On Wed, 22 Jul 2020 at 11:03, 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:
It doesn't seem like you are using dtlen. You only set it, but I don't see it used anywhere.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2533da4b-9785-4248-8f55-f7ad631c92ecn%40googlegroups.com.


--


Sahar Weinberg

E-Success co-founder & Owner
Logo
Mobile: +972543110840
Gmail: Saha...@gmail.com

linkedin icon facebook icon twitter icon youtube icon 

--------------------------------------------------
..

.
.

Kim Nilsson

unread,
Jul 22, 2020, 4:16:57 AM7/22/20
to Google Apps Script Community
Hi, Sahar!

DZ is just adding it as a variable, but doesn't then use the variable anywhere.
Reply all
Reply to author
Forward
0 new messages