Connect Apps script to Microsoft SQL Server Management

[Gus Fring]

Hello all,

I have a project where we are using google sheet as our temporary database. Now we have an database which is Microsoft SQL server management, I want to throw all the data to that database instead on the Google sheet. How can I connect my appscript to the said database? 

[Keith Andersen]

You would be better off exporting the mySQL database as a CSV file and then importing it into Google.

To safely and securely transfer data from mySQL database to Google Sheets you would have to do an SSH tunnel or SSL. SSL is a nightmare. 

On Wed, Oct 19, 2022, 11:52 AM Gus Fring <humble...@gmail.com> wrote:

Hello all,

I have a project where we are using google sheet as our temporary database. Now we have an database which is Microsoft SQL server management, I want to throw all the data to that database instead on the Google sheet. How can I connect my appscript to the said database? 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/334f6c6a-7d95-4aef-a037-d4849bd4263an%40googlegroups.com.

[Andrew Roberts]

Take a look at the JDBC service.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4x1J4177oos5pXsxyMKcZjTAOaAp808YB8w03tSO_yQWQ%40mail.gmail.com.

[Keith Andersen]

Andrew,

Through Google Cloud MYSQL instance the JDBC Google connect is secure (pseudo SSL). But...any other MYSQL connections to other remote databases is not secure with JDBC connect only. You would have to have a SSL connection or an SSH connection.

I have a MYSQL database through Siteground with SSL, however each time I tried to connect using a jdbc connector and passing the SSL certificates, the script editor in apps script would not accept the certificates saying that they had an unintended token. Research showed that the way the new certificates are made, one of the characters causes exceptions (error) in various applications.

Have you overcome this issue?

Keith

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAN1QPJT5WQTUYpuW1%2BJ_PDCSRTWFG%2B5jgsqc0nA1PCm%2BxKfjqA%40mail.gmail.com.

[Keith Andersen]

Gus,

How much data are you looking at? Before migrating keep in mind limitations on total cells in in any spreadsheet.

Keith

[Gus Fring]

Hello Keith,

I'm tranferring thousands of data. And then after that all the incoming entries from my web app automatically pppulate to my database? How can I achieve that? 

[Keith Andersen]

Gus,

Export each table as CSV.

Then Import each table into Google Spreadsheets as separate tabs.

Keith

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/038e157f-cdd1-4308-886c-ad569639ea0fn%40googlegroups.com.

[Gus Fring]

Hi Keith, 

But how will I connect my app script to databse so that when there's an update it will automatically go to our databse instead of the google sheet? 

[Keith Andersen]

Gus,

I mis-understood. You want your temporary "Google Sheet" database to be transfered to your MySQL database.

Make your MySQL database tables with the same structure as each tab in Google Sheets.

Then created a CSV file from each tab of your Google Sheets removing the header row.

Then through MySQL Workbench or PhpMyAdmin , import each tab CSV into your MySQL table.

Do you have sensitive data? If yes, do it through an SSL or SSH.

Read my response to Andrew - creating a secure connection to mySQL with Google spreadsheets is difficult if not almost impossible... Unless that Mysql database is housed in the Google Cloud Services.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/46c20e41-40e0-48d9-8ff2-30a4f7e064c1n%40googlegroups.com.