Google Script: Define Variable from Library

Question

I have a Google script set up to grab data from a mySQL database. The script is set up by first defining variables for access credentials. Ex:

var host = '123.45.67.89';
var user = 'username';
var password = 'password';
var port = '3306';

My issue: These credentials could change. If they do change, I would have to change them in every script, in every spreadsheet that uses them.

I think the solution is to set up a script to use as a library, whose sole purpose is to define these variables. So, all this script will be is:

function setCredentials() {
    var host = '123.45.67.89';
    var user = 'username';
    var password = 'password';
    var port = '3306';
}

And once that is set up as a library, saved as "DBCredentials," I would set up each SQL grabbing script to simply call those variables from that library. But I can't get it to work. I'm sure I'm just missing the correct way to call a variable from a library, but I haven't had any luck finding the answer using Google.

I did find this: Defining globals in Google Apps Script that can be used across multiple projects

According to this, I would call the variable by using:

var host = SpreadsheetApp.openById( DBCredentials.host );

But when I do that, I receive a "Bad value" error. I also tried:

var user = DBCredentials.user;

But that doesn't work, either. The script will run, but checking the access logs it shows that a user named "undefined" tried to access the database.

What's the correct way to call a variable from a library script?

You are assuming the name of the app script, and the identifier, are different. They are not. I'm fully aware that you can use whatever identifier you want to call functions from a library. I have set the identifier to be the same as the script's name; DBCredentials. To test, I have tried changing the identifier for the library to something else, and updating the script to call it. It still does not work. — RL85
– RL85, Commented Aug 24, 2018 at 22:45
Libraries don't export properties AFAIK. Call a library method, i.e. DBCredentials.getUser() — tehhowch
– tehhowch, Commented Aug 24, 2018 at 22:57
A GLOBAL can be set from inside of a function, but you can't use var inside the function or the variable will be scoped to the inside of the function. So, declare your global's outside of any function, and if you want to set the value of the globals from inside of a function, then don't use var — Alan Wells
– Alan Wells, Commented Aug 24, 2018 at 23:41

RL85 · Accepted Answer · 2018-08-25 03:59:52Z

Sandy's second comment led me to the answer:

A GLOBAL can be set from inside of a function, but you can't use var inside the function or the variable will be scoped to the inside of the function. So, declare your global's outside of any function, and if you want to set the value of the globals from inside of a function, then don't use var

My mistake was setting in up my library script file, the one with credentials. I put the var with the set credentials (host, user, password, port) within a function, like so:

function getCredentials() {
var host = '123.45.67.89';
var user = 'username';
var password = 'password';
var port = '3306';
}

This was where I was incorrect. I'm rather new to this, so I assumed ALL scripts had to have a function in order to operate. Putting the variables inside a function made those variables scoped only to that function, which is why they did not transfer OUT when the other script called those variables.

I simply removed the function around the variables, changing the library script to only this and nothing more:

  var host = '123.45.67.89';
  var user = 'user';
  var password = 'password';
  var port = '3306';

I then was able to call those variables from the container-bound script (one tied to a spreadsheet) using the following:

  var host = DBCredentials.host;
  var user = DBCredentials.user;
  var password = DBCredentials.password;
  var port = DBCredentials.port;

(DBCredentials being the identifier chosen for the library script.) It now works perfectly. Thank you very much for your help!

Collectives™ on Stack Overflow

Google Script: Define Variable from Library

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related