How to query my database and have it be outputted on a Google Sheet so that someone can edit this data and then have it reflected back in MySQL?

Question

I’d like to push some data from MySQL into Google Sheets. Once I’ve edited my data in Google Sheets, I’d like to push my edited data back into MySQL. Ideally, I’d even like to schedule it to update it every hour, so my data is always live and matches what's in my MySQL.

Please have in mind that it is for someone on my team who can’t figure out querying with SQL, has a hard time navigating MySQL, and that I don’t want to train in SQL. I would just like this person to edit Google Sheets and these edits to be reflected back in MySQL, without this person ever having to go into my SQL database.

I’ve looked into Google Sheets Script and it seems that it enables you to type in a SQL query into a cell in Google Sheets and retrieve your queried data. But it seems that, if you make changes to the queried data in Google Sheets, it will not be reflected in MySQL.

Any suggestion so that I can work with this person in my team who doesn’t know how to query with SQL?

Answer 1

This is not a complete solution, but could point you in the right direction:

1. Open an SQL terminal, then execute the SELECT you want and capture the output in a text file
2. Import the text file you captured into Google Sheets. I've not used import function with that software, but I have done something similar using Excel and Libre Office Calc before. The output is fairly uniform, and by choosing the correct separator character (usually ) you can get the data in columns. You'll still have to clean it up some, possibly delete some columns before sending the spreadsheet file to your colleague.
3. Once they've edited the data and returned the updated spreadsheet to you, you can reverse the process, creating a text file with a list of UPDATE/SET commands to alter the data in the relevant table(s)
4. Use the text file as a script in the SQL terminal window to update the SQL database.

I've used a similar process in the past on a regular basis with a customer database for a company I worked for. It's not completely automated, but it's flexible, and it sure beats hand-entry for large datasets. Possibly you could use Sheets' scripting functions to generate the update script automatically from the data in the spreadsheet.