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

Answer 1

I understand that you have a Sheet loaded with columns and values, and you want to insert them into an existing MySQL database. I see that you already scripted the connection to the database, so you are very close to reaching your goal.

First of all you would need to subtract the data from the Sheets. To manage that you could use Range.getValues() and save the information in one variable. Now the target is to create a SQL statement that inserts the desired values into the table. It may look similar to this one:

INSERT INTO tableName (column1, column2, column3, column4, column5, column6, column7) VALUES ('column1Value', 'column2Value', 'column3Value', 'column4Value', 'column5Value', 'column6Value', 'column7Value');

Keeping that structure on mind you can easily build your own statement by substituting the placeholders and updating the values to include the array of previously saved data. Then, you would only need to send that statement to the database. To do so you could use JdbcConnection.createStatement() and JdbcStatement.execute(). Please, ask me any additional doubts to better document this approach.