Hello! I have written the same code with my password, username and connection name but the code is not working Error -- Exception: Failed to establish a database connection. Check connection string, username and password. Checked the input information many times
Is there a way to set environment variables so that the raw password is not contained in the script? I'm concerned about having plaintext credentials in a script that my sheet users will have access to
Hi Bitmaximus! Great foresight, that's a typical concern for a lot of Apps Script programmers. One solution to this problem is to use the PropertiesService to store your password (developers.google.com/apps-script/guides/properties). The PropertiesService provides three different scopes for your variables (script-level, document-level, and user-level) but it seems like you may want to use the scriptProperties scope. Just save your credentials there, and then whenever you need them to connect to the database, you can access them using code similar to this: const password = PropertiesService.getScriptProperties().getProperty("password"); Here's a good video outlining how to use the propertiesService: th-cam.com/video/TremiRingVg/w-d-xo.html I hope this answered your question. If you need more support, please don't hesitate to reply back!
Hello David, Thanks for this very informative series of episodes. Somehow, calling my table with 700rows X 7columns ("SELECT * FROM people") : - takes a very long time (up to 30s) - weirdly the same call, exactly, takes sometimes 3s and sometimes 30s Is someone experimenting with this? Were you able to solve the problem? Thanks a lot, L
Hi Laury, I am having the exact same issue (range between 3-25 seconds). Have you been able to workout what the issue is? David, much like David if you are reading this I would love to understand if you have experienced the same issue.
Hello @@klaroapp, Unfortunately not, somehow App Script looks very unstable. I changed my pipeline and used a flask app on App Engine connected to BigQuery. If you have a GCP account I would highly recommend to do so. Sorry not to be able to help you more :/ Cheers !
@@LauryFYOT Fair enough, thanks for such a quick reply. I have been able to connect with a google representative via support to get some assistance. My end goal is to publish a google sheet add-on so unfortunately I do not have much choice other than apps script...you'd think they would make it more performant if they wanted people to create add-ons you know! (and I am using google cloud too!) But yeah let's see, if I am able to crack the nut I will let you know. Sounds like you've moved on anyhow but really appreciate you taking the time to reply.
@@LauryFYOT Update! I know you moved on as well but just FYI I thought I'd share an update from my end, hopefully this can be helpful to anyone else also reading this. Working with the google support team the JDBC connection couldn't be improved materially. It took 2-4 seconds to just establish a connection and google apps script simply didn't allow for any efficient ways of storing the JDBC results into an array. According to the Google support team it seems to just be an inherent limitation. I ended up switching to using google sheet as a database and by way of comparison it averages ~1.00 second. Previously, this would take between 30-120 seconds. My dataset was ~2000 rows with about 8 columns, mostly text, ~90-100KB size-wise. Best of luck with your projects/business!
Thanks. It really helped me today.
thank you for your amazing tutorials!!
Hello!
I have written the same code with my password, username and connection name but the code is not working
Error --
Exception: Failed to establish a database connection. Check connection string, username and password.
Checked the input information many times
You must make sure that the Google SQL account and the Google Apps Scripts account are the same before you can connect.
Hi! Does have any cost?
Hi David, great videos. Can you make one video for BigQuery connection with App Script :)
Is there a way to set environment variables so that the raw password is not contained in the script?
I'm concerned about having plaintext credentials in a script that my sheet users will have access to
Hi Bitmaximus! Great foresight, that's a typical concern for a lot of Apps Script programmers. One solution to this problem is to use the PropertiesService to store your password (developers.google.com/apps-script/guides/properties). The PropertiesService provides three different scopes for your variables (script-level, document-level, and user-level) but it seems like you may want to use the scriptProperties scope. Just save your credentials there, and then whenever you need them to connect to the database, you can access them using code similar to this:
const password = PropertiesService.getScriptProperties().getProperty("password");
Here's a good video outlining how to use the propertiesService: th-cam.com/video/TremiRingVg/w-d-xo.html
I hope this answered your question. If you need more support, please don't hesitate to reply back!
yes, you can create a function to encript that password and to decript it when password is accesed, without public eye to see.
Hello David,
Thanks for this very informative series of episodes.
Somehow, calling my table with 700rows X 7columns ("SELECT * FROM people") :
- takes a very long time (up to 30s)
- weirdly the same call, exactly, takes sometimes 3s and sometimes 30s
Is someone experimenting with this? Were you able to solve the problem?
Thanks a lot,
L
Hi Laury, I am having the exact same issue (range between 3-25 seconds). Have you been able to workout what the issue is?
David, much like David if you are reading this I would love to understand if you have experienced the same issue.
Hello @@klaroapp,
Unfortunately not, somehow App Script looks very unstable.
I changed my pipeline and used a flask app on App Engine connected to BigQuery. If you have a GCP account I would highly recommend to do so.
Sorry not to be able to help you more :/
Cheers !
@@LauryFYOT Fair enough, thanks for such a quick reply. I have been able to connect with a google representative via support to get some assistance. My end goal is to publish a google sheet add-on so unfortunately I do not have much choice other than apps script...you'd think they would make it more performant if they wanted people to create add-ons you know! (and I am using google cloud too!) But yeah let's see, if I am able to crack the nut I will let you know. Sounds like you've moved on anyhow but really appreciate you taking the time to reply.
@@LauryFYOT Update! I know you moved on as well but just FYI I thought I'd share an update from my end, hopefully this can be helpful to anyone else also reading this.
Working with the google support team the JDBC connection couldn't be improved materially. It took 2-4 seconds to just establish a connection and google apps script simply didn't allow for any efficient ways of storing the JDBC results into an array. According to the Google support team it seems to just be an inherent limitation.
I ended up switching to using google sheet as a database and by way of comparison it averages ~1.00 second. Previously, this would take between 30-120 seconds. My dataset was ~2000 rows with about 8 columns, mostly text, ~90-100KB size-wise.
Best of luck with your projects/business!
Nice video, but will still store my sheet informations