Control de Inventarios con Google Sheets y Google Forms

En este artículo vemos como podemos integrar Google Forms y Google Sheets para crear un archivo de control de inventarios

Aquí les dejo los videos por si lo prefieren ver en video

No voy a explicar en detalle a que me refiero con un archivo de control de inventarios pero si les dejo el artículo de creación de un control de inventario con Google Sheets.

Integrando Google Forms y Google Sheets

Google Forms en un software muy sencillo, pero que nos permite conectarnos con otros aplicativos de Google como Sheets o Gmail

Esto nos permite hacer unos sistemas poderosos sin tener que complicarnos mucho la vida

Empezando porque para conectar Forms con Sheets no necesitamos código sino que ya hay una opción predefinida.

Desde Sheets podemos ir a “Herramientas”>”Crear un formulario”

Desde Forms nos vamos a “Respuestas” y en las opciones escogemos “Seleccionar el destino de las respuestas”

Así de fácil

Lo bueno de este segundo método es que podemos conectar múltiples formularios a un solo Sheets que es lo que vamos a hacer el día de hoy

Crear los formularios

Lo primero que vamos a hacer es crear una carpeta para nuestro proyecto en nuestro Google Drive, ya que vamos a tener varios archivos y no queremos que se pierdan

Para esto nos vamos a Google Drive y vamos A “Nuevo”>”Carpeta”

Una vez dentro de la carpeta vamos a “Nuevo”>”Formularios de Google”

Aquí vamos a ir creando las preguntas o los campos que vamos a necesitar para cada una de las pestañas.

Formulario de Productos

Aquí vamos a crear los nuevos productos

Primero le vamos a dar un título y un nombre de archivo de “Ingreso de Productos”

Entre otros podemos crear los siguientes:

• Referencia
• Nombre
• Precio
• Tipo
• Categoría
• Color

Formulario de Entradas

En este formulario vamos a escoger uno de nuestros productos e incluir información de la compra o entrada

Primero le vamos a dar un nombre de “Ingreso de Entradas”

El campo obligatorio es el nombre del producto y debe ser un desplegable

El resto de variables ya depende de su producto. Entre otros campos podemos incluir:

• Fecha
• Cantidad
• Valor
• Fecha de Vencimiento

Formulario de Salidas

Al igual que en el formulario de Entradas nuestro único campo obligatorio es el nombre, y podríamos incluir otros campos como:

Primero le vamos a dar un nombre de “Ingreso de Salidas”

• Fecha de Salida
• Cantidad
• Precio

Crear el Sheets

Una vez tenemos nuestros formularios creados, vamos a crear un nuevo archivo de Sheets dentro de nuestra carpeta Una vez dentro de la carpeta vamos a “Nuevo”>”Hoja de Cálculo de Google” y le damos un nombre de “Inventarios”

Por ahora no vamos a hacer nada más hasta que no conectemos los formularios

Conectar los formularios

Ahora tenemos que conectar cada uno de nuestros formularios.

Para esto nos situamos en la pestaña “Respuestas”, vamos a los 3 puntos de la esquina superior derecha y escogemos “Seleccionar el destino de la respuesta”

Escogemos la opción “Seleccionar una hoja de cálculo existente”

Nos abre un cuadro de diálogo para buscar un archivo

Nos vamos a la pestaña “Recientes” y escogemos nuestro archivo de Sheets “Inventarios”

Repetimos lo mismo para los 3 Formularios

Una vez hecha esta operación debemos tener nuestro Sheets con 3 pestañas, todas con un nombre largo (“Respuestas de Formulario 1”, “Respuestas de Formulario 2”) y un icono de Google Forms Morado

Podemos cambiar los nombres de pestaña a “Productos”, “Entradas” y “Salidas”

Ahora pueden hacer algunas pruebas de ingreso de productos, entradas y salidas para cerciorarse que las hojas correspondientes se estén actualizando

Conectar los desplegables de productos en los formularios de Entradas y Salidas

En los pasos anteriores, en los formularios de entradas y salidas dejamos una pregunta de Producto. Lo ideal sería que esa pregunta estuviera vinculado con la lista de mis productos y se actualizara sola

Esto solo lo podemos hacer con una macro.

Estos son los pasos (o pseudo algoritmo) que debe seguir nuestro código.

