https://drive.google.com/file/d/18NcHJFeRSfQ9IOGx4iLDHjqH73XrxyQF/view?usp=sharing
Build a solution that enables real-time synchronization of data between a Google Sheet and a specified database (e.g., MySQL, PostgreSQL). The solution should detect changes in the Google Sheet and update the database accordingly, and vice versa.
Many businesses use Google Sheets for collaborative data management and databases for more robust and scalable data storage. However, keeping the data synchronised between Google Sheets and databases is often a manual and error-prone process. Your task is to develop a solution that automates this synchronisation, ensuring that changes in one are reflected in the other in real-time.
- Real-time Synchronisation
- Implement a system that detects changes in Google Sheets and updates the database accordingly.
- Similarly, detect changes in the database and update the Google Sheet.
- CRUD Operations
- Ensure the system supports Create, Read, Update, and Delete operations for both Google Sheets and the database.
- Maintain data consistency across both platforms.
- Conflict Handling
- Develop a strategy to handle conflicts that may arise when changes are made simultaneously in both Google Sheets and the database.
- Provide options for conflict resolution (e.g., last write wins, user-defined rules).
- Scalability:
- Ensure the solution can handle large datasets and high-frequency updates without performance degradation.
- Optimize for scalability and efficiency.
The timeline for this submission is: Next 2 days
Some things you might want to take care of:
- Make use of git and commit your steps!
- Use good coding practices.
- Write beautiful and readable code. Well-written code is nothing less than a work of art.
- Use semantic variable naming.
- Your code should be organized well in files and folders which is easy to figure out.
- If there is something happening in your code that is not very intuitive, add some comments.
- Add to this README at the bottom explaining your approach (brownie points 😋)
- Use ChatGPT4o/o1/Github Co-pilot, anything that accelerates how you work 💪🏽.
Make sure you finish the assignment a little earlier than this so you have time to make any final changes.
Once you're done, make sure you record a video showing your project working. The video should NOT be longer than 120 seconds. While you record the video, tell us about your biggest blocker, and how you overcame it! Don't be shy, talk us through, we'd love that.
We have a checklist at the bottom of this README file, which you should update as your progress with your assignment. It will help us evaluate your project.
- [] My code's working just fine! 🥳
- [] I have recorded a video showing it working and embedded it in the README
▶️ - [] I have tested all the normal working cases 😎
- [] I have even solved some edge cases (brownie points) 💪
- [] I added my very planned-out approach to the problem at the end of this README 📜
Feel free to check the discussions tab, you might get some help there. Check out that tab before reaching out to us. Also, did you know, the internet is a great place to explore? 😛
We're available at techhiring@superjoin.ai for all queries.
All the best ✨.
This project implements a real-time synchronization between Google Sheets and a MySQL database, leveraging Kafka for scalability and conflict resolution. The Flask server is hosted via Waitress and exposed to the internet using NGROK. Google Apps Script handles the detection of changes on Google Sheets, triggering the appropriate operations (Create, Read, Update, Delete) in real time.
- Flask Server: Built using Python's Flask framework, served via Waitress.
- NGROK: Used to expose the Flask server to the internet for handling requests from Google Sheets.
- Kafka: Handles message passing for scalable synchronization between the Google Sheets and MySQL database. It also resolves conflicts when concurrent updates occur.
- Google Apps Script: Detects changes in Google Sheets and triggers CRUD operations accordingly.
- MySQL Database: Stores the data synced from the Google Sheets.
- Real-Time Synchronization: The system ensures that any changes made in Google Sheets (adding rows, updating values, etc.) are reflected in the MySQL database instantly.
- Scalability: Kafka integration allows the system to scale horizontally, ensuring that multiple clients can interact with the database without performance degradation.
- Conflict Resolution: Internal conflict-handling mechanisms resolve issues arising from concurrent edits.
- CRUD Operations: Supports Create, Read, Update, and Delete operations seamlessly between Google Sheets and MySQL.
One major roadblock encountered was configuring Kafka, as it was my first time working with it. The rest of the functionalities were more straightforward since I had prior experience implementing similar operations using Google Apps Script and Python during my internship at EKCS.
Building this project was really fun and I learned a lot about Kafka in the process.
- Setup MySQL Database: Ensure you have a running MySQL instance.
- Flask Server: Start the Flask server using Waitress and expose it via NGROK.
- Google Sheets: Add the appropriate Apps Script to trigger updates on changes in Google Sheets.
- Kafka: Set up a Kafka broker and configure the consumer and producer in the Flask app.
- [YES] My code's working just fine! 🥳
- [YES] I have recorded a video showing it working and embedded it in the README
▶️ - [YES] I have tested all the normal working cases 😎
- [YES] I have even solved some edge cases (brownie points) 💪
- [YES] I added my very planned-out approach to the problem at the end of this README 📜
Aavish Gilbert J
Email: aavish.gilbert@gmail.com
PES University, PES1UG21CS012