CODEWITHSUNDEEP
mysqlgoogle-apps-scriptjdbc
LeroyFromBerlin
LeroyFromBerlin

Reputation: 395

automated data upload from google doc to MySQL database - how to hide the database credentials in the google script

I have a repetitive task of uploading data from a google doc into a MySQL database. Its manual right now and I'd like to automate it..

For pulling data from the database into a google doc I usually use the below and then paste the data into one of the sheets - works perfectly fine.

Jdbc.getConnection('jdbc:mysql://host:port/', user, password);
var stmt = conn.createStatement();
stmt.setMaxRows(1000);
var start = new Date();
var rs = stmt.executeQuery(exq);

According to the documentation by Google I can use JDBC for doing it the other way around too. Have not tried it yet but should work perfectly fine as well.

https://developers.google.com/apps-script/guides/jdbc

Here is my problem, though: everybody can open the script editor and see the code (including the credentials).

For pulling from the db that is ok because the user will only have reading rights and I'm not too secretive about these within the company. But for pushing data into the db the user must have writing rights and therefor I obviously can't share them..

Can you think of a smart way of hiding the script so that the creds can't be seen? Any other way of doing this would also be highly appreciated.

Upvotes: 0

Views: 74

Answers (1)

tehhowch
tehhowch

Reputation: 9872

Anyone with edit access to the Google Sheet / Doc / Slide will always have view and edit access to all its bound script projects.

To hide credentials, you have a few options:

  1. Use PropertiesService to set up some user-specific properties for yourself (and then remove the one-off function you wrote to populate the credentials into your user properties before a new script version is created or someone else opens the script editor).
  2. Create a standalone script that uses openById to access the spreadsheet with data to upload (and change the ID whenever necessary). Do not share this standalone script with anyone who should not have access to the credentials.
  3. create a privately published GSuite add-on that performs some identity checks before uploading, but otherwise allows reading, and install this add-on into the necessary spreadsheets.

Upvotes: 3

Related Questions