mysql - how to auto update SQl database with google sheets data

Question

I have a google sheet that has data on it. I have a MySQL database that will be an exact copy of the Google Sheets data. I want to make it so when they save the page it will auto update a MySQL database with the new data that the website will pull data from. Here is my script so far. I can connect to the database but can't see how to update it with the sheets data automatically.

var server = 'server ip';
var dbName = 'db name';
var username = 'user';
var password = 'pass';
var port = '3306';

function CreateConnection() {
  var url = "jdbc:mysql://" + server + ":" + port + "/" + dbName;
  var conn = Jdbc.getConnection(url, username, password);

  conn.close();
}

Here are my columns:

1. ID (auto increments)
2. CUs No
3. Name
4. Phone
5. License No.
6. Status 1-5
7. Last Update

question from:https://stackoverflow.com/questions/65891353/how-to-auto-update-sql-database-with-google-sheets-data

Answer 1

Automatically updating db based on entire sheet might not be a good idea, because of manual errors while filling the sheet. So instead I would suggest go with a form based approach where on form submit you have a trigger that runs a function to insert that new entry into the database. Benefit of using form is that prevents human error. And if not that way then you could keep a flag column and based on its value you can run the ones that haven't been already processed into db using a time based trigger which runs say every 10 mins. All this is important to prevent duplicate entries in the db, and to avoid manual errors.