Connect Google Sheets to a Postgres Database

Connecting Postgres to Google Sheets can make it easy for business teams to pull data into a spreadsheet for analysis. The spreadsheet is often the preferred tool for building models and combining various data sets into one analysis. The following post covers the benefits and considerations when importing data from PostgreSQL to Google Sheets and provides instructions for 3 different methods of connecting Google Sheets to PostgreSQL.

Table of Contents:

The top 5 Methods for connecting Google Sheets to a Postgres Database

1. CSV Upload
2. Google Sheets App Script
3. Coefficient
4. Row Zero
5. Other Connectors

• What is Postgres
• The pros and Cons when connecting Postgres to Google Sheets.
• Conclusion

The top 5 Methods for Connecting a Postgres Database to Google Sheets

CSV Upload

The CSV option is simple and free but is not an actual connection between Postgres and Google Sheets and is a one-time data upload to Google Sheets that will need to be repeated every time the data in the Postgres database changes. Follow the steps below for the CSV upload from Postgres to Google Sheets. Keep in mind that if the CSV being uploaded to Google Sheets is 100,000 or more rows, the program may slow down or crash. In that case, consider using Row Zero, a cloud based spreadsheet designed for big data.

1. Use a SQL editor, like SQL Workbench.
2. Open SQL Workbench and launch the application on your computer.
3. Create a new connection. If you haven't already connected to your database, you'll need to set up a new connection. Click File > Connect window, or click the 'Select Connection Profile' icon in the toolbar.
4. Enter connection details. Fill in the details for your database connection (such as database type, host, port, database name, user, password) and click 'OK' to establish the connection.
5. Open a new SQL tab. If not already open, start a new SQL tab by clicking File > New SQL Tab or the appropriate toolbar icon.
6. Write your SQL query. In the new tab, write the SQL query you want to execute. For example:

Copy code
SELECT * FROM your_table;

1. Execute the query. Run the query by pressing F9 or clicking the 'Execute' button.
2. After the query runs, the results will be displayed in the lower panel of the SQL Workbench window.
3. Export to CSV. Right-click on the result set. Choose Export to Clipboard/File. In the export window, set the 'Export Format' to Text (CSV, Tab, ...). Configure the CSV options as desired (e.g., column delimiter, string quote character, filename, etc.). Choose the location where you want to save the CSV file and provide a filename. Click 'OK' to export the data.
4. In Google Sheets click on File > Import > Upload > Browse and select the csv file on your computer.
5. Repeat the process if data in the Postgres database is updated.

Google Sheets App Script

Google Sheets provides an app development functionality within the product, called Apps Script. App Script allows Google Sheets users to build custom features within the product, like menus, sidebars, and other web based apps. App script also includes a native MYSQL protocol through a Java Database Connectivity (JDBC) service. This JDBC service enables connection to databases. The Apps Script + JDBC functionality can be used to connect a Postgres database to Google Sheets. To connect Postgres to Google Sheets using Apps Script, follow the instructions below.

1. Setup the Postgres DSN

The first step in connecting Google Sheets to a Postgres database is to download and install the Postgres ODBC driver. The purpose of the PostgreSQL ODBC driver is to provide a standardized interface for applications to access Postgres databases regardless of the programming language or operating system used by the application.

Download the PostgreSQL ODBC Driver

In order to download the driver, visit https://odbc.postgresql.org, click on the link to go to the download site, click on 'msi' and then download the most recent version that matches the version of windows running on your computer (x32, x64, or x86). You can also click on the link here to go straight to the ODBC driver downloads page.

Install the PostgreSQL ODBC Driver

Once you've downloaded the correct drive version, unzip or decompress the file in your downloads folder. Inside, you will find an executable file type (e.g. psqlodbc_x64.msi). Double click on the executable to install the driver and follow the steps in the installation wizard.

Configure and Add the PostgreSQL ODBC Driver in Windows

In Windows, go to the start menu and search for 'ODBC Data Sources.' Select and open the ODBC Data Source Administrator.

Navigate to the 'Drivers' tab and look for your PostgreSQL driver in the list. If you don't see it, it has not been installed correctly and you should try repeating the previous step. Assuming your driver is visible in the list, click on the 'User DSN' or 'System DSN' tabs, click 'Add,' select the PostgreSQL driver from the list, and click 'Finish.'

Next add the connection credentials for your postgreSQL database. You will need to match the settings on your database. For example, if the database allows encryption, you will need to toggle the 'SSL Mode' value to match it. Once credentials have been entered, click 'Test' to test the connection. If the Test is successful, click 'Save.' The PostgreSQL driver will now show up in either System DSN or User DSN lists.

