If else error

[Silvio Cesar Dos Santos Junior]

Hi guys, first of all sorry for my english i'am from Brazil.

I already have a script to schedule events in Google Calendar, someone answers a Google Forms, that response goes to a Spreadsheet and i'am using that Spreadsheet like a database, the schedule script insert the event ID he created in that sheet.

So now i'am creating a new script to cancel a event if is needed but i'm struggling to make the if/else statement. The script will look into another sheet who receives answers from another Google Forms get some cells and one of those cell is the client email, the script will check the client email in the schedule/database and if matchs he will get de event ID and use that ID to delete the event else he will send a email warning he couldn't found a match.

The error is he goes to the else condition even if has a match, for some reason the script in not checking all the lines in the for loop, if the first line he checks doesn't match with the client email he sends the email warning the error, he is not checking all the lines if the first line he checks is not whats he is looking for and i can't found what i'am doing wrong, pls someone save me.

My for loop is to check all the database cells starting from the database lastrow and stoping at the line 2.

Everything that has age_ is from the spreadsheet/database, both scripts are in the same Project.

My Delete Event script is (i have a lot of Looger.log because i was using then to find the error):

function canc_responses(canc_page, canc_lastRow) {

var canc_timeStamp = canc_page.getRange(canc_lastRow, 1).getValue();

var canc_type = canc_page.getRange(canc_lastRow, 2).getValue();

var canc_name = canc_page.getRange(canc_lastRow, 3).getValue();

var canc_email = canc_page.getRange(canc_lastRow, 4).getValue();

var canc_emailStamp = canc_page.getRange(canc_lastRow, 5).getValue();

Logger.log(canc_timeStamp);

Logger.log(canc_type);

Logger.log(canc_name);

Logger.log(canc_email);

Logger.log(canc_emailStamp);

canc_checkID(canc_page, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp);

}

function canc_checkID(canc_page, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp) {

for (var r = age_lastRow; r > 1; r--) {

var canc_dataRangeEmail = age_page.getRange(r, 4);

var canc_dataRangedate = age_page.getRange(r, 8);

var canc_emailCheck = canc_dataRangeEmail.getValue();

var canc_date = canc_dataRangedate.getValue();

Logger.log(r);

Logger.log(canc_emailCheck);

Logger.log(canc_date);

Logger.log(canc_emailCheck);

Logger.log(canc_email);

if (canc_emailCheck == canc_email) {

var canc_eventId = age_page.getRange(r, 11).getValue();

Logger.log(canc_eventId);

Logger.log("Keep Going");

//canc_deleteEvent(canc_page, canc_eventId, canc_date, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp);

break;

} else {

Logger.log("Error");

//canc_erros(canc_page, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp);

break;

}

}

}

[cbmserv...@gmail.com]

Hi Silvio,

 

Do you have other code somewhere else that you are not showing us? There are some variables that you are using in your functions that are not initialized to anything. Are these global variables you defined elsewhere?

--
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/f52bdd8a-5f83-4237-9eef-555044968985n%40googlegroups.com.

[Šáhàr]

Send me the web as link 

‫أُرسلت من الـ iPhone‬

‫في 24‏/04‏/2021 الساعة 3:15 ص، كتب/كتبت cbmserv...@gmail.com:‬



To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/008e01d7389e%24f6567b50%24e30371f0%24%40gmail.com.

[Silvio Cesar Dos Santos Junior]

Sorry for the late

Hi George, that project has a main file who calls all the other files and it declares some global variables that other files may need to use.

Hi Sahar, i didn't understand your request.

[CBMServices Web]

If you are using global variables from another source, then this is basically a library to you. So when the global variable is used, you need to reference it as follows:

LibraryName.varName

Just using the variable name by itself would not work.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8a3c8d82-c0c9-411f-b9d4-2d07130d6ba4n%40googlegroups.com.

[Silvio Cesar Dos Santos Junior]

I don't think the Global Variables are the issue, with de Logger.Log i can see the correct value of those variables is reaching de If Else and when i remove de else condition the script works just fine but when i insert the else condition the error appears again and i want to have the else because i'am using it to create custom error messages to the user.

[Clark Lind]

You may be running into a problem I had one time. When checking email addresses, make sure they are formatted exactly the same:

canc_emailCheck  = canc_emailCheck.toLowerCase().trim();

canc_email =  canc_email.toLowerCase().trim();

