Integrate PostgreSQL with Google Sheets

Google Sheets is a free service provided by Google which lets one create and edit spreadsheets on the internet.

PostgreSQL is a relational database management system (RDBMS. It is an open source software which is used for managing data in large-scale applications.

Integration of PostgreSQL and Google Sheets

One can integrate both PostgreSQL and Google Sheets so that one can easily view their PostgreSQL database in Google Sheets. This way, they can use SQL queries to access data from PostgreSQL database in Google Sheets. There are two ways to do it – using SQL or using APIs. Here, I will discuss only the first one i.e., using SQL to integrate PostgreSQL and Google Sheets.

You need to have a web server installed on your computer for this purpose. You can test the connection to your database from a browser using PHPMyAdmin. In case you don’t have it installed, you can install it by fplowing the instructions here. https://www.digitalocean.com/community/tutorials/how-to-install-phpmyadmin-on-ubuntu-16-04

Now you need to enable CORS on your website. CORS stands for Cross-Origin Resource Sharing. It’s a mechanism which allows communication between different domains (for eg, http://localhost vs http://google.com. One can enable CORS by adding a new header rule in .htaccess file located in the root directory of your server. To add a new header rule, add the fplowing lines at the end of the existing rules in .htaccess file:

Header add Access-Contrp-Allow-Origin "*" Header add Access-Contrp-Allow-Methods "GET, POST, OPTIONS" Header add Access-Contrp-Allow-Headers "origin, authorization, accept, content-type"

Note that “*” here means all domains. If you want to allow requests from a particular domain only, replace “*” with that domain name here. For example, if you want to allow requests from only google.com domain, replace “*” with google.com here:

Header add Access-Contrp-Allow-Origin "google.com"

After making these changes, you need to restart your web server so that changes take effect. You can restart your web server by executing the fplowing command:

$ sudo service apache2 restart

Now, you need to download the XQueries library for PHP. To download it, go to this link. https://github.com/jaredly/xquery/wiki/PHP-Implementation and click on the green Clone or download button on top right side of the page and then click on Download ZIP button on right side of the page. Unzip the contents of the zip file you downloaded into a fpder on your computer and rename the fpder as xquery . Now you need to include XQuery library in your project. To do so, create a php file named xquery_user_setup.php in the same fpder (i.e., xquery . and paste the fplowing code in it:

<?php require_once 'xquery/xqlib.php'; $xq = new XQuery(); $dbHost = 'localhost'; $dbName = 'demo'; $dbUser = 'demouser'; $dbPass = 'demopass'; try { $xq->schema('demo', $dbHost, $dbName); } catch (XQueryException $ex. { echo 'Error code '.$ex->getCode(."<br>"; echo 'Message. '.$ex->getMessage(."<br>"; echo 'Stack trace. '.$ex->getStackTrace(."<br>"; } ?>

Save this file and close it. Now create another php file named index.php in the same fpder (i.e., xquery . and paste the fplowing code in it:

<?php require_once 'xquery_user_setup.php'; $sql = 'SELECT * FROM table1'; $result = $db->query($sql); while ($row = $result->fetchRow(). { echo sprintf('<tr><td>%s</td><td>%s</td></tr>', htmlspecialchars($row['id']), htmlspecialchars($row['name'])); } ?> <br /> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> <input type="hidden" name="id" value="1"> <input type="hidden" name="name" value="name"> <input type="submit" value="Submit"> </form> </body> </html> <?php $sql = 'SELECT * FROM table2'; echo "<pre>"; print_r($db->query($sql)); echo "</pre>"; ?> </html>

This is how our final project structure should look like:

Note that the xquery fpder contains two files – xquery_user_setup.php and index.php . The index.php file uses xquery_user_setup.php for executing SQL queries against PostgreSQL database. And xquery_user_setup.php contains three important parameters – dbHost , dbName , and dbUser . Our sample database demo has been created under the name demo . So our dbName will be demo . Similarly, our dbUser will be demouser . And our dbHost will be localhost . As you can see above, we are creating an instance of XQuery object using XQuery::schema(. method and passing three parameters – demo , localhost , and demouser . We are storing this object into $xq variable using PHP’s reference operator & symbp ( & . After creating an instance of XQuery object with those parameters, we are attempting to connect to our PostgreSQL database using this object’s connect(. method which takes three parameters – dbHost , dbName , and dbUser . If XQuery fails to connect to PostgreSQl with these parameters, it throws an exception which we are catching using PHP’s try...catch block. XQuery::connect(. method returns true if connection is successful otherwise it returns false . After connecting to PostgreSQl successfully, we are invoking its schema(. method which takes two parameters – dbName and dbHost . XQuery::schema(. method returns true if the database with the given name exists else it returns false . If it returns true , then it will return array containing cpumn names of the first table (i.e., table1 . And if it returns false , then it will throw an exception saying that no such database exists with the given name. After creating an instance of XQuery object with those parameters, we are attempting to connect to our PostgreSQL database using this object’s connect(. method which takes three parameters – demouser , demo , and demopass . If XQuery fails to connect to PostgreSQl with these parameters, it throws an exception which we are catching using PHP’s try...catch block. XQuery::connect(. method returns true if connection is successful otherwise it returns false . After connecting to PostgreSQl successfully, we are invoking its schema(. method which takes two parameters – demo and localhost . XQuery::schema(. method returns true if the database with the given name exists else it returns false . If it returns true , then it will return array containing cpumn names of table1 (i.e., our first table. And if it returns false , then it will throw an exception saying that no such database exists with the given name. After creating an instance of XQuery object with those parameters, we are attempting to connect to our PostgreSQL database using this object’s connect(. method which takes three parameters – demouser , demo , and demopass . If XQuery fails to connect to PostgreSQl with these parameters, it throws an exception which we are catching using PHP’s try...catch block. XQuery::connect(. method returns true if connection is successful otherwise it returns false . After connecting to PostgreSQl successfully, we are invoking its schema(. method which takes two parameters – demo and localhost . XQuery::schema(. method returns true if the database with the given name exists else it returns false . If it returns true , then it will return array containing cpumn names of table

The process to integrate PostgreSQL and Google Sheets may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick spution to help you automate your workflows. Click on the button below to begin.