2. Install the SQL Gateway

The second step in connecting Google Sheets to your Postgres database is to download and install a SQL Gateway, which can be configured to work with the DSN created in the previous step. A company called CData makes a SQL Gateway, which can be downloaded from their site and configured by following the instructions at CData SQL Gateway. There is an additional page where you can find are further instructions about how to configure their SQL Gateway software.

3. Write and run the Appscript code

• Create a new App Script in your Google Sheet using the top menu and selecting Extensions > Apps Script
• When the app script window opens, it will look like the image below. Select and delete the sample 'function myfunction()' and begin using the code provided below. Make sure to replace the example values for your own values in the first code snippet below.
• Next, create a set of variables that identify the database address and login information to be used by the program. Replace the values in green with your own values.

//replace the variables in this block with real Postgres values as needed
var address = 'your_host:your_port'; // e.g., 'localhost:5432'
var user = 'your_username'; // e.g., 'postgres'
var userPwd = 'your_password'; // Your database password
var db = 'your_dbname'; // Your database name

// JDBC connection string for PostgreSQL
var dbUrl = 'jdbc:postgresql://' + address + '/' + db;

• Write a function that will appear in the Google Sheets menu bar and allow you to run in from the UI rather than with code.

// Create a function that adds a menu item to google sheets
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Import PostgreSQL Data', functionName: 'importPostgresData'}
  ];
  spreadsheet.addMenu('PostgreSQL Data', menuItems);
}

• Write a function that will identify the first empty row in a spreadsheet in which to start the import.

/*
* Finds the first empty row in a spreadsheet by scanning an array of columns
* @return The row number of the first empty row.
*/
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var columnData = spreadSheet.getRange(column + ":" + column).getValues();
  var emptyRowIndex = 0;
  while (columnData[emptyRowIndex] && columnData[emptyRowIndex][0] != "") {
    emptyRowIndex++;
  }
  return (emptyRowIndex + 1);
}

• Finally, write a function that will import and write Postgres data to your Google Sheet.

/*
* Reads data in a specified Postgres table and then imports it to the specified sheet in your workbook.
*    (If there is no specified Google Sheet, the function will create one.)
*/
function importPostgresData() {
  var thisWorkbook = SpreadsheetApp.getActive();
  var selectedSheet = Browser.inputBox('Enter the name of the sheet for data import:', Browser.Buttons.OK_CANCEL);
  if (selectedSheet === 'cancel') return;

  if (!thisWorkbook.getSheetByName(selectedSheet)) 
    thisWorkbook.insertSheet(selectedSheet);

  var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  var table = Browser.inputBox('Enter the name of the PostgreSQL table to import:', Browser.Buttons.OK_CANCEL);
  if (table === 'cancel') return;

  var stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT * FROM ' + table);
  var rsmd = results.getMetaData();
  var numCols = rsmd.getColumnCount();
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");

  if (firstEmptyRow == 1) {
    var headers = new Array(numCols);
    for (var i = 0; i < numCols; i++) {
      headers[i] = rsmd.getColumnName(i + 1);
    }
    resultSheet.getRange(1, 1, 1, numCols).setValues([headers]);
  }

  var rowNum = firstEmptyRow;
  while (results.next()) {
    var row = new Array(numCols);
    for (var i = 0; i < numCols; i++) {
      row[i] = results.getString(i + 1);
    }
    resultSheet.getRange(rowNum, 1, 1, numCols).setValues([row]);
    rowNum++;
  }

  results.close();
  stmt.close();
}

*The entire script can be found at the end of this blog post in the Appendix

• Once all the code has been written or copied from this blog, save the project with the 'Save' icon on the top menu bar. Then run the program. You will be prompted to verify and give access to your Google Account.
• After running the program, you will see a 'Postgres Data' menu option in your Google Sheet
• The first time you run the script, you may be asked to authorize access to your google account. Follow the prompts to complete this step.
• When running the extension, you will first be asked which sheet should receive the imported data. Simply type the sheet name (e.g. 'Sheet1'). If no sheet with that name exists, a new sheet will be created.
• A second question will ask which table should be imported. Type the name of the table in the text box.
• After responding to the questions, the program will import your Postgres data into the Google Sheet you selected. The program can be re-run any time the data needs to be updated with new data in the Postgres database.

Coefficient

Coefficient is a Google Sheets add-on that makes it easy to connect the spreadsheet to a number of different applications. Coefficient offers a free plan that limits import to 5000 rows. The starter plan is $50/mo with the same 5000 row limit and the Pro plan is $99/user/mo and has no row limit on import. To use Coefficient to connect a Postgres database to Google Sheets, follow the steps below.

