Macro de UserForm en Google Scripts

Uno de los grandes problemas al pasar libros de Excel a Google Sheets es cuando nuestro libro contiene alguna Macro que llame a una UserForm, por eso el día de hoy quiero hacer un aproximamiento a crear algo que sea lo mas parecido usando Google Scripts.

Si bien de forma nativa Gsheets permite crear formulario como si fueran Forms, estos no siempre pueden cumplir todas nuestras expectativas.

Algunos puntos importantes para tomar en cuenta son:

  • No podemos crear las ventanas emergentes. Por lo que todo tendrá que ser hecho en la hoja de cálculo.
  • Para imitar el listbox de la foto de arriba, usaremos una tabla normal.
  • Los botones de funcionamiento son imágenes planas.
  • Estoy usando un ejemplo de funcionamiento de un usuario que me pregunto hace poco como hacerlo. Por lo que el código es totalmente perfeccionable.

Evaluando el problema.

Para recrear la UserForm veamos el problema. La macro original fue creada pensando en una tabla de 8 columnas con filas indefinidas, que se veía más o menos así.

Todo eso, debe ser traspasado a una ListBox cuando el usuario presiona el botón “Inventario”.

Adicional, una Textbox fue colocada para crear búsquedas de los productos con un LIKE al apretar el botón “Buscar”.

Por último, si se evalúa que un artículo entro al inventario, debe seleccionarse con doble click para enviarla a la hoja “entradas”.

Ahora con esto en la mesa, vamos a resolver cada una de las partes.

El aproximamiento.

Variables globales.

Antes de comenzar, vemos que en el problema se usa la hoja “Inventario” para llamar en dos procedimientos diferentes. Adicional, la hoja “template” siempre debe de existir seleccionada y la tabla del inventario es vital para todos los procedimientos. Un ultimo punto, es que en GScripts es una buena practica tener siempre en un objeto la variable del libro activo.

Para no estar repitiendo estas variables dentro de cada función, vamos a crearlas en el ámbito global:

var worksheet = SpreadsheetApp.getActiveSpreadsheet();             //Este libro
var template = worksheet.getSheetByName("template");               //hoja del usuario. Cambiar nombre entre comillas de ser 
var inventario = worksheet.getSheetByName("INVENTARIO");          
var entrada = worksheet.getSheetByName("ENTRADAS");
var data = inventario.getRange("B1").getDataRegion().getValues();  // region actual de la hoja inventario.

El método getDataRegion() es bastante útil en estos casos, considerando que las filas tienden a crecer con el tiempo.

Listbox con todo el inventario.

Empecemos por lo más fácil. Para traer al rango A5 de mi hoja “template” el inventario, haremos uso del método getDataRegion que ya definimos en la variable data.

La función se ve como sigue.

function call_inventory() {
  // usa todos los datos de la hoja inventario
  // los deposita en la hoja del usuario
  template.getRange(6,1,100000,8).clearContent();
  template.getRange(5,1,data.length,8).setValues(data);
}

Queda bastante recortada, considerando el código origen que podría tener en la UserForm y cumple con la misma función: “Llamar a todo el inventario en una listbox” pero sin la listbox.

Filtro de parecidos dentro del inventario.

Como segundo paso, entremos a la búsqueda por LIKE. Este paso me resulto un poco mas tramposo, ya que Google no tiene tal cual una función en Scripts para esto. La vuelta que se me ocurrió fue usar la función QUERY.

function search_desc(){
  // realiza la busqueda por lo escrito en la celda
  // es sensible a mayusculas
  template.getRange(5,1,100000,8).clearContent();
  searchy = template.getRange("E3").getValue();
  query = '=QUERY(INVENTARIO!' + inventario.getRange("B1").getDataRegion().getA1Notation() + ';"SELECT * WHERE B LIKE \'' + '%' + searchy + '%\'")'; 
  template.getRange("A5").setFormula(query);
}

Como podrás ver, la función Query usa el lenguaje SQL para manejar un rango de datos. En nuestro caso, estoy aplicandolo a toda la tabla de la hoja “inventario”, usando la columna B para filtrar según lo escrito en la celda E3 (que está imitando al textbox).

Si bien puede no ser la respuesta, creo que es una aproximación bastante valida.

La entrada de artículos.

Por último, una función mas para rellenar una hoja, con la diferencia que esta requiere agregar una fila para dejar el nuevo articulo hasta arriba del rango.

function in_product(){
   // agrega el codigo en el que el usuario se encuentra depositado
   // en la hoja entrada insertando una nueva celda
   var rng = template.getActiveCell().getRowIndex();
   var codigo = template.getRange(rng,1).getValue();
   if(rng>5 && codigo != ''){
      entrada.insertRowBefore(6);
      entrada.getRange("E6").setValue(codigo);
   }
}

Debido a que no existe el evento DblClick dentro de Google Sheets y Scripts, tuve que crear una función que mandara el articulo seleccionado por el usuario a la hoja de entradas. Siempre asegurándome que el usuario este dentro de la región de artículos para no enviar basura a la hoja de captura. Esto último se logró conociendo la posición de la fila en la que el cursor se encuentra posicionada.

Formato a hoja template.

Ahora que ya tenemos nuestras funciones en Script, vamos a asignarles algunos botones. Para ello, vamos a la pestana insertar, y luego Dibujo.

En mi caso, seleccioné insertar cuadros de texto para poder escribir dentro de ellos, pero dejo a tu elección la forma.

Una vez terminada, damos aceptar y vamos a la forma. Dentro de ella, del lado superior derecho, veremos el menú de tres puntos, en el cual podremos asignarle un procedimiento. Aquí tendremos que escribir el nombre de la función literal, no nos ayuda como lo hacen las formas de Excel (¡uhm! 100 puntos menos para Griffindor).

Repetimos este procedimiento hasta tener los tres botones, uno para cada función, y damos un poco de formato a la hoja.

¡Listo! Ahora prueba tu despojo de Macro UserForm y demuestra que para todo hay una solución en Google Scripts, siempre que seas creativo (y te paguen 40dlls por hora jojojo).

Cualquier duda nos leemos abajo.

Macro de UserForm en Google Scripts

2 comentarios en «Macro de UserForm en Google Scripts»

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Scroll hacia arriba