Connecting your Distilled database to Google Sheets.

Below is a step-by-step guide to help you connect your Distilled PostgreSQL database to Google Sheets, allowing you and your team to keep your spreadsheet up to date with the latest data. By using the available connection string in your account, you can integrate your Distilled data with Google Sheets and streamline your workflow. There are two methods to accomplish this:

Coefficient App Extension – non-technical option, easy to set up, free plan option
Google Apps Script – technical option, requires coding knowledge, free

We’ll explain each method in detail so you can choose the one that works best for you. Whether you’re a seasoned developer or a newcomer to database management, this guide will provide you with the information you need to make the connection quickly and easily. Once connected, your Google Sheet will be live and automatically updated with new data from your Distilled database, ensuring that you always have the most current information at your fingertips.

1. Coefficient App Extension for Google Sheets.

The Coefficient app automatically pulls data from your Distilled tables into Google Sheets without requiring custom SQL queries or any other coding. You will only need to set up the connection between Google Sheets and the Distilled Database once.

Connect Google Sheets to your Distilled PostgreSQL database with Coefficient in a few clicks by following these steps.

Step 1: Install the Coefficient add-on in Google Sheets by clicking Extensions on the top menu. Then navigate to the Add-ons menu from the dropdown and click Get add-ons.

Enter Coefficient in the Google Workspace Marketplace search bar. Click the Coefficient app and follow the steps to finish the installation.

Select your Google account. Click Allow to authorize Coefficient’s access to the required permissions to complete installation.

After installation, launch the Coefficient app from the Google Sheets menu. Click Extensions>Coefficient: Salesforce, HubSpot Data Connector>Launch.

You’ll see Coefficient running as a side panel on Google Sheets. Now you can start pulling data from the Distilled Database into your Google spreadsheet.

Step 2: Select Import from… on the Coefficient side panel.

Click PostgreSQL from the list of data sources.

Choose whether to import from tables or custom SQL queries. With Coefficient, you can perform SQL queries on top of your Distilled PostgreSQL database directly from Google Sheets.

Step 3: Click Import from tables

Provide the connection details, such as the host, database username, password, port, and IP addresses. All connection details can be found in your Distilled account under Analyze > Fetch your data

Click Connect when you’re done.

*Note: Coefficient may ask you to Whitelist several IP addresses. To do this, navigate to Analyze > Fetch your data > Add IP

Place the provided IP addresses in the correct fields inside of Distilled.

Step 4: Select the data and tables you want to import. Coefficient provides a data inline preview for Distilled PostgreSQL.

The data inline previewer allows you to import tables, columns, and rows using the front end. This way, you can quickly import data without needing to provide specific identifiers or table names. You can also apply limits and custom filters to your data import. This means you don’t have to use SQL to pull and format your data.

Step 5: Click Import. Your data should auto-populate inside your spreadsheet in seconds.

*Optional Step: Upgrade Coefficient Plan to Activate Feature ($25/month): Keep the Distilled PostgreSQL data up to date in your spreadsheet by setting up an auto-refresh schedule. You can set your data to auto-refresh hourly, daily, or weekly at your preferred day and time.

You can also refresh your data instantly by clicking the ‘Refresh’ button at the top of your data set.

2. Google Apps Script

For the more technical user with coding knowledge, Google Apps Script is a good option for connecting Distilled to Google Sheets. CData’s SQL Gateway lets you create a MySQL interface for ODBC drivers to connect the Distilled PostgreSQL database to your spreadsheet.

The MySQL protocol has native support via the JDBC service within Google Apps Script. Using the SQL Gateway, you can get access to live Distilled data in your Google spreadsheets.

Step 1: Before connecting the Distilled PostgreSQL database to your Google spreadsheet, you’ll need to provide the necessary connection properties within the Data Source Name (DSN). Leverage the built-in Microsoft ODBC Data Source Administrator to set up the DSN.

Set the server, database, and port connection properties, including the ‘user’ and ‘password’ you want to authenticate to the server. You can find these details inside your Distilled account by navigating to Analyze > Fetch your data.

The provider connects to your default database if the database property is unspecified.

Next, create a connection to the Distilled PostgreSQL data as a virtual MySQL database within the SQL Gateway UI. Once you’re done with the pre-configurations, harness Google Apps Script to access the MySQL remoting service and access your Distilled PostgreSQL data in Google Sheets.

You’ll need to create a script to populate your spreadsheet with your Distilled PostgreSQL data. To do this, open Apps Script from your active Google sheet. Then, make several class variables that should be available for functions created within the script.

Next, include a function that adds a menu option. This allows you to use the front end in Google Sheets to call your function.

Then write a helper function that can locate the first empty row in your spreadsheet.

Finally, create a function that writes the Distilled PostgreSQL data to your spreadsheet. Use Apps Script JDBC to link to the MySQL remoting service. Then SELECT data, and populate your Google spreadsheet.

Run the script and input the name of the Sheet and the Distilled PostgreSQL table you want to pull data from. The function is intended as a menu option, but you can extend its use as a spreadsheet formula.

It will import your Distilled PostgreSQL data into your spreadsheet, allowing you to calculate, graph, and chart your datasets.