menu

omitsis

digital challenges

blog

Google Apps Scripting para spreadsheets

Muchos de vosotros habréis trabajado con Excel durante años, y también quizá habíais entrado en el mundo de la programación en las hojas de estilo, la programación con formulas (avanzadas) o tal vez habíais oído hablar y conocíais su existencia.

Google Spreadsheets, el "office" de Google para hojas de cálculo, incorpora, al igual que todos sus productos de la misma gama, una interfaz de programación en un lenguaje idéntico al Javascript, pero incorporando nuevas funcionalidades de Google Apps Scripts, que también nos da soporte y una pasarela de comunicación entre sus productos: Gmail, Documents, Finanzas, etc.

Hoy no os venimos a hablar de las bondades de Google Docs, sino a introduciros al mundo de Google Apps Scripts para spreadsheets, con un ejemplo.

Supongamos que tenemos un listado de cuentas de usuarios, con sus respectivos passwords, y queremos que la hoja de cálculo nos haga una estimación de la seguridad de esa clave (password strength).

Para empezar, crearemos una nueva hoja de cálculo en Google Docs, y tal y como nos explican en los tutoriales en la documentación oficial, tenemos que ir a "Tools" > "Script manager". Ahí crearemos un script, con el nombre que queramos.

A continuación os dejo el código comentado, es muy sencillo y si conocéis Javascript no os costará nada seguirlo.

//Evento onEdit, se ejecutará cada vez que se modifique una celda
function onEdit(event)
{  
  //Rango activo
  var r = event.source.getActiveRange();
  //Fila en la que estamos
  var row = r.getRow();
  //Columna actual
  var col = r.getColumn();
  
  //Valor del rango activo (de lo que se ha modificado)
  var val = r.getValues();
  val = val[0][0];  
  
  //A partir de la fila 2
  if(row>1)
  {   
    //...y solo para la columna "B"
    if(col==2)
    {      
      //miramos si hay algun valor
      if(val.length>0)
      {
        var color = "";
        //calculo la fuerza del password con una función
        switch(testPassword(val))
        {
          case "very weak":
            color = "#cccccc"; // gris
            break;
          case "weak":
            color = "#ff6739"; // rojo
            break;
          case "mediocre":
            color = "#ffd53f"; //amarillo
            break;
          case "strong":
            color = "#c0ff45"; // verde claro
            break;
          case "stronger":
            color = "#1cff23"; // verde
            break;
          default:
            color = "#cccccc"; // gris
            break;
        }
        //marco el color
        r.setBackgroundColor(color);
      } 
    } 
  }
}

//esta puede ser una función cualquiera, esta la saqué del primer resultado en google, las hay a pares.
function testPassword(passwd)
{
    var intScore   = 0
    var strVerdict = "weak"
    var strLog     = ""

    // PASSWORD LENGTH
    if (passwd.length<5)                         // length 4 or less
    {
        intScore = (intScore+3)
        strLog   = strLog + "3 points for length (" + passwd.length + ")\n"
    }
    else if (passwd.length>4 && passwd.length<8) // length between 5 and 7
    {
        intScore = (intScore+6)
        strLog   = strLog + "6 points for length (" + passwd.length + ")\n"
    }
    else if (passwd.length>7 && passwd.length<16)// length between 8 and 15
    {
        intScore = (intScore+12)
        strLog   = strLog + "12 points for length (" + passwd.length + ")\n"
    }
    else if (passwd.length>15)                    // length 16 or more
    {
        intScore = (intScore+18)
        strLog   = strLog + "18 point for length (" + passwd.length + ")\n"
    }

    // LETTERS (Not exactly implemented as dictacted above because of my limited understanding of Regex)
    if (passwd.match(/[a-z]/))                              // [verified] at least one lower case letter
    {
        intScore = (intScore+1)
        strLog   = strLog + "1 point for at least one lower case char\n"
    }

    if (passwd.match(/[A-Z]/))                              // [verified] at least one upper case letter
    {
        intScore = (intScore+5)
        strLog   = strLog + "5 points for at least one upper case char\n"
    }

    // NUMBERS
    if (passwd.match(/\d+/))                                 // [verified] at least one number
    {
        intScore = (intScore+5)
        strLog   = strLog + "5 points for at least one number\n"
    }

    if (passwd.match(/(.*[0-9].*[0-9].*[0-9])/))             // [verified] at least three numbers
    {
        intScore = (intScore+5)
        strLog   = strLog + "5 points for at least three numbers\n"
    }

    // SPECIAL CHAR
    if (passwd.match(/.[!,@,#,$,%,^,&,*,?,_,~]/))            // [verified] at least one special character
    {
        intScore = (intScore+5)
        strLog   = strLog + "5 points for at least one special char\n"
    }

                                 // [verified] at least two special characters
    if (passwd.match(/(.*[!,@,#,$,%,^,&,*,?,_,~].*[!,@,#,$,%,^,&,*,?,_,~])/))
    {
        intScore = (intScore+5)
        strLog   = strLog + "5 points for at least two special chars\n"
    }

    // COMBOS
    if (passwd.match(/([a-z].*[A-Z])|([A-Z].*[a-z])/))        // [verified] both upper and lower case
    {
        intScore = (intScore+2)
        strLog   = strLog + "2 combo points for upper and lower letters\n"
    }

    if (passwd.match(/([a-zA-Z])/) && passwd.match(/([0-9])/)) // [verified] both letters and numbers
    {
        intScore = (intScore+2)
        strLog   = strLog + "2 combo points for letters and numbers\n"
    }

                                // [verified] letters, numbers, and special characters
    if (passwd.match(/([a-zA-Z0-9].*[!,@,#,$,%,^,&,*,?,_,~])|([!,@,#,$,%,^,&,*,?,_,~].*[a-zA-Z0-9])/))
    {
        intScore = (intScore+2)
        strLog   = strLog + "2 combo points for letters, numbers and special chars\n"
    }

    if(intScore < 16)
    {
       strVerdict = "very weak"
    }
    else if (intScore > 15 && intScore < 25)
    {
       strVerdict = "weak"
    }
    else if (intScore > 24 && intScore < 35)
    {
       strVerdict = "mediocre"
    }
    else if (intScore > 34 && intScore < 45)
    {
       strVerdict = "strong"
    }
    else
    {
       strVerdict = "stronger"
    }
    
  return strVerdict;
    
}

Una vez guardado, nos fijaremos que cada vez que escribimos algo en la segunda columna, el color de la celda cambiará según nuestros parámetros.

A partir de este ejemplo simple, se abre un mundo de posibilidades, como mandar emails, marcar fechas de cambios, hacer tareas periódicas de limpieza, formularios, y un largo etcétera.

Para cualquier consulta sobre el tema o asesoramiento profesional, no dudéis en poneros en contacto con nosotros.