Implementation of an MQTT Server and Automatic Google Sheets Update.

Introduction

In this article, we will document the process of developing an MQTT server that connects to a specific topic (sismico/status/#) and automatically updates a Google Sheets spreadsheet with the received data. Below is a list of all the versions of the code developed during this process.

Summary of Automation with Node.js and Google Sheets

Project Description: The objective of this project was to create an automation system to update a Google Sheets spreadsheet in real-time using data received from an MQTT server. This automation was designed to handle data regarding the status and devices of a seismic system.

Implementation: From the start of training, the process took approximately four weeks. This time included initial setup, testing, debugging, and adjustments to ensure smooth integration between Node.js, MQTT, and Google Sheets. Although this is in a testing phase and is currently local (not on a server), this service can easily be migrated to a permanent connection, either in the cloud or on a dedicated server.

Process Overview:

• Initial Setup:

• Reading MQTT Messages:

• Writing to Google Sheets:

• Updating Formulas:

Types of Connections Attempted:

• Using Node-RED:

• Node.js:

Benefits of Automation:

• Efficiency: Automatic updates to Google Sheets in real-time eliminate the need for manual intervention, saving time and reducing errors.

• Reliability: Duplicate verification and formula updates ensure data accuracy and up-to-date information.

• Scalability: The solution can easily be adapted to handle more data and different MQTT topics as needed.

• Flexibility: Implementation with Node.js allows customization and extension of functionality according to project requirements.

Conclusion

The implementation of this automation proved to be a challenging but rewarding process. Throughout the project, we explored different approaches to find the most efficient and effective solution. In the end, we achieved a robust and scalable integration that meets the requirements of the seismic system, demonstrating the power of combining Node.js with Google Sheets and MQTT.

Implementation Versions (Draft Available for Your Own Implementation)

Version 1 - Basic Connection and Subscription to MQTT Topic:

Version 2 - Secure Connection with Basic Authentication:

Version 3 - Integration with Google Sheets:

Version 4 - Complete Integration with Google Sheets:

Version 5 - Existing Data Verification and Update:

Final Version - Adding XLOOKUP Formula and Comprehensive Data Handling:

GlideApp Implementation

Description of GlideApp: GlideApp is a tool that allows converting Google Sheets spreadsheets into mobile applications. In this project, we implemented GlideApp to provide an accessible and user-friendly interface for monitoring the status of seismic devices.

GlideApp Functionality:

• Periodic Monitoring: GlideApp does not offer real-time updates but allows users to periodically check the status of the equipment. The application reflects the updated data from Google Sheets, facilitating the monitoring of connectivity and device performance.

• Time Difference: Although GlideApp does not update in real-time, the information in Google Sheets is updated every minute. This creates a time difference of between 3 and 8 minutes in the data display in the application, which is suitable for periodic monitoring.

• User-Friendly Interface: GlideApp provides an intuitive and easy-to-navigate interface, allowing users to view the status of devices, alarms, and other relevant data.

Process of Configuration:

• Integration with Google Sheets: The spreadsheet used for the project was integrated with GlideApp, configuring the necessary columns to reflect relevant data.

• Application Design: Customized views were designed in GlideApp to display the status of devices, including visual indicators for connectivity and alerts.

• Functionality Testing: Tests were conducted to ensure that the information was displayed correctly and that updates were reflected in the application within the expected time frame.

Benefits of Using GlideApp

• Easy Access: Users can access information from any mobile device, facilitating field monitoring.

• Visibility: It allows users to have a consolidated view of the status of the equipment, aiding in decision-making and corrective actions.

• Flexibility: GlideApp is highly customizable, allowing it to be tailored to the specific needs of the project.

Importance of a Debug and Testing System

Debug System: Implementing a debug system is crucial for quickly identifying and resolving issues. In this project, the following debug strategies were implemented:

• Detailed Logs: Capturing comprehensive logs to track the flow and pinpoint errors.

• Error Handling: Implementing robust error handling to manage unexpected situations and failures.

Testing System: To ensure the robustness of the system, an extensive testing system was developed:

• Unit Tests: Unit tests were conducted to verify the functionality of individual parts of the code, such as the connection to MQTT and writing to Google Sheets.

• Integration Tests: Integration tests were performed to ensure that all components worked together seamlessly.

• Field Tests: Real-world tests were conducted to validate the system's performance and reliability.

Debug Document: A detailed debug document was created to guide the problem-solving process:

• Problem Description: Each identified problem was documented with a detailed description.

• Steps to Reproduce: Steps necessary to reproduce the problem were included, facilitating its identification.

• Implemented Solutions: The applied solutions were documented, including any changes to the code or configuration.

Conclusion

The implementation of this automation demonstrates the importance of combining technologies such as Node.js, MQTT, and Google Sheets to create effective and efficient solutions. Additionally, the use of Glide adds an additional level of accessibility and ease of use. Finally, a robust debug and testing system is essential to ensure the reliability and success of any software project.