In this article we will show you how to import SQL to Google Sheets in just a few clicks. Simply follow the steps below.
First, you need to have access to the SQL server through an account. The following information will be needed:
If you don’t have a Lido account, you can create a free one here.
Create a new file then click Connect Data. Select your database type in the Add data modal then enter in your credentials from Step 1.
Now that your database is connected it’s time to select the exact data to import.
Lido has a visual data browser so you can point and click to select the exact data you want to connect. Use the dropdown menu in the upper left corner to change tables. Alternatively, you can write SQL directly by clicking the Custom tab.
Optional: Add filters to columns through these menus.
When you’re happy with your data in this view, click Add Data.
This creates a Table in Lido that is live one-way connected to your SQL database. Any changes in your database will be automatically reflected in Lido. However, you cannot edit this data in Lido and must do so in the original source.
Once your SQL data has been imported into a Lido spreadsheet, you can work with it using normal spreadsheet formulas.
For example, if your database only contains First Name and Last Name, but you need a column for Full Name, you can easily create one by adding a Computed Column.
Your data is likely scattered across multiple tables in your database or even multiple sources (like Salesforce, Hubspot, or Google Analytics). Joining data
You can easily send emails, slack and whatsapp messages to your imported data.
While this method is also effective, we now suggest using the Lido method above as it is much more user friendly and customizable.
First, you need to have access to the SQL server through an account. The following information will be needed:
Keep them in a text file that can be used to safely store sensitive access credentials.
You can open Google Apps Script by clicking Extensions in the main menu, then selecting Apps Script.
A new tab will be loaded for Apps Script. The right side of the screen is where we will insert a special function to import SQL data to Google Sheets.
Copy the following code:
====
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. Click on the code area, press Ctrl+A to highlight the default code, then press Delete on your keyboard. That clears the area.
Afterwards, press Ctrl+V or right-click again then select Paste. This will insert the code we copied into Google Apps 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');
Save the script by clicking the Save project icon near the top of the page.
Afterwards, you can also name the script by clicking the default title Untitled project.
A small pop-up box will appear where you can rename the project. Type the name you want to give, then click Rename.
We are now ready to authorize the script.
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 SQL data has now been imported into the sheet.
Absolutely! You can use the script in this tutorial for PostgreSQL. You can learn more about it and other ways to do so in our tutorial page dedicated to it.
Want to import data from SQL databases to Google Sheets without needing to create a custom Google Apps script? Say no more! Lido offers a simple solution to seamlessly integrate data from multiple databases, including SQL, in just a few clicks. Click here to get started!
We hope this article has helped you and given you a better understanding of how to import SQL to Google Sheets. You might also like our articles on how to import JSON to Google Sheets and how to connect Snowflake to Google Sheets.
To optimize your workflow, we recommend checking out our template for contact lists in Google Sheets.