1. Install the Coefficient extension in Google Sheets. If starting from Coefficient's page, follow the instructions with the pop-up menu. If starting from your own Google Sheets workbook, use the top menu and click Extensions>Add-Ons>Get Add-ons. Search for 'Coefficient.' Select Coefficient from the search results and click 'Individual Install.'
2. Follow the steps to authenticate your Google account with Coefficent and give the add-on access.
3. Launch the application by going to Extensions>Coefficient>Launch.
4. Once launched, follow the steps to answer questions and select Postgres as your import source
5. Enter your database name, username, password, and an optional nickname. If your Postgres database is behind a firewall or private network, you will need to whitelist the Coefficient IP addresses.
6. Once connected, you can use the visual query editor or write your own sql query and click import.
7. The data will load into a Google sheet. The data in Google Sheets can be automatically updated every time Postgres data updates if you use a paid tier of Coefficient.
8. Be cognisant of the query result size as Google sheets has a cell limit (rows x columns) of 10M cells. Google Sheets also has a file size limit of 100MB. There is a blog specifically focused on the Google Sheets row limit here. To work with large data sets in a spreadsheet, use Row Zero, a spreadsheet designed for big data.

Row Zero

Connect Postgres to a Row Zero Spreadsheet

1. Connect Row Zero to a PostgreSQL database

In Row Zero click on the 'data source' icon in the right-hand menu and then click the button for 'New data source.'

The connection requires 6 pieces of information defined below.

• Name - This field can be anything that helps identify the data source being connected. (e.g. "Production Data" or "Product Metrics")
• Host - This is the PostgreSQL address that typically takes the form of a string like 'database.mydomain.com.'
• Port - This is always 5432.
• User - This is the username for your PostgreSQL database.
• Password - The password used to log into your PostgreSQL database.
• Database - The name of the PostgreSQL database being connected to.

Once all the information is entered, hit 'Test connection' to ensure the information is correct. If the test connection icon turns green and says 'Connected', proceed by clicking on '+ Add Source.'

2. Write a query to import data

Now that the Postgres database is connected to Row Zero, begin writing a query to pull data into the spreadsheet. First select the 'Schema' from the drop down. Write a query in the query editor. The easiest query to write is the 'select *' statement, which pulls in the entire table. Example:

select * from TABLENAME

Row Zero is equipped to handle large data sets and will easily ingest the entirety of a database. Once the query is executed by hitting the 'run' button at the bottom of the SQL window, Row Zero will show the progress of the data import. You can see the amount of time it has taken and the number of rows imported.

Write more complicated queries to pull in various subsets of data stored in the Postgres database. All results will be displayed in the 'connected data table' in the spreadsheet.

3. Begin analysis in Row Zero

Once the data is loaded into the data table use all the features in Row Zero to analyze, filter/sort, pivot, and graph the data.

Other Connectors

There are a few other connectors that can be used to connect Postgres to Google Sheets. Some notable options are listed below but there are many options of varying costs and complexity. In this post we covered the most commonly used options.

• AirOps - Requires use of their 3rd party UI to connect Postgres and Google Sheets.
• Polytomic - A fairly technical option for those familiar with SQL and/or Python
• Hevo Data - Another application that requires use of their 3rd party portal to connect Postgres and Google Sheets.

What is Postgres?

Postgres or PostgreSQL is an open source object-relational database management system (ORDBMS). It was developed at the University of California Berkeley in 1986. PostgreSQL is popular because it is open source (free) and runs on all major operating systems, including Linux, Unix, Mac OS, and Windows. It supports text, image, sound, and video files. Lastly, it has programming interfaces for many of the more popular languages, like C/C++, Java, Perl, Python, Ruby, and others. You can find all the details about PostgreSQL at postgresql.org. Due to the open source license and compatibility with most programming languages and data types, PostgreSQL databases are a popular choice for startups and large businesses alike.

The Benefits of Connecting a Postgres Database to Google Sheets

Production Data

Connecting Google Sheets to a Postgres DB enables financial and accounting models to be built on product or operational data stored in the database.

Live Models

Once a PostgreSQL database is connected to a spreadsheet, like Google Sheets, it is easy to re-run or refresh the PostgreSQL query to pull the latest dataset into the spreadsheet without having to recreate another static analysis.

Dashboards

With a connected spreadsheet it is easy to turn a simple analysis into a dashboard. Simply refresh the queried data from a Postgres instance and all graphs, pivot tables, and spreadsheet models will update.

Considerations When Connecting Postgres to Google Sheets

Security

