GMail sheet to MySQL connector

We have a MySQL database in the cloud and wish to use the GMail sheet interface as available in Excel to this database. Is there an add-on to do this ? alternatively, Is there any database that Google sheets connects to that we can store our data in?
shaunwinginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
In Tools, Editor Script, write down the code below. Please observe the changes in the IP addresses, database names, etc that will be needed to make it work:
function DriveDashboard() {

// Replace the variables in this block with real values.
var ServerIP = '168.23.52.81';
var SQL_Port = '3306';
var SQL_Usr = 'GDriveUser';
var SQL_Pwd = 'reallyshitpassword';
var SQL_DB = 'world';

var connectorInstance = 'jdbc:mysql://' + ServerIP+':'+SQL_Port;
var ConnectString = connectorInstance+'/'+SQL_DB;

var conn = Jdbc.getConnection(ConnectString, SQL_Usr, SQL_Pwd);

}

//Executes the queries
var execStmt = conn.createStatement();

//Gets and Sets the Current Spreadsheet as Active
var doc = SpreadsheetApp.getActiveSpreadsheet();
doc.setActiveSheet(doc.getSheetByName('Sheet1'));

/* Count all records in the City Table */
var mysqlQuery = execStmt.executeQuery("select COUNT(*) From `City`");
var docRange = doc.getRange('A2'); //assign the value to cell A1
while(mysqlQuery.next()) {
docRange.setValue(mysqlQuery.getString(1));
}

// if you expect to ouput more than one record use this method
var mysqlQuery = execStmt.executeQuery("select Name, Language From CountryLanguage, Country where countrycode=code and language = 'Swedish'");
var cell = doc.getRange('D2');

// loop through result object, setting cell values to database data
var row = 0;
while(mysqlQuery.next()) {
for(var i=0; i<2; i++) { // 2 fields per record
cell.offset(row, i).setValue(mysqlQuery.getString(i+1));
}
row++;
}

// if you expect to ouput more than one record use this method
var mysqlQuery = execStmt.executeQuery("select Name, Language, Percentage From CountryLanguage, Country where countrycode=code ORDER BY Name, Percentage");
var cell = doc.getRange('G2');

// loop through result object, setting cell values to database data
var row = 0;
while(mysqlQuery.next()) {
for(var i=0; i<3; i++) { // 3 fields per record
cell.offset(row, i).setValue(mysqlQuery.getString(i+1));
}
row++;
}

mysqlQuery.close();
execStmt.close();
conn.close();

Open in new window


Original code can be found at: https://www.lassiemarlowe.com/tutorials/google-sheets-mysql-dashboard/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shaunwinginAuthor Commented:
This seems only in one direction - its not able to update / delete records in the MySQL. Is this available?
0
Walter RitzelSenior Software EngineerCommented:
The example was coded in only one direction. But using INSERT UPDATE or DELETE commands on the queries would allow you to do whatever you want.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

shaunwinginAuthor Commented:
I'm looking for a live - realtime solution....
0
Walter RitzelSenior Software EngineerCommented:
In other words, you are looking for something that is ready to be used, right? Well, There is no such thing, because the technology available for that requires that you code what you need to be done. Google Spreadsheet are not so advanced as Excel in this case, it requires more development to do a similar thing.
0
shaunwinginAuthor Commented:
Tx. Perhaps can advise what tool or product I can use to allow for collaboration on the spreadsheets but still maintain data integrity?
0
Walter RitzelSenior Software EngineerCommented:
I font think there is such a product. You will need to implement it. But would be the same for Excel, só no news here.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Google Apps

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.