In this article:

Connect Google Sheets to a Database (Easiest Way in 2024)

May 8, 2024

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.

google sheets connect to database, click extensions, 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.

google sheets connect to database, google apps script page‍

Click on the code area, press Ctrl+A to highlight the default code, then press Delete on your keyboard. That clears the area.

Clear google apps script area to insert custom code to connect google sheets to database

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

custom code to import database to google sheets inserted‍

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. 

Connect database to google sheets, save project to enable function‍

7. Click the Run Button and Authorize the Script

Click Run the selected function.

Connect database to google sheets, run the selected function button highlighted‍

When you run the function for the first time, Google Sheets will ask you for authorization with a box labeled Authorization Required. Click Continue

connect google sheets to database, authorization required for the script to run‍

A new window will appear, allowing you to select the Google account associated with the spreadsheet containing the button. 

connect google sheets to your database, choose account to authorize the script

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.

connect google sheets to my database, google hasnt verified this app warning‍

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.

connect database to google sheets, google hasnt verified this app warning, link to continue using it appears

A new page will appear listing the permissions needed by the script. Click Allow

connect google sheets to database, list of permissions needed by the script shown

The script now works! In Apps Script you will get the following log:

Google sheets connect to database, execution log confirms code running

And the database data has now been imported into the sheet.

data imported to 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.

connect data button highlighted‍

The Add Data box will be loaded, listing the platforms that can be connected to Lido. Select MySQL

import database using lido

You will then be asked to supply access credentials. Once done, name the connection, and then click Connect With MySQL

connect to database

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.

add data for selecting 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. 

select 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. 

database import

Once you are satisfied, click Add Data on the lower-right corner.

The data is now imported to a separate sheet.

Import database google sheets with lido, data imported to lido

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->