Importing data from Postgres to Google Sheets poses an information security risk because the data is moving from a cloud data warehouse where activity is traceable and behind layers of security to a locally accessable Google Sheets file stored on a computer. Once the data is in Google Sheets, that data goes wherever the computer goes. The computer could be lost, stolen, or shared. The file could also be emailed to personnel who wouldn't otherwise have access to the data. A more secure option is a cloud based spreadsheet, like Row Zero

Data Set Size

While Google sheets is a great tool it cannot support large data sets. Google sheets will often slow down or crash as data sets sizes reach tens of thousands or a hundred thousand rows, depending on the number of columns. For more information on the Google Sheets row limit, read our previous blog. It is common for data sets stored in a Postgres database to be quite large. To import data from Postgres to Google sheets, the Postgres query will need to trim the data down to a size Google Sheets can manage or it will be better to use a more powerful spreadsheet, like Row Zero.

Cost

Some methods for connecting Postgres to Google Sheets charge a monthly fee. Depending on the company's budget, paying for another service may or may not be acceptable

Complexity

Certain options for connecting Postgres to Google Sheets are technically complicated and not easy to maintain. They may require writing code or regularly repeating a process every time the Postgres data is updated.

Conclusion

After reviewing the popular options for connecting PostgreSQL to Google Sheets, it is evident that there are several different methods available. Some approaches, like CSV import, are straightforward and cost-free. Others, such as using Google Apps Script, are more technically involved. There are also products like Coefficient which simplify the process but impose row limits on data imports and require payment for features like auto-updating the connected data. A significant challenge, however, is that many datasets in PostgreSQL can be quite large, and Google Sheets struggles with large data sets. To fully utilize the connection of PostgreSQL to a spreadsheet, the best option is Row Zero, a blazingly fast spreadsheet designed for handling big data. Row Zero offers a simple user interface for connecting to PostgreSQL and can effortlessly manage many millions of rows of data. Try Row Zero for free and connect to PostgreSQL now.

If you use another cloud data warehouse or blob storage, Row Zero also connects to Snowflake, Redshift, and S3.

Appendix

// Replace with your PostgreSQL database details
var address = 'your_host:your_port'; // e.g., 'localhost:5432'
var user = 'your_username'; // e.g., 'postgres'
var userPwd = 'your_password'; // Your database password
var db = 'your_dbname'; // Your database name

// JDBC connection string for PostgreSQL
var dbUrl = 'jdbc:postgresql://' + address + '/' + db;

// Create a menu function
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Import PostgreSQL Data', functionName: 'importPostgresData'}
  ];
  spreadsheet.addMenu('PostgreSQL Data', menuItems);
}

// Create a function that adds a menu item to google sheets
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Import PostgreSQL Data', functionName: 'importPostgresData'}
  ];
  spreadsheet.addMenu('PostgreSQL Data', menuItems);
}

/*
* Finds the first empty row in a spreadsheet by scanning an array of columns
* @return The row number of the first empty row.
*/
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var columnData = spreadSheet.getRange(column + ":" + column).getValues();
  var emptyRowIndex = 0;
  while (columnData[emptyRowIndex] && columnData[emptyRowIndex][0] != "") {
    emptyRowIndex++;
  }
  return (emptyRowIndex + 1);
}

/*
* Reads data in a specified Postgres table and then imports it to the specified sheet in your workbook.
*    (If there is no specificed Google Sheet, the function will create one.)
*/
function importPostgresData() {
  var thisWorkbook = SpreadsheetApp.getActive();
  var selectedSheet = Browser.inputBox('Enter the name of the sheet for data import:', Browser.Buttons.OK_CANCEL);
  if (selectedSheet === 'cancel') return;

  if (!thisWorkbook.getSheetByName(selectedSheet)) 
    thisWorkbook.insertSheet(selectedSheet);

  var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  var table = Browser.inputBox('Enter the name of the PostgreSQL table to import:', Browser.Buttons.OK_CANCEL);
  if (table === 'cancel') return;

  var stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT * FROM ' + table);
  var rsmd = results.getMetaData();
  var numCols = rsmd.getColumnCount();
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");

  if (firstEmptyRow == 1) {
    var headers = new Array(numCols);
    for (var i = 0; i < numCols; i++) {
      headers[i] = rsmd.getColumnName(i + 1);
    }
    resultSheet.getRange(1, 1, 1, numCols).setValues([headers]);
  }

  var rowNum = firstEmptyRow;
  while (results.next()) {
    var row = new Array(numCols);
    for (var i = 0; i < numCols; i++) {
      row[i] = results.getString(i + 1);
    }
    resultSheet.getRange(rowNum, 1, 1, numCols).setValues([row]);
    rowNum++;
  }

  results.close();
  stmt.close();
}