[MDBF-275] Connect Google Apps Script with local MariaDB instance Created: 2021-09-28  Updated: 2023-12-02

Status: Closed
Project: MariaDB Foundation Development
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Anel Husakovic Assignee: Anel Husakovic
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Windows

Attachments: PNG File screenshot-1.png    

 Description   

Google Apps Script https://script.google.com/home/ have built-in JDBC connector used to connect to MySQL. There are couple of tutorials out there showing how to do it on some free hosting MySQL databases.
I want to test and allow to connect to my local database, because there are no tutorials MariaDB and Google Apps what could be great topic for the blog.
My work is done on Windows machine with 10.7 MariaDB.

  • 1) I tested my port forwarding, where the port 3306 of the public IP is forwarded to my local IP address (this settings is done on router).
  • 2) In mariadb server bind_address is set to 0.0.0.0
  • 3) Tested with telnet -> telnet <publicIP> 3306 works
  • 4) Created user <user>at<publicIP> and granted all
  • 5) Before we go to the Apps Script, we can check connection in Google Data studio https://datastudio.google.com/ with Basic or JDBC connector.
    In my case, this step doesn't work.

 Comments   
Comment by Anel Husakovic [ 2021-09-28 ]

Looking the Notes paragraph from Connect to MySQL article seems that firewall should be allowed for some IPs.

Have tested port forwarding with: https://pentest-tools.com/network-vulnerability-scanning/tcp-port-scanner-online-nmap
and it founds 3306 port is open, so firewall is off.

What leads me to the conclusion that MariaDB is not supported by Google Apps Scripts.

Comment by Anel Husakovic [ 2023-11-13 ]

Update: Found blog that should work with JDBC, have to test it.

Comment by Anel Husakovic [ 2023-12-02 ]

The blog above speaks about using JDBC driver to connect to ODBC driver (I guess through ODBC REST if something like this exist?)
Still not possible via mysql native protocol JDBC, supported only for MySQL, from Google documentation

Apps Script can connect to external databases through the JDBC service, a wrapper around the standard Java Database Connectivity technology. The JDBC service supports Google Cloud SQL for MySQL, MySQL, Microsoft SQL Server, and Oracle databases.

There is feature request for JDBC support for Postgresql
One should write Restful api and connect from script to API.
Postgresql has its own postgrest
We have question about this issue too on KB
There are some alternatives with Pandas and SeetsAPI.
Another possibility is to make own endpoints of database,expose them on some port and mke request from spreadsheet directly t those endpoints.

Generated at Thu Feb 08 03:36:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.