In this article we will show how to connect google sheets to a database in a few simple steps with apps script.
Connect Google Sheets to a Database (Apps script Method)
1. Get Access Credentials to the Database
First, you need to have access to the database. You need the following information:
- server_url is for the url of the database
- port_number is the port number for the database
- database_name is the name of the database
- username is the username for access
- password is the corresponding password for the given username
- table_title is the name of the table you want to load
Keep them in a text file or in an app that can be used to safely store sensitive access credentials.
2. Open Google Apps Script
Click Extensions in the main menu, then select Apps Script.
A new tab will be loaded for Apps Script. The right side of the screen is where we will paste the custom script to connect the database to Google Sheets.
Click on the code area, press Ctrl+A to highlight the default code, then press Delete on your keyboard. That clears the area.
3. Copy The Custom Script
Copy the following code (credits to Actiondesk):
var server = 'server_url';var port = port_number;var dbName = database_name;var username = 'username';var password = 'password';var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;function readData() { var conn = Jdbc.getConnection(url, username, password); var stmt = conn.createStatement(); var results = stmt.executeQuery('SELECT * FROM table_title'); var metaData=results.getMetaData(); var numCols = metaData.getColumnCount(); var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName('Sheet1'); sheet.clearContents(); var arr=[]; for (var col = 0; col < numCols; col++) { arr.push(metaData.getColumnName(col + 1)); } sheet.appendRow(arr); while (results.next()) { arr=[]; for (var col = 0; col < numCols; col++) { arr.push(results.getString(col + 1)); } sheet.appendRow(arr); } results.close(); stmt.close(); sheet.autoResizeColumns(1, numCols+1);}
Go back to the Google Apps Script tab. Press Ctrl+V or right-click again then select Paste. This will insert the code we copied into Google Apps Script.
4. Add Access Credentials in the Script
Replace certain lines in the script with the access credentials information you have:
Lines 1-5:
var server = 'server_url';
var port = port_number;
var dbName = database_name;
var username = 'username';
var password = 'password';
Line 11:
var results = stmt.executeQuery('SELECT * FROM table_title');
5. Save and Rename Project
Save the script by clicking the Save project icon near the top of the page.
7. Click the Run Button and Authorize the Script
Click Run the selected function.
When you run the function for the first time, Google Sheets will ask you for authorization with a box labeled Authorization Required. Click Continue.
A new window will appear, allowing you to select the Google account associated with the spreadsheet containing the button.
After selecting the account, a warning will appear reminding you that Google has not verified the app. Click the small link on the lower-left corner labeled Advanced.
Another reminder will appear to continue only if you understand the risks and trust the developer. Click Go to Untitled project (unsafe) or whatever the project name is in Google Apps Script.
A new page will appear listing the permissions needed by the script. Click Allow.
The script now works! In Apps Script you will get the following log:
And the database data has now been imported into the sheet.
Connect Google Sheets to a Database with Lido (Easiest way)
After seeing how to connect Google Sheets to a Database, you will find Lido easier to use. Here are the steps:
1. Get Access Credentials to the Database
First, you need to have access to the database. You need the following information:
- Hostname is for the url of the database
- Port is the port number for the database
- Database is the name of the database
- Username is the username you use to access the contents of the database
- Password is the corresponding password for the given username
Keep them in a text file or in an app that can be used to safely store sensitive access credentials.
2. Click Connect Data
On the Lido sheet, click Connect Data. It is in the upper-left corner.
The Add Data box will be loaded, listing the platforms that can be connected to Lido. Select MySQL.
You will then be asked to supply access credentials. Once done, name the connection, and then click Connect With MySQL.
A message saying you are successfully connected to the database will appear. Click Next.
3. Select Data to Import
After clicking Next, you can now select the data to import.
To select the table to import, click the drop-down list on the upper-left corner. For our example, we select the accounts table.
I
The list of columns will be automatically detected. You can select certain columns or tick Select All to include all columns for import.
Once you are satisfied, click Add Data on the lower-right corner.
The data is now imported to a separate sheet.
You can now process the data and visualize them in the built-in dashboard in Lido!
Why Lido Instead of Google Sheets?
Lido is a new spreadsheet app that has a built-in import function. This means you don’t need to get your hands dirty with hundreds of lines of code. Unlike Google Sheets, there is also no need to insert the same code for every spreadsheet you make. Lido can automatically scan your database for tables so you can select the ones you want to import on-the-fly.
Interested? Click here to get started.
