Is it possible to have a google sheet retrieve data from a windows ODBC data source without using any 3rd party commercial software?

Saood Khan

unread,

Oct 14, 2021, 10:26:18 AM10/14/21

to Google Apps Script Community

Is there any way of perhaps doing this through a Google Apps Script that connects to a windows ODBC data source?

Due to network restrictions we are not able to connect directly to a SQL database. The problem with this is that we would need to open firewall ports so that Google Sheet in the cloud can access the SQL server that is behind our firewall, creating a cyber risk. I would like to make a connection from my local browser to the Windows ODBC data source and then to the SQL server. This would mean that if I want to retrieve data, I must be in our network, behind our firewall, but that is not a problem because we can VPN into the network and then retrieve the data.

Saood Khan

Systems Administrator

RSA: +27 21 100 3774

UK: +44 207 493 6741

US: +1 415 795 7699

Email: sk...@groveis.com

Web: www.groveis.com

If you no longer wish to receive emails from this sender please contact pri...@groveis.com

Confidentiality Notice. This email contains Privileged and/or Confidential Information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should delete this message and kindly notify the sender by reply email. Please advise immediately if you or your employer does not consent to email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Grove shall be understood as neither given nor endorsed by it. This mail is sent from Grove.

CBMServices Web

unread,

Oct 14, 2021, 1:42:44 PM10/14/21

to google-apps-sc...@googlegroups.com

Hi Saood,

AppsScript as far as I know does not have a native connector for ODBC. You may be able to find one in the open source libraries in GIT. However based on your security restrictions, this may be moot if you do not permit it to communicate through the firewall.

If you want to use your local browser to interact with the ODBC then you are basically writing your own ODBC connector in Javascript to run on your browser and then passing any data back to Google Spreadsheet.

So either way, I suspect your situation will be difficult to resolve cleanly due to the security issues.

--
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/f19676e6-9efd-4412-a167-8ca2875c6efdn%40googlegroups.com.

1.png

0.png

2.png

3.gif

Clark Lind

unread,

Oct 14, 2021, 4:52:24 PM10/14/21

to Google Apps Script Community

Theoretically, it should be possible. You may have complications because apps script is run within an Iframe. So you may have to open the web app, which in-turn opens a new window where you can do the work. Once done, the window passes the returned data back to the web app, which sends it on the the sheet. Again, purely theoretical.
Another method is, once the second browser window is open, you could just use the sheets.api to post the data to a sheet. In fact, using this method, you could have more flexibility running it in node.js (or local server) and just post to sheets using the sheets.api.