Does not compare

[Eneko Rodríguez Larrinaga]

Hello,

I am trying to do a process that loops through the rows of an excel sheet, and compares the value of a column with a value (sDie). If they are equal, it increments a counter by 1 ... but it does nothing, it seems that it does not enter the conditional. When if there are values ​​that match.

Attached screenshot of the code, as well as the excel sheet ...

By the way, sDie, has no value since just before this code, in the same function I have passed it to another column.

Thanks

[CBMServices Web]

We can not see where the value of sDie is assigned.

Perhaps if you provide all the code we can see better what is going on.

Suggest you declare variable outside of loop and then assign it a new value in each loop. Same for reading the spreadsheet values, read all before loop into an array and then look through array. As written, your code is slow and will take time to execute.

--
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/a457b23d-5643-4890-93db-27963919abd3n%40googlegroups.com.

[Eneko Rodríguez Larrinaga]

Egunon!!1

here you have all the code... I have put you in red where I collect the value of sDIE, and in green where I put it in another column (in yesterday's attachment of the sheet it looked filled, the last row that is not filled, ignore it). I'll try to declare the varibe outside the loop

/**

 * @OnlyCurrentDoc

 */

function OnEdit(e) {

    var hoja=SpreadsheetApp.getActiveSheet()

    //Recupero el numero de fila

        var celdaActiva=e.range;

        var NumFila=celdaActiva.getRow()

    //Recupero el nombre del alumno/a

    var sAlumno=hoja.getRange(NumFila,6).getValue() + hoja.getRange(NumFila,7).getValue() + hoja.getRange(NumFila,8).getValue()  + hoja.getRange(NumFila,10).getValue() + hoja.getRange(NumFila,11).getValue() + hoja.getRange(NumFila,12).getValue()  + hoja.getRange(NumFila,14).getValue() + hoja.getRange(NumFila,15).getValue() + hoja.getRange(NumFila,16).getValue() + hoja.getRange(NumFila,18).getValue() + hoja.getRange(NumFila,19).getValue() + hoja.getRange(NumFila,20).getValue() + hoja.getRange(NumFila,22).getValue() + hoja.getRange(NumFila,23).getValue() + hoja.getRange(NumFila,24).getValue() + hoja.getRange(NumFila,26).getValue() + hoja.getRange(NumFila,27).getValue() + hoja.getRange(NumFila,28).getValue()

    //Inserto en al celda AE (31) el nombre del alumnO (con su Die)

    hoja.getRange(NumFila,31).setValue(sAlumno)

    //Recupero el DIE

    var sDIE= sAlumno.substring(sAlumno.lastIndexOf(' - ')+3)

    //Inserto en al celda AF (32) el DIE del alumno

    hoja.getRange(NumFila,32).setValue(sDIE)

    //Recupero el tutor

    var sTutor=hoja.getRange(NumFila,5).getValue() + hoja.getRange(NumFila,9).getValue() + hoja.getRange(NumFila,13).getValue()  + hoja.getRange(NumFila,17).getValue() + hoja.getRange(NumFila,21).getValue() + hoja.getRange(NumFila,25).getValue()

    //Inserto en al celda AG (33) el tutor (con su email)

    hoja.getRange(NumFila,33).setValue(sTutor)

    //Recupero el email

    var smailTutor= sTutor.substring(sTutor.lastIndexOf('=')+1)

    //Inserto en al celda AH (34) el email

    hoja.getRange(NumFila,34).setValue(smailTutor)

    

    

    //TENEMOS QUE VER SI ESA PERSONA YA TIENE ANEXOS REGISTRADOS.

    //SI ES ASI, SE MANDA UN MENSAJE DE AVISO AL TUTOR Y JEFE DE ESTUDIOS.

    //LA BUSQUEDA LO HACEMOS POR DIE (Columna 32)

    var ultimaFila=hoja.getLastRow

    var NumVeces=0

    var sAnexos1="<h3>AVISO el alumno/a ya ha tenido anexos1 previos !!</h3>"

    sAnexos1=sAnexos1 +  "<table class='default'> <tr>"

    for (i=1; i<ultimaFila;i++){

      var PreguntaFiltro=hoja.getRange(i,32).getValue().getDisplayValue()

      if (PreguntaFiltro==sDIE){

          NumVeces=NumVeces+1

          sAnexos1=sAnexos1 + "<td><strong>    Fecha Anexo1 </strong></td> <td>" + hoja.getRange(i,3).getDisplayValue() + "</td></tr>"

          sAnexos1=sAnexos1 + "<td><strong>          Profesor/a </strong></td> <td>" + hoja.getRange(i,2).getDisplayValue() + "</td></tr>"

          sAnexos1=sAnexos1 + "<td><strong>          Motivo </strong></td> <td>" + hoja.getRange(i,29).getDisplayValue() + "</td></tr>"

          sAnexos1=sAnexos1 + "<td><strong>          Medida Correctora </strong></td> <td>" + hoja.getRange(i,30).getDisplayValue() + "</td></tr>"

          sAnexos1=sAnexos1 + "<td><strong>          Link al documento </strong></td> <td>" + hoja.getRange(i,36).getDisplayValue() + "</td></tr></table>"

      }

    }

    if (NumVeces>1){

      //enviamos el email al tutor y jefe de estudios para que lo sepan

      sAnexos1=sAnexos1 + "<br><br> Número total de Anexos1: " + NumVeces + " </body>"

      sEnviarA="xxx...@colegioirlandesas.com"  //hoja.getRange(NumFila,34).getValue()

      sAsunto="ANEXO 1 VECES: " + NumVeces + " " + hoja.getRange(NumFila,31).getValue() 

      GmailApp.sendEmail (sEnviarA,sAsunto,'',{htmlBody: BodyHtml})

    }

}

[Eneko Rodríguez Larrinaga]

I'll try to declare the varibe outside the loop --> nothing happens

[cbmserv...@gmail.com]

You had some bugs in your code that prevented it from running. I do not know exactly what you were trying to do but I fixed the obvious bugs that were preventing it from running.

 

First was name of function. If you wan this to run on every edit, you need to call it onEdit (not OnEdit).

 

The getDisplayValue() is not typically called that way, You were alreading getting the value, so was not sure what you wanted (I removed the getDisplayValue call).

 

Here is the code you can play with:

 

/**

 * @OnlyCurrentDoc

 */

 

function onEdit(e) {

    var hoja=SpreadsheetApp.getActiveSheet();

 

    //Recupero el numero de fila

       var celdaActiva=e.range;

       var NumFila=celdaActiva.getRow();

 

    //Recupero el nombre del alumno/a

    var sAlumno=hoja.getRange(NumFila,6).getValue() + hoja.getRange(NumFila,7).getValue() + hoja.getRange(NumFila,8).getValue()  + hoja.getRange(NumFila,10).getValue() + hoja.getRange(NumFila,11).getValue() + hoja.getRange(NumFila,12).getValue()  + hoja.getRange(NumFila,14).getValue() + hoja.getRange(NumFila,15).getValue() + hoja.getRange(NumFila,16).getValue() + hoja.getRange(NumFila,18).getValue() + hoja.getRange(NumFila,19).getValue() + hoja.getRange(NumFila,20).getValue() + hoja.getRange(NumFila,22).getValue() + hoja.getRange(NumFila,23).getValue() + hoja.getRange(NumFila,24).getValue() + hoja.getRange(NumFila,26).getValue() + hoja.getRange(NumFila,27).getValue() + hoja.getRange(NumFila,28).getValue()

 

    //Inserto en al celda AE (31) el nombre del alumnO (con su Die)

    hoja.getRange(NumFila,31).setValue(sAlumno)

 

    //Recupero el DIE

    var sDIE= sAlumno.substring(sAlumno.lastIndexOf(' - ')+3)

 

    //Inserto en al celda AF (32) el DIE del alumno

    hoja.getRange(NumFila,32).setValue(sDIE)

 

    //Recupero el tutor

    var sTutor=hoja.getRange(NumFila,5).getValue() + hoja.getRange(NumFila,9).getValue() + hoja.getRange(NumFila,13).getValue()  + hoja.getRange(NumFila,17).getValue() + hoja.getRange(NumFila,21).getValue() + hoja.getRange(NumFila,25).getValue()

 

    //Inserto en al celda AG (33) el tutor (con su email)

    hoja.getRange(NumFila,33).setValue(sTutor)

 

    //Recupero el email

    var smailTutor= sTutor.substring(sTutor.lastIndexOf('=')+1)

 

    //Inserto en al celda AH (34) el email

    hoja.getRange(NumFila,34).setValue(smailTutor)

 

    

 

    

    //TENEMOS QUE VER SI ESA PERSONA YA TIENE ANEXOS REGISTRADOS.

    //SI ES ASI, SE MANDA UN MENSAJE DE AVISO AL TUTOR Y JEFE DE ESTUDIOS.

    //LA BUSQUEDA LO HACEMOS POR DIE (Columna 32)

    var ultimaFila=hoja.getLastRow();

    var NumVeces=0

    var sAnexos1="<h3>AVISO el alumno/a ya ha tenido anexos1 previos !!</h3>"

    sAnexos1=sAnexos1 +  "<table class='default'> <tr>"

 

    for (i=1; i<ultimaFila;i++){

      var PreguntaFiltro=hoja.getRange(i,32).getValue();

      if (PreguntaFiltro==sDIE){

          NumVeces=NumVeces+1

          sAnexos1=sAnexos1 + "<td><strong>    Fecha Anexo1 </strong></td> <td>" + hoja.getRange(i,3).getDisplayValue() + "</td></tr>"

          sAnexos1=sAnexos1 + "<td><strong>          Profesor/a </strong></td> <td>" + hoja.getRange(i,2).getDisplayValue() + "</td></tr>"

          sAnexos1=sAnexos1 + "<td><strong>          Motivo </strong></td> <td>" + hoja.getRange(i,29).getDisplayValue() + "</td></tr>"

          sAnexos1=sAnexos1 + "<td><strong>          Medida Correctora </strong></td> <td>" + hoja.getRange(i,30).getDisplayValue() + "</td></tr>"

          sAnexos1=sAnexos1 + "<td><strong>          Link al documento </strong></td> <td>" + hoja.getRange(i,36).getDisplayValue() + "</td></tr></table>"

      }

    }

 

    if (NumVeces>1){

      //enviamos el email al tutor y jefe de estudios para que lo sepan

      sAnexos1=sAnexos1 + "<br><br> Número total de Anexos1: " + NumVeces + " </body>"

 

      sEnviarA=xxx...@colegioirlandesas.com  //hoja.getRange(NumFila,34).getValue()

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b51bc35b-4998-42cb-aad1-b80b1ff0da56n%40googlegroups.com.

[Eneko Rodríguez Larrinaga]

Hello!!

If the "onEdit" thing I had already noticed ... the getDisplayValue () thing, I would see it on the Internet (I'm still very green on this).

I've already made the changes and nothing ...

Actually, in case you have another idea of ​​how to do it, I want to see if the value of a column just entered via the form, how many times it appears in that column.

Thanks

[CBMServices Web]

Would suggest to get it working for you first, then you can start modifying it to do the calculations you want.

Do a copy paste of the code I pasted into the reply as is, there were several bugs in your code that I corrected. So to get all of them copy all and paste it as is to get it working properly.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/007b479a-56f0-4eb4-8be6-a0c2aa8fe8e5n%40googlegroups.com.

[Eneko Rodríguez Larrinaga]

Good morning!

I've already copied the code from your previous answer ... nothing, it's still the same.

Thank you

[CBMServices Web]

It may be that you have not authorized the script to run.

Go into the script editor and try running the script manually (it will fail because the environment variable e is not available when run manually). This should pop up a window to allow you to authorize the script. After that it should work fine on edits in the spreadsheet.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c377612f-a376-4675-a7db-dd54b8562aaen%40googlegroups.com.

[Eneko Rodríguez Larrinaga]

Thank you very much, and thank you again ...

IT ALREADY WORKS ... I had not noticed that yesterday I was getting another error, from a non-existent variable ... but today I have seen the error and it was ... after the evil "if" ...

SOLVED.

Thank you so much

[cbmserv...@gmail.com]

Excellent. Now you have a working base to evolve as needed.

 

George

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4a3909cb-b46c-46cf-96f9-fd3777bf2ccfn%40googlegroups.com.