CODEWITHSUNDEEP
google-apps-script
RL85
RL85

Reputation: 53

Google Script: Define Variable from Library

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?

Upvotes: 3

Views: 1294

Answers (1)

RL85
RL85

Reputation: 53

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!

Upvotes: 2

Related Questions