Como crear un FORMULARIO de Ingreso de datos en GOOGLE SHEETS (Macros) - El Tío Tech

Introduction

In this tutorial, we will learn how to create a data entry form in Google Sheets using macros. This method allows users to input data easily without any programming skills. By the end of this guide, you will have a fully functional form that saves data automatically while allowing multiple users to collaborate in real-time.

Chapter 1: Advantages of Creating a Form in Google Sheets

• Collaboration: Multiple users can access and enter data simultaneously from different locations.
• Automatic Updates: Changes made by any user are instantly reflected in the sheet.
• User-Friendly: No coding is required; all actions are accomplished through simple clicks.

Chapter 2: Setting Up Google Sheets

1. Open Google Sheets:

   • Go to Google Sheets in your browser.
   • Click on 'Blank' to create a new spreadsheet.

2. Name Your Spreadsheet:

   • Click on "Untitled spreadsheet" and rename it to something like "Data Entry Form 2022".

3. Design the Form Layout:

   • In the first row, create headers: Code, Product, Quantity, Price, Total, and Date.
   • Use borders to separate the header row from the data entry cells.

4. Adjust Cell Sizes:

   • Select rows 2 to 7, right-click, and choose "Resize row." Set the height to a comfortable size (e.g., 35).

5. Remove Gridlines:

   • Click on "View" in the menu and uncheck "Gridlines" to enhance the form's appearance.

Chapter 3: Creating the Form Title

1. Insert Drawing:
   • Click on "Insert" → "Drawing."
   • Create a rounded rectangle and label it "Product Registration Form."
   • Customize the font and colors as desired, then click "Save and Close."

Chapter 4: Adding Action Buttons

1. Create Save and Clear Buttons:

   • For the Save button, go to "Insert" → "Drawing," select a shape, and label it "Save."
   • Format the button with color and size adjustments.
   • Repeat the process for the Clear button.

2. Position the Buttons:

   • Place the buttons conveniently below the form fields.

Chapter 5: Setting Up the Data Storage Sheet

1. Create a New Sheet for Data:

   • Click on the "+" at the bottom left to add a new sheet.
   • Rename this sheet to "Data."

2. Set Up the Data Table:

   • Create the same headers (Code, Product, Quantity, Price, Total, Date) in the new sheet.

Chapter 6: Data Validation

1. Ensure Correct Data Entry:
   • Select the cells for Code, Quantity, Price, and Total.
   • Go to "Data" → "Data validation."
   • Set criteria to restrict inputs (e.g., only numbers for Quantity and Price).

Chapter 7: Formatting Currency

1. Apply Currency Format:
   • Select the Price and Total columns.
   • Go to "Format" → "Number" → "Currency" to ensure values are displayed in your local currency.

Chapter 8: Creating the Macro

1. Record the Save Macro:

   • Go to "Extensions" → "Macros" → "Record Macro."
   • Choose "Absolute references" and start recording.
   • Perform the following actions:
     • Insert a new row at the top of the data table.
     • Copy data from the form fields and paste them into the corresponding cells in the Data sheet.
   • Save the macro with a name like "SaveData."

2. Assign Macro to Save Button:

   • Right-click on the Save button, select the three dots, and choose "Assign script."
   • Enter the name of the macro (e.g., SaveData) and click "OK."

Chapter 9: Authorization for Macros

• When you run the macro for the first time, you will need to authorize it.
• Follow the prompts to allow the macro to execute.

Chapter 10: Testing the Macro

1. Input Sample Data:
   • Fill in the form fields and click "Save."
   • Check the Data sheet to verify that the information is recorded correctly.

Chapter 11: Configuring the Clear Button

1. Record the Clear Macro:
   • Similar to the Save macro, start recording a new macro for clearing the form fields.
   • Save it with a name like "ClearForm."
   • Assign it to the Clear button.

Chapter 12: Adding Alerts

1. Display a Confirmation Message:
   • Edit the Save macro to include a message that confirms data has been saved.
   • Use the following code snippet:

   SpreadsheetApp.getUi().alert('Data saved correctly');
   

Chapter 13: Sharing the Form

1. Share the Spreadsheet:
   • Click on the "Share" button in the upper right corner.
   • Enter the email addresses of users you want to share the form with and set their permissions.

Conclusion

You have successfully created a data entry form in Google Sheets using macros. This form allows for efficient data input while maintaining accuracy and collaboration among users. You can further customize the form and macros as needed. Consider experimenting with additional features to enhance functionality and usability. Happy data entry!