then
if (canc_emailCheck == canc_email) {

or simply:

if ( canc_emailCheck.toLowerCase().trim()   ==  canc_email.toLowerCase().trim()  ) {

[Clark Lind]

Also consider using a switch instead of an If. That way you can keep the comparison from kicking you out of the For loop. Otherwise, I believe you use "continue" instead of "break".

switch(canc_emailCheck) {

  case canc_emailCheck == canc_email:

    var canc_eventId = age_page.getRange(r, 11).getValue();

    Logger.log(canc_eventId);

    Logger.log("Keep Going");

    //canc_deleteEvent(canc_page, canc_eventId, canc_date, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp);

    break;

  default:

    Logger.log("Error");

    //canc_erros(canc_page, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp);

}

[Silvio Cesar Dos Santos Junior]

Thanks for the help Clark.

Now the script is no more stopping at the first wrong match, the for loop checks every line but he still can't find the right match. You have any idea why this keep happening?

My canc_checkID function is now that:

function canc_checkID(canc_page, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp) {

for (var r = age_lastRow; r > 1; r--) {

var canc_dataRangeEmail = age_page.getRange(r, 4);

var canc_dataRangedate = age_page.getRange(r, 8);

var canc_emailCheck = canc_dataRangeEmail.getValue();

var canc_date = canc_dataRangedate.getValue();

Logger.log(r);

Logger.log(canc_emailCheck);

Logger.log(canc_email);

var canc_emailCheck = canc_emailCheck.toLowerCase().trim();

var canc_email = canc_email.toLowerCase().trim();

switch (canc_emailCheck) {

case canc_emailCheck == canc_email:

var canc_eventId = age_page.getRange(r, 11).getValue();

Logger.log(canc_eventId);

Logger.log("Keep Going");

//canc_deleteEvent(canc_page, canc_eventId, canc_date, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp);

break;

default:

Logger.log("Error");

//canc_erros(canc_page, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp);

break;

}

}

}

In the execution Log i can see with the logger.log the canc_emailCheck and canc_email matches but the script doesn't see it.

13:37:52

Informação

21.0

13:37:52

Informação

silvio...@mercadolivre.com

13:37:52

Informação

silvio...@mercadolivre.com

13:37:52

Informação

Error

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/p42UPgUur1k/unsubscribe.
To unsubscribe from this group and all its topics, 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/f15ec86b-0f25-4ade-9c77-d3e8a6f756dcn%40googlegroups.com.

--

Atenciosamente.

[Clark Lind]

Ok, try changing the switch condition to:

switch (true)  { 

I may have gotten it wrong before.. lol

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BE6Xqte%2BsW0QLpt1n632SkC36jmT%2BWOitkn8Wh_ikn9%2B8oE5A%40mail.gmail.com.

[Silvio Cesar Dos Santos Junior]

Thanks Clark, with that modification he found the match and canceled the event but he didn't stop at the first match, how can i do that ?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHgJr-2mf9ZoDBzya5UbwbDUSEV8bMKfsekuh2WF9VO-%2B05dAA%40mail.gmail.com.

--

Atenciosamente.

[Clark Lind]

I've looked at the code and rethought it better. I think this will work smoother and faster since it only makes 1 or 2 calls to the sheet instead of a LOT lol 

This works from top down and only on the first one it finds, so if you MUST do it from bottom up, let me know and I'll rewrite this:

function canc_checkID(canc_page, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp) {

  const flatten = arr => [].concat.apply([], arr.map(a => Array.isArray(a) ? flat(a) : a)); //this is a utility function I use to flatten arrays

  var tempArr = age_page.getRange(2, 4, ws.getLastRow(), 1).getValues(); //get column D values from row2 to last row

  var canc_dataRangeEmail = flatten(tempArr); //convert array of arrays to single array ('flatten')

  canc_dataRangeEmail.map(item => item.toLowerCase().trim()); //go through array and turn to lowercase and trim

  var row = canc_dataRangeEmail.indexOf(canc_email.toLowerCase().trim()); //check to see if the lowercase/trimmed email is inside the array, and at which location.

  if (row === -1) { //if email is not inside the array, index will be -1

    // canc_erros(canc_page, canc_lastRow, canc_type, canc_name, canc_email, canc_emailStamp);

  } else {

    row += 2; //need to add 2 to the index since we skipped first row, and array starts at zero. This will give us the correct row

    var canc_eventId = age_page.getRange(row, 11).getValue();

var canc_date = age_page.getRange(row, 8).getValue();

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BE6XquE-3Gs_VOj8mY6XDA9v4N%3DePR_88rghM%2Bv9p%3DaOcd3Aw%40mail.gmail.com.