Como hacer una función personalizada para una hoja de cálculo con Google Apps Script

Artículo perteneciente a la introducción de Google Apps Script
Artículo referente a como usar Google Apps Script en spreadsheets

En muchas ocasiones ocurre que las hojas de cálculo se nos quedan un poco "cortas", las funciones para trabajar con las celdas están muy bien pero no siempre cumplen con nuestras necesidades y hacer según que cálculos u operaciones genera un engorro horrible, ya ni hablemos si hemos de trabajar con textos.

La mejor forma de solucionar estos problemas es crear nuestras propias funciones para trabajar directamente desde la barra de formulas de la hoja de cálculo. Para conseguir esto solo hemos de crear una función dentro de un archivo de código.gs (si no tienes claro este paso te recomiendo que mires los siguientes tutoriales: Hola Mundo en GAS, Como leer una hoja de cálculo con GAS) y llamarla directamente desde la celda de la hoja de cálculo con =mifuncion(parametro1,parametro2...)

Vamos ha hacer un ejemplo. Vamos a crear una función que se encargará de calcular la distancia entre dos puntos en un mundo de 2D. El código es el siguiente:

function CalcularDistancia(xi,yi,xf,yf)
{
  return Math.sqrt(Math.pow((xf-xi),2) + Math.pow((yf-yi),2));
}

Y dentro de la hoja de cálculo llamaremos a esta función como si fuera cualquier otra operación matemática.


Lo que devolvemos de la función es lo que se pondrá en la celda, así que realmente podemos trabajar con números, con strings o con lo que queramos y nos da la capacidad incluso de recoger información de otras hojas de cálculo para generar el contenido de esta.

En capítulos futuros ahondaremos más sobre como implementar estas funciones y a desplegar toda la potencia que tiene.

NOTA: Si te interesa aprender más sobre fórmulas tal vez te interese echarle un vistazo a este blog

11 comentarios:

  1. una pregunta descolgada, ando buscando como hacer en una macro de scripts GAS la funcion de autocompletar datos al arrastrar una celda , en el viejo excel era:
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D14"), Type:=xlFillDefault

    pero no encuentro la contrapartida para las hojas de calculo de drive
    ¿alguien tiene alguna idea?

    ResponderEliminar
  2. Hola alguien.te ha contestado esto o ya lo hiciste?

    ResponderEliminar
  3. por drive puedes hacer una lista desplegable dependiendo de una columna que tengas en otra parte, en Datos>Validación de datos> Lista a partir de un intervalo (marcas el intervalo) y listo el te va ir autocompletado cada tecla que vas metiendo y de una validas que tienen que ser obligatoriamente lo que tiene el intervalo

    ResponderEliminar
    Respuestas
    1. Gracias por responder, este comentario se me había quedado descolgado.

      Buena solución

      Eliminar
  4. Hola!, Una pregunta, se puede pasar una celda como parámetro?, veo que al pasar una celda pasa el valor pero yo quiero ver el color del fondo de la celda no solo el valor y no sirve hacerlo con la celda activa . La idea es sumar un conjunto de celdas si el fondo es rojo y puedo pasar una sola celda o un array de celdas a esa funcion , por ejemplo la funcion se llama Sisepago y en la formula seria =Sisepago(A2) +Sisepago(B2) o =Sisepago(A2,B2)
    Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Pues te tengo que decir que no, en las formulas solo se pasa el valor.

      Para hacer lo que quieres se debería hacer con el onEdit de forma que cada vez que alguien modifique el valor de las celdas que te interese se mire el color y se procede como necesitas.

      No es tan facil como parece

      Siento no ser de más ayuda

      Nos vemos

      Eliminar
    2. Gracias por responder, lo pude resolver con Arguments!, lo que si no funciona con todos los argumentos, solo con el primero pero igual me sirve

      function prueba() {
      Rojo = '#ff0000';
      var suma = 0;
      var spreadsheet = SpreadsheetApp.getActive();
      for (var i=0;i<arguments.length;i++) {
      celda = spreadsheet.getRange(arguments[i]);
      if (celda.getBackground() != Rojo)
      suma=suma+celda.getValue();
      }
      return suma;
      }

      Llamando la función desde otra si toma todos los argumentos, vaya a saber que falta para que tome todos

      function prueba0() {
      return prueba("I6","I7","I8");
      }

      Eliminar
    3. No lo acabo de ver el como funciona, pero si te funciona, oye, encantado.

      Gracias por compartirlo

      Eliminar
    4. Este comentario ha sido eliminado por el autor.

      Eliminar
    5. Me acabo de dar cuenta que estaba haciendo mal la llamada a la función , en lugar de comas hay que poner punto y coma para separar los parámetros !!
      Esta seria una llamada a la función =Sisepago("B3";"C3";"D3") y devuelve la suma de las celdas que tengan color de fondo distinto de rojo, eso me sirve para ver rápidamente lo que aun se debe y cuando se paga se cambia el color rojo de fondo por cualquier otro y la suma se actualiza.
      Los parámetros se toman con "arguments" recorriendo en el bucle el largo del vector arguments, y con spreadsheet.getRange(arguments[i]) se toma control de la celda con sus propiedades y todo

      for (var i=0; i<arguments.length ;i++) {
      celda = spreadsheet.getRange(arguments[i])

      y la llamada a la función se separa con puntos y comas, Sisepago("A1";"B1")
      Saludos!!

      Eliminar
    6. Perfecto.

      Me alegro que te haya funcionado

      Ya me iras contando nuevos proyectos que vayas haciendo

      Nos vemos

      Eliminar

Tal vez te interese