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?
How to connect to PostgreSQL databases
To connect to a PostgreSQL database, you need the following information:
server_url is for the url of the PostgreSQL server
port_number is the port number for the PostgreSQL server, usually set at 5432
database_name is the name of the PostgreSQL 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 into a spreadsheet
Alternatively, you can create a connection string with the following format:
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.
How to import PostgreSQL data using Google Apps Script
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:
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!