Connecting Google Sheets to Projectworks

It is possible for for applications to connect to the Projectworks database using the SQL login credentials which are available within the Admin section of Projectworks.

However, Google Sheets does not natively support connecting to a SQL Database and will require a third party addon to make this possible.

The best addon to use will depend on the exact requirements off each end user, though the setup is largely the same. A common extension is one called Database Browser and is made by a company called Jivrus. This addon has a free version which doesn’t allow custom queries, but the paid versions start at $9 per month which will likely give a user everything they need.

To install this extension, you can follow these steps: 

1. Sign into Google Sheets and create a new blank sheet.

2. Click Extensions > Addons > Get Addons.

3. Search for Database Browser and find the extension made by Jivrus Technologies.

4. Click the tile and click install.

5. Once installed click Extensions > Database Browser > Start.

6. Click Create new connection.

7. Select MS SQL Server.

8. Setup the connection to you Projectworks instance

   1. Type in a friendly name for the connection

   2. You can find the Hostname, Database Name, User Name, and Password in the admin section of Projectworks in the Integration section.
      

9. Test and save the new connection.

10. Once successfully connected then you can click “Create New Query”.

11. Create a new query

    1. Select the newly created connection in the Query Name dropdown.

    2. Select Custom SQL

    3. Write the query that selects the data you require. Please note that queries against the direct data connection views will need the [BI] prefix included in the query or they will not work. An example query might be: SELECT * FROM [BI].[FactUsers]

12. Select the newly created blank sheet as the destination.

13. Click Execute.