1. Conectarse con Google Sheets (Necesito el ID)
2. Conectarse con el formulario (Entradas o Salidas, necesito los IDs)
3. Conectarse con la pregunta específica (En este caso el nombre del producto, tanto para Entradas como para salidas)
4. Traer el listado de productos del Sheets
5. Insertarlo en las opciones de la pregunta
6. Asignarlo a un activador para que la función se ejecute cada vez que el formulario se envíe.

Primero que todo vamos a abrir nuestro editor de código. Para esto:

Desde nuestro Sheets, nos vamos a “Herramientas” > “Editor de Secuencia de Comandos”

Como se dan cuenta antes de cualquier cosa necesito las identificaciones o IDs tanto del archivo de Sheets como de mis formularios de Entradas y Salidas

Para esto vamos a buscar en el URL del archivo. Aunque podemos poner toda la URL y va a funcionar, es más eficiente, solo traer el ID, el cual encuentran después de la “d/” y antes del “/edit”

Vamos a “grabar” estos IDs antes de cualquier función

/*Identificación del archivo de Sheets
...se encuentra en la URL del archivo despues de "d/" y antes de "/edit"
*/
var sheetsId = "1umtsRn3BrPebiXAk4EsNGOTiswMklWY61YMcF5Ed18w"

/*Identificación de los formularios de Entrada y Salida
...se encuentra en la URL de cada archivo despues de "d/" y antes de "/edit"
*/
var formEntradaId ="17aeQEeLM8_Esd8hgV0IbqxtAO9ifReEkgJ2tA67bEfo";
var formSalidaId ="141odGolqGUV-PQxEkakJovF7Mt0-v-2OfGZ6Y7mvP-E";

//Nombre de la pestaña donde van a estar los nombres de productos ordenados
var nombreHojaProductos ="Listas";

Ahora, necesitamos los IDs de las preguntas.

Esto no es tan fácil de hacer como los IDs de los archivos. Para esto si necesitamos la ayuda de código.

Tenemos que hacer una función que nos arroje los IDs de las preguntas y después los guardamos. Después vamos a llamar esta función dos veces, una para el de entradas y otra para el de salidas. El código sería:

//Función que averigua el Id de las preguntas de un formulario.
function averiguarOpciones(formId) {
  //Conectarse con el Forms y el Sheets en la hoja con los productos 
  var formulario =FormApp.openById(formId);
  var archivoSheets = SpreadsheetApp.openById(sheetsId);
  var hojaOpciones = archivoSheets.getSheetByName(nombreHojaProductos);

  
  /* Recorrer todas las preguntas y loguear el título y el ID de cada pregunta del formulario */
  var preguntas=formulario.getItems();
  preguntas.forEach(function(item){
    Logger.log(item.getTitle());
    Logger.log(item.getId().toString());
  })
}

//Correr la funcion averiguaropciones() para los 2 formularios
function encontrarIdsPreguntas(){
  Logger.log("Estas son las preguntas del Formulario de Entradas");
  averiguarOpciones(formEntradaId);
  Logger.log("Estas son las preguntas del Formulario de Salidas");
  averiguarOpciones(formSalidaId);
}

Ahora, en nuestro editor, escogemos la función encontrarIdsPreguntas y le damos play

Despues vamos a “Ver”>”Registros”

Y nos debe identificar el ID de la pregunta “Nombre” o “Producto”. Lo debemos copiar y tener listo para incluir en nuestro código

En mi caso específico, el código de la pregunta es el mismo, porque dupliqué el formulario de entradas cuando fui a crear el de Salidas, pero no siempre va a ser así.

Después de correrlas, ya no necesitamos estas funciones más, entonces las pueden borrar si desean.

Una vez ya tenemos estos códigos ahora si podemos hacer el resto de nuestro código.

Básicamente vamos a hacer una función que se conecte con una pregunta cualquiera de un formulario cualquiera

Después vamos a llamar esa función para nuestros formularios y nuestra pregunta

Aquí les dejo el código explicado:

//Función que llena las opciones de un formulario.
function llenarOpciones(colNombres,formId,preguntaId) {
  //Conectarse con el Forms y el Sheets en la hoja con los productos 
  var formulario =FormApp.openById(formId);
  var archivoSheets = SpreadsheetApp.openById(sheetsId);
  var hojaOpciones = archivoSheets.getSheetByName(nombreHojaProductos);
  
  //Convertir el rango de productos en una lista
  var opciones = hojaOpciones
         .getRange(1,colNombres,hojaOpciones.getLastRow()-1,1)
         .getValues()
         .map(function(opcion){
                return opcion[0];    
           });  
  //Conectarse con la pregunta
  var pregunta = formulario.getItemById(preguntaId);
  
  //Insertar las opciones en la pregunta
  pregunta.asListItem().setChoiceValues(opciones);  
  
}

function opcionesEntradas(){
  var colNombres = 1;
  var formId = formEntradaId;
  var preguntaId = "937436962";
  llenarOpciones(colNombres,formId,preguntaId);
}

function opcionesSalidas(){
  var colNombres = 1;
  var formId = formSalidaId;
  var preguntaId = "937436962";
  llenarOpciones(colNombres,formId,preguntaId);
}

function actualizar(){
  opcionesEntradas()
  opcionesSalidas()
}

Asignar el activador

Por ultimo vamos a llamar la función actualizar() desde un activador. Para esto:

• Vamos al editor de secuencias de comando
• Vamos a “Editor”>”Activadores del Proyecto Activo”
• Damos clic en “Crear nuevo activador”
• Vamos a seleccionar lo siguiente

Crear el Inventario

Ya teniendo nuestros formularios actualizándose, ahora vamos a crear nuestra base de Inventarios con los siguientes campos:

• Referencia
• Nombre
• Entradas
• Salidas
• Stock

Todo lo vamos a trabajar con fórmulas tipo “Array” y nuestras fórmulas van a quedar en el encabezado.

Vamos una por una

Referencia

En esta columna queremos traer todas las referencias que pueden tener algun inventario

En la celda A1 vamos a ingresar lo siguiente

={"Referencia";UNIQUE(Entradas!G2:G)}

Aquí lo que estamos diciendo es: en la primera fila vamos a poner el título “Referencia”, después, en la segunda fila vamos a traer todos los nombres de productos (Que en mi caso están en la columna G) y los vamos a traer sin duplicados con la ayuda de la función UNIQUE. (Aquí les dejo un tutorial de la función UNIQUE)

Nombre

En este campo queremos simplemente traer el nombre correspondiente a la referencia, y esto lo hacemos con la ayuda de BUSCARV.

En la celda B1, vamos a ingresar la siguiente formula:

=ArrayFormula(SI.ERROR(BUSCARV(A1:A;{Productos!C:C\Productos!B:B};2;0);""))

Donde Productos!C:C es la columna con la referencia y Productos!B:B es la columna con el nombre

Entradas

Aquí vamos a sumar todas las unidades compradas o ingresadas al inventario correspondientes a la referencia de la fila.

En la celda C1 vamos a ingresar:

=ArrayFormula(SI(B1:B="Nombre";"Entradas";SI(SUMAR.SI(Entradas!G:G;A1:A;Entradas!H:H)=0;"";SUMAR.SI(Entradas!G:G;A1:A;Entradas!H:H))))

Lo único raro de esta fórmula es que en la fila 1, pondrá el encabezado “Entradas”

Después de eso, va a sumar la columna H de la pestaña entradas (donde están las cantidades), con la condición que encuentre la referencia en la columna G

Salidas

La fórmula es muy similar a la de Entradas

En la celda D1, vamos a ingresar

=ArrayFormula(SI(B1:B="Nombre";"Salidas";SI(SUMAR.SI(Salidas!F:F;A1:A;Salidas!C:C)=0;"";SUMAR.SI(Salidas!F:F;A1:A;Salidas!C:C))))

Inventario

Por último, vamos a restar las salidas de las entradas

En la celda E1 vamos a ingresar la siguiente formula

=ArrayFormula(SI( A1:A="Referencia";"Inventario";SI( A1:A="";"";C1:C-D1:D+E1:E)))

Y listo!

Conclusión

Como ven, cuando tenemos unos tipos de producto sencillo, usar formularios de Google Forms para rápidamente ingresar las entradas y las salidas, es una muy buena opción y podemos armar un archivo de control de inventarios muy robusto

Quedo pendiente de sus comentarios y sugerencias