¿Cómo crear un formulario de entrada de datos automatizado en Google Sheets y Apps Script?
– Automated Data Entry Form – Google Sheet
Google proporciona un formulario de entrada de datos predeterminado que es un formulario de Google. Se puede utilizar para un trabajo básico de entrada de datos. Cuando se trata de trabajos de entrada de datos complejos y de gran escala, siempre es preferible desarrollar un formulario de entrada de datos automatizado para que la tarea sea fácil de usar y mejorar la precisión y la velocidad.
La hoja de Google tiene muchas ventajas sobre el formulario de entrada de datos en MS Excel, MS Access y otras aplicaciones instaladas en su máquina local. Le permite utilizar la aplicación de hoja de cálculo más avanzada sin haberla instalado en su computadora.
Como Google Sheet está disponible en la nube, puede acceder a él fácilmente desde cualquier lugar y en cualquier momento sin ninguna limitación. Puede compartir y colaborar con sus colegas, amigos y otras personas. Además de todo esto, Google Apps Script le permite agregar funciones avanzadas en la aplicación existente y automatizar todas las tareas repetitivas y el trabajo de entrada de datos.
En este tutorial, aprenderemos cómo crear un formulario de entrada de datos automatizado en Google Sheet con la ayuda de Apps Script.
Siga los pasos a continuación para desarrollar un formulario de entrada de datos automatizado en Google Sheet y Apps Script
- Inicie sesión en Google Drive con su ID de Google existente (1) o cree una nueva cuenta de Google (3) y luego inicie sesión en Google Drive.
2. Haga clic en Nuevo y luego haga clic en Carpeta para crear una nueva carpeta para nuestro proyecto.
3. En la nueva ventana de la carpeta, basta con introducir ‘ Google Hoja de Automatización ‘ y haga clic en Ok .
4. Ahora abra la carpeta Google Sheet Automation para crear Google Sheet en ella.
5. Haga clic con el botón derecho en la carpeta Automatización de hojas de Google y luego seleccione Hojas de cálculo de Google -> Hoja de cálculo en blanco en el menú emergente.
Una vez que haga clic en Hoja de cálculo en blanco, se creará y abrirá la hoja de cálculo en blanco en la nueva pestaña del navegador.
6. Cambie el nombre de la hoja de cálculo por «Formulario de ingreso de datos del empleado».
7. Cree y cambie el nombre de tres hojas de trabajo en la hoja de cálculo recién creada como ‘ Formulario de usuario ‘, ‘ Base de datos ‘ y ‘ Hoja de soporte ‘.
Utilizaremos la hoja ‘Formulario de usuario’ para crear un formulario de entrada de datos, la hoja de ‘Base de datos’ para almacenar los datos transferidos desde el formulario de entrada de datos y la ‘Hoja de soporte’ para contener la lista de departamentos.
8. Pasemos a la hoja Departamento y creemos una lista de departamentos en la Columna A.
9. Saltemos a la hoja de Base de datos y creemos los encabezados de columna requeridos en la fila 1 comenzando desde la Columna A a la H, por ejemplo , ID de Emp, Nombre de Emp, Sexo, ID de correo electrónico, Departamento, Dirección, Enviado el y Enviado por.
10. Pasemos a la hoja ‘Formulario de usuario’ y comencemos a diseñar el Formulario de ingreso de datos del empleado con etiquetas, campos de entrada y botones obligatorios.
Aquí, tenemos un campo para buscar los datos y seis campos de entrada son para el trabajo de entrada de datos. Emp ID (C4) es un campo de entrada para buscar el registro de un empleado en particular con Emp ID.
ID de empleado (celda C7), Nombre del empleado (celda C9), Género (celda C11), ID de correo electrónico (celda C13), Departamento (celda C15) y Dirección (celda C17) son los principales campos de entrada que el usuario actualizará para transferir los datos de esto desde la hoja de la base de datos. En estos campos de entrada, utilizaremos Género y Departamento como un menú desplegable.
11. Para crear un menú desplegable para Género, simplemente haga clic en el menú Datos y luego seleccione Validación de datos .
12. En la ventana Validación de datos, seleccione ‘Lista de elementos’ en el menú desplegable de Criterios e ingrese ‘Mujer, Hombre, Otro’ en el cuadro de texto. Marque la casilla ‘Mostrar lista desplegable en la celda’ y ‘Mostrar texto de ayuda de validación:’. Ingrese el texto de ayuda en el cuadro de texto dado como ‘Seleccione Género en el menú desplegable’. Ahora haga clic en Guardar para crear el menú desplegable de Género.
13. Repita el mismo paso para crear el menú desplegable para Departamento. Para el menú desplegable Departamento, utilizaremos la lista disponible en la hoja de datos de soporte.
Seleccione ‘Lista de un rango’ en el menú desplegable de Criterios y proporcione la referencia de los elementos de la lista disponibles en la hoja ‘Datos de soporte’.
En este formulario, tenemos 5 botones diferentes, por ejemplo, Buscar, Guardar, Modificar, Eliminar y Borrar para realizar las acciones relevantes según el nombre dado.
14. Para crear un botón, simplemente haga clic en el menú Insertar y luego haga clic en Dibujo .
15. En la ventana Dibujo, simplemente dibuje un rectángulo redondeado y rellénelo con el color deseado y dé el título como Buscar. Después de crear el botón, haga clic en Guardar y cerrar para insertar el botón en la hoja.
16. Siga los pasos 14 y 15 para crear los botones Guardar, Modificar, Eliminar y Borrar.
Ahora, hemos terminado con el diseño del formulario, creando menús desplegables e insertando los botones requeridos. Pasemos a la ventana de código.
17. Para abrir la ventana de Apps Script, simplemente haga clic en el menú Herramientas y luego haga clic en Editores de secuencias de comandos.
Una vez que haga clic en Editor de scripts en el menú Herramientas, se abrirá la ventana de Apps Script para el proyecto de entrada de datos.
18. Escribamos una función para borrar el formulario de entrada de datos. Asignaremos esta función al hacer clic en el evento del botón Borrar disponible en la hoja de formulario de usuario.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| // Function to Clear the User Form function clearForm() { var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm = myGoogleSheet.getSheetByName(«User Form»); //declare a variable and set with the User Form worksheet //to create the instance of the user–interface environment to use the alert features var ui = SpreadsheetApp.getUi(); // Display a dialog box with a title, message, and «Yes» and «No» buttons. The user can also // close the dialog by clicking the close button in its title bar. var response = ui.alert(«Reset Confirmation», ‘Do you want to reset this form?’,ui.ButtonSet.YES_NO); // Checking the user response and proceed with clearing the form if user selects Yes if (response == ui.Button.YES) {
shUserForm.getRange(«C4»).clear(); //Search Field shUserForm.getRange(«C7»).clear();// Employeey ID shUserForm.getRange(«C9»).clear(); // Employee Name shUserForm.getRange(«C11»).clear(); // Gender shUserForm.getRange(«C13»).clear(); // Email ID shUserForm.getRange(«C15»).clear(); //Department shUserForm.getRange(«C17»).clear();//Address //Assigning white as default background color shUserForm.getRange(«C4»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C7»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C9»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C11»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C13»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C15»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C17»).setBackground(‘#FFFFFF’); return true ;
} } |
19. Cree una función para validar la entrada de datos realizada por los usuarios. Utilizaremos esta función al transferir datos del formulario de usuario a la hoja de la base de datos.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
| //Declare a function to validate the entry made by user in UserForm function validateEntry(){ var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm = myGooglSheet.getSheetByName(«User Form»); //delcare a variable and set with the User Form worksheet //to create the instance of the user–interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); //Assigning white as default background color shUserForm.getRange(«C7»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C9»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C11»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C13»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C15»).setBackground(‘#FFFFFF’); shUserForm.getRange(«C17»).setBackground(‘#FFFFFF’);
//Validating Employee ID if(shUserForm.getRange(«C7»).isBlank()==true){ ui.alert(«Please enter Employee ID.»); shUserForm.getRange(«C7»).activate(); shUserForm.getRange(«C7»).setBackground(‘#FF0000’); return false; } //Validating Employee Name else if(shUserForm.getRange(«C9»).isBlank()==true){ ui.alert(«Please enter Employee Name.»); shUserForm.getRange(«C9»).activate(); shUserForm.getRange(«C9»).setBackground(‘#FF0000’); return false; } //Validating Gender else if(shUserForm.getRange(«C11»).isBlank()==true){ ui.alert(«Please select Gender from the drop-down.»); shUserForm.getRange(«C11»).activate(); shUserForm.getRange(«C11»).setBackground(‘#FF0000’); return false; } //Validating Email ID else if(shUserForm.getRange(«C13»).isBlank()==true){ ui.alert(«Please enter a valid Email ID.»); shUserForm.getRange(«C13»).activate(); shUserForm.getRange(«C13»).setBackground(‘#FF0000’); return false; } //Validating Department else if(shUserForm.getRange(«C15»).isBlank()==true){ ui.alert(«Please select Department from the drop-down.»); shUserForm.getRange(«C15»).activate(); shUserForm.getRange(«C15»).setBackground(‘#FF0000’); return false; } //Validating Address else if(shUserForm.getRange(«C17»).isBlank()==true){ ui.alert(«Please enter address.»); shUserForm.getRange(«C17»).activate(); shUserForm.getRange(«C17»).setBackground(‘#FF0000’); return false; } return true;
} |
20. Cree una función para transferir los datos del formulario de usuario a la hoja de la base de datos.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
| // Function to submit the data to Database sheet function submitData() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm= myGooglSheet.getSheetByName(«User Form»); //delcare a variable and set with the User Form worksheet var datasheet = myGooglSheet.getSheetByName(«Database»); ////delcare a variable and set with the Database worksheet //to create the instance of the user–interface environment to use the messagebox features var ui = SpreadsheetApp.getUi();
// Display a dialog box with a title, message, and «Yes» and «No» buttons. The user can also // close the dialog by clicking the close button in its title bar. var response = ui.alert(«Submit», ‘Do you want to submit the data?’,ui.ButtonSet.YES_NO); // Checking the user response and proceed with clearing the form if user selects Yes if (response == ui.Button.NO) {return;//exit from this function } //Validating the entry. If validation is true then proceed with transferring the data to Database sheet if (validateEntry()==true) {
var blankRow=datasheet.getLastRow()+1; //identify the next blank row datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange(«C7»).getValue()); //Employee ID datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange(«C9»).getValue()); //Employee Name datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange(«C11»).getValue()); //Gender datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange(«C13»).getValue()); // Email ID datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange(«C15»).getValue()); //Department datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange(«C17»).getValue());// Address
// date function to update the current date and time as submittted on datasheet.getRange(blankRow, 7).setValue(new Date()).setNumberFormat(‘yyyy-mm-dd h:mm’); //Submitted On
//get the email address of the person running the script and update as Submitted By datasheet.getRange(blankRow, 8).setValue(Session.getActiveUser().getEmail()); //Submitted By
ui.alert(‘ «New Data Saved – Emp #’ + shUserForm.getRange(«C7»).getValue() +’ «‘);
//Clearnign the data from the Data Entry Form shUserForm.getRange(«C7»).clear(); shUserForm.getRange(«C9»).clear(); shUserForm.getRange(«C11»).clear(); shUserForm.getRange(«C13»).clear(); shUserForm.getRange(«C15»).clear(); shUserForm.getRange(«C17»).clear();
} } |
21. Escriba la siguiente función para buscar en un registro la entrada proporcionada por el usuario en la celda C4 en la hoja de formulario de usuario.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
| //Function to Search the record function searchRecord() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm= myGooglSheet.getSheetByName(«User Form»); //delcare a variable and set with the User Form worksheet var datasheet = myGooglSheet.getSheetByName(«Database»); ////delcare a variable and set with the Database worksheet
var str = shUserForm.getRange(«C4»).getValue(); var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable var valuesFound=false; //variable to store boolean value
for (var i = 0; i < values.length; i++) { var rowValue = values[i]; //declaraing a variable and storing the value
//checking the first value of the record is equal to search item if (rowValue[0] == str) {
shUserForm.getRange(«C7»).setValue(rowValue[0]) ; shUserForm.getRange(«C9»).setValue(rowValue[1]); shUserForm.getRange(«C11»).setValue(rowValue[2]); shUserForm.getRange(«C13»).setValue(rowValue[3]); shUserForm.getRange(«C15»).setValue(rowValue[4]); shUserForm.getRange(«C17»).setValue(rowValue[5]); return; //come out from the search function
} } if(valuesFound==false){ //to create the instance of the user–interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); ui.alert(«No record found!»); } } |
22. Cree una función para eliminar el registro de la identificación de empleado disponible en la celda C4.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
| //Function to delete the record function deleteRow() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm= myGooglSheet.getSheetByName(«User Form»); //delcare a variable and set with the User Form worksheet var datasheet = myGooglSheet.getSheetByName(«Database»); ////delcare a variable and set with the Database worksheet //to create the instance of the user–interface environment to use the messagebox features var ui = SpreadsheetApp.getUi();
// Display a dialog box with a title, message, and «Yes» and «No» buttons. The user can also // close the dialog by clicking the close button in its title bar. var response = ui.alert(«Submit», ‘Do you want to delete the record?’,ui.ButtonSet.YES_NO); // Checking the user response and proceed with clearing the form if user selects Yes if (response == ui.Button.NO) {return;//exit from this function }
var str = shUserForm.getRange(«C4»).getValue(); var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable
var valuesFound=false; //variable to store boolean value to validate whether values found or not
for (var i = 0; i < values.length; i++) { var rowValue = values[i]; //declaraing a variable and storing the value
//checking the first value of the record is equal to search item if (rowValue[0] == str) {
var iRow = i+1; //identify the row number datasheet.deleteRow(iRow) ; //deleting the row //message to confirm the action ui.alert(‘ «Record deleted for Emp #’ + shUserForm.getRange(«C4»).getValue() +’ «‘); //Clearing the user form shUserForm.getRange(«C4»).clear() ; shUserForm.getRange(«C7»).clear() ; shUserForm.getRange(«C9»).clear() ; shUserForm.getRange(«C11»).clear() ; shUserForm.getRange(«C13»).clear() ; shUserForm.getRange(«C15»).clear() ; shUserForm.getRange(«C17»).clear() ; valuesFound=true; return; //come out from the search function } } if(valuesFound==false){ //to create the instance of the user–interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); ui.alert(«No record found!»); } } |
23. Escriba la siguiente función para editar los datos existentes para la identificación de empleado proporcionada en la celda C4.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
| //Function to edit the record function editRecord() {
var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm= myGooglSheet.getSheetByName(«User Form»); //delcare a variable and set with the User Form worksheet var datasheet = myGooglSheet.getSheetByName(«Database»); ////delcare a variable and set with the Database worksheet //to create the instance of the user–interface environment to use the messagebox features var ui = SpreadsheetApp.getUi();
// Display a dialog box with a title, message, and «Yes» and «No» buttons. The user can also // close the dialog by clicking the close button in its title bar. var response = ui.alert(«Submit», ‘Do you want to edit the data?’,ui.ButtonSet.YES_NO); // Checking the user response and proceed with clearing the form if user selects Yes if (response == ui.Button.NO) {return;//exit from this function }
var str = shUserForm.getRange(«C4»).getValue(); var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable
var valuesFound=false; //variable to store boolean value to validate whether values found or not
for (var i = 0; i < values.length; i++) { var rowValue = values[i]; //declaraing a variable and storing the value
//checking the first value of the record is equal to search item if (rowValue[0] == str) {
var iRow = i+1; //identify the row number datasheet.getRange(iRow, 1).setValue(shUserForm.getRange(«C7»).getValue()); //Employee ID datasheet.getRange(iRow, 2).setValue(shUserForm.getRange(«C9»).getValue()); //Employee Name datasheet.getRange(iRow, 3).setValue(shUserForm.getRange(«C11»).getValue()); //Gender datasheet.getRange(iRow, 4).setValue(shUserForm.getRange(«C13»).getValue()); // Email ID datasheet.getRange(iRow, 5).setValue(shUserForm.getRange(«C15»).getValue()); //Department datasheet.getRange(iRow, 6).setValue(shUserForm.getRange(«C17»).getValue());// Address
// date function to update the current date and time as submittted on datasheet.getRange(iRow, 7).setValue(new Date()).setNumberFormat(‘yyyy-mm-dd h:mm’); //Submitted On
//get the email address of the person running the script and update as Submitted By datasheet.getRange(iRow, 8).setValue(Session.getActiveUser().getEmail()); //Submitted By
ui.alert(‘ «Data updated for – Emp #’ + shUserForm.getRange(«C7»).getValue() +’ «‘);
//Clearnign the data from the Data Entry Form shUserForm.getRange(«C4»).clear(); shUserForm.getRange(«C7»).clear(); shUserForm.getRange(«C9»).clear(); shUserForm.getRange(«C11»).clear(); shUserForm.getRange(«C13»).clear(); shUserForm.getRange(«C15»).clear(); shUserForm.getRange(«C17»).clear(); valuesFound=true; return; //come out from the search function } } if(valuesFound==false){ //to create the instance of the user–interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); ui.alert(«No record found!»); } } |
Ahora, hemos terminado de escribir las funciones que se requieren para Restablecer el formulario, Validar las entradas realizadas por los usuarios, transferir los datos del formulario de Usuario a la hoja de Base de datos, editar y eliminar los registros existentes. Asignemos todas estas funciones a los respectivos botones disponibles en la hoja de formulario de usuario.
24. Hagamos clic derecho en el botón Buscar, luego hagamos clic en puntos suspensivos (puntos triples) disponibles en el lado derecho del botón y luego hagamos clic en ‘Asignar script’
25. En la ventana ‘Asignar script’, simplemente ingrese el nombre de la función y luego haga clic en el botón Aceptar.
26. Siga los pasos 24 y 26 para asignar las funciones de Apps Script (por ejemplo, submitData, editRecord, deleteRow y clearForm) a los botones respectivos (por ejemplo, Guardar, Modificar, Eliminar y Borrar).
Por lo tanto, hemos terminado con el desarrollo de un formulario de entrada de datos automatizado en Google Sheet con la ayuda de Apps Script.
Mire el tutorial paso a paso en YouTube.
Puede seguir lo siguiente para abrir el formulario de usuario y ver la codificación.
https://docs.google.com/spreadsheets/d/1UboTdtkC_rnwhyY29b01ML2_VEK2BZ2kMpU9VugHcW8/edit?usp=sharing
Comparta sus comentarios y preguntas en la sección de comentarios. ¡Gracias!