PostgreSQL is one of the popular open source SQL relational databases. According to its website, it has over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. In this tutorial, we will learn two methods for importing PostgreSQL data to Google Sheets, and then we also learn how to import PostgreSQL data using the Lido app. Are you ready?
To connect to a PostgreSQL database, you need the following information:
Alternatively, you can create a connection string with the following format:
postgres://username:password@server_url:port_number/database_name
The better syntax will depend on the importation method that you choose.
As we have emphasized in our blog post about Google Sheets add-ons, you have a wide variety of choices in selecting an add-on that will boost the capabilities of your spreadsheet. For this tutorial, we will use the SyncWith | Any API add-on.
After installing the add-on, the sidebar appears as below. It shows a list of platforms that it can import to Google Sheets, and has a search bar to quickly find the platform you want to access.
Once you select PostgreSQL, the list of access details will show up. You can either add the connection parameters or the connection string.
Besides the connection parameters, you will also need to provide an SQL query. One example query you can use is the following:
SELECT * FROM table_name
We have also written two articles detailing the possible SQL Queries that you can use. Check them out below:
SQL Basics: Introduction to Queries - Lido.app
Advanced SQL Queries & Tutorial - Lido.app
This specific query will import all the columns and rows from the table_name.
Once you have set the sidebar, click Insert at the bottom of the sidebar. When you click the arrow beside the Insert button, it will also give you an option to insert the data at a certain selected cell of your active sheet. You can get the following result:
SyncWith inserts a checkbox at the first cell that you can check if you want to manually refresh the spreadsheet.
Being a popular implementation of SQL, PostgreSQL data can also be imported using other methods that work for SQL-based databases such as MySQL. One way to do so is to create a custom Google Apps Script. If you are tech-savvy, you may prefer this option because it removes a middleman that will process the data before it reaches your spreadsheet.
Check the following tutorial to access the code and tweak it as needed:
How to Import Data from a MySQL Database to Google Sheets
Unlike Google Sheets, Lido has a built-in feature that allows you to directly import data from popular eCommerce and marketing platforms, which includes PostgreSQL databases. To do so, click the Data icon on the upper-left side of Lido, and then select PostgreSQL.
The left sidebar changes to a list of textboxes where you must input the connection parameters. You have to whitelist the IP addresses shown in the sidebar in your PostgreSQL database.
When you are done, click Connect.
When Lido successfully connects to your PostgreSQL database, it will load the Dataset Editor. This is where you can select the data to be imported. Select the database on the drop-down box on the left side, then click Open Data Explorer.
A large window labeled Select data will appear, where you can select the table to load on the left side and then select the columns listed under that table. For our example, we want to load all of the columns. You can also opt not to load all the rows by specifying the range of rows on the lower-left side of the box. You can use that feature to also check how many rows are stored in the selected table. Once you are done, click Save.
The Select data window will close, and you will go back to Database Editor, but with a preview of the PostgreSQL data you will load. If you are satisfied, click Save dataset & add view.
The PostgreSQL data will then be loaded into your Lido spreadsheet:
Have you noticed something? Unlike in the Google Sheets add-on we use, we do not need prior knowledge of the tables stored in the database nor even the basics of SQL. This makes using Lado a breeze for non-technical teams who need to access otherwise complicated PostgreSQL data!
Interested? Click here to get started today!
-Filter SQL Data Before Importing to Google Sheets
-Import MySQL Database to Google Sheets
-Export SQL Data to Google Sheets
