Control de Inventarios con Google Sheets y Google Forms

Control de Inventarios con Sheets y 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

28 comentarios en “Control de Inventarios con Google Sheets y Google Forms”

  1. Hola ¿como estas?
    tengo un inconveniente, cuando ingreso la formula para inventario:
    =(BUSCARV(A1:A;{Productos!C:C\Productos!B:B};2;0)
    me aparece: error de analisis de la formula, como la puedo corregir?

  2. Hola, tengo un problema, al borrar manualmente en sheets el listado creado por forms, al ingresar nuevamente la info se crean en la ultima celda usada hacia abajo, como la vuelvo a colocar al inicio?

  3. Yo deseo hacer lo siguiente: Ingresar in Id en un google forms y este se vaya a buscar en una google sheets que previamente este cargado con los IDs, si la ID se encuentra en la hoja sheet continuar con el formulario y cargar los demas datos en el formulario. Muchas gracias por su ayuda

  4. Luis Ernesto Valderrama

    Hola buen día

    El inventario me estaba funcionando muy bien, pero hoy agregue un insumo y al ejecutar la macro de actualizar el insumo en los formulario me salió este error en rojo y no actualizó el listado en el formulario:

    “Exception: No se pudieron establecer las opciones porque superaste la cantidad máxima.”

    Agradezco apoyo u orientación…

  5. Gracias Juan por los cursos. Tengo el siguiente inconveniente al ejecutar la función me figura “Error Exception: Questions cannot have duplicate choice values.”
    y me lleva a que el error está en esta línea pregunta.asListItem().setChoiceValues(opciones);

    me podrías dar una mano? muchas gracias

      1. Franklin Cabanillas

        Buenas noches, en la misma linea tambien me sale como error, ¿Como podria solucionarlo porfavor?, muchas gracias de antemano.

  6. hola juan, en el macro tira un error en funcion llenar opciones, especificamente en esta linea: var formulario =FormApp.openById(formId), como lo puedo solucionar??

  7. Hola amigo gracias por el don de compartir conocimiento… Te consulto habría forma de usar un form para dar acceso a determinadas hojas dependiendo de la clave que se use en el form… O Algo como usuarios de ingresos

    1. Hola!

      Esta bien avanzado

      La verdad puede que haya, pero la naturaleza de Sheets y de Google es usar todo con las credenciales de la cuenta

      Una opción es tener distintos archivos y darle permiso a los usuarios que necesites

      Un gran saludo!

  8. Hola, muy util tus videos, muchas gracias por las explicaciones. Una consulta, es posible validar la cantidad de unidades en inventario antes de confirmar una salida? La idea es confirmar que tenemos stock para no realizar una carga inconsistente.

  9. Saludos Juan. Una pregunta, yo puedo exportar estos archivos que realice desde mi cuenta de Google Gmail-Google Drive a otra cuenta de Google..? sin que se alteren las formulas y por supuesto configurando los detalles pertinentes. Por que intente una exportación como supuse y no funciono, me modifico añadiéndome unas formulas nuevas. Y no me abrio por supuesto el Editor de secuencias de comandos.

    Gracias de antemano por tu respuesta…estoy haciendo experimentos con esto.

  10. Saludos desde Venezuela, Gracias por tu tiempo y videos, pero he tenido problemas con las formulas en el video 2, minuto 4:20. Los pasos anteriores me salieron bien, esta formula me da un “error de análisis de formula” te pongo aquí lo que coloco yo: =ArrayFormula(BUSCARV(A2:A;{Productos!C:C\Productos!B:B};2;0))he seguido cuidadosamente tus pasos…tengo capture de pantallas que no se donde enviarte para ver si me puedes ayudar ..gracias de antemano…un abrazo fuerte….

Deja un comentario

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

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This