How I learned to get my data inside Salesforce published on my website automatically using a third-party application exchange tool called GConnectHow I learned to get my data inside sales force published on my website automatically using a third-party application exchange tool called GConnector.
I love using Salesforce at my small nonprofit. We are small but mighty… $225,000 receipts with an average annual give of $691, 4.5ish employees strong. Working alongside 12M$+ nonprofit partners. Providing direct service to 509 unique homeless young people in 2016, meeting them 8,517 times. Tracking 647 unique goals and outcomes last year. There is little time to waste anything in such a fast-paced environment. And Salesforce really helps (to know what we did, what we are doing, what we haven’t done yet, and what we need to do next!)
However, as much as I loved migrating data into Salesforce since 2013, breaking down the isolated spreadsheet databases, one thing has really nagged me from the beginning. I have all these beautiful reports and dashboards but I have no way to share them with the outside world. I’ve looked into every way I could imagine. I talked to lots of people. Nobody had anything to offer that didn’t require tons of development time, new user licences, login, or whatever. I remained frustrated that I could not even do simple things like put a real-time bar chart of a fundraiser on our web page.
Well… NO MORE! I discovered a new paid app called GConnector. My free trial was super because it saved me so much time and let me do what I’ve been waiting 4 hours to do! I am the king of cheap and free, but sometimes you have need to pay when time is scarce or something isn’t possible otherwise. And GConnector falls into that category for me. But when I went to pay for it, it turns out it’s free for nonprofits (3 licenses). Where have you been all my life!?!?
Take a look for yourself. I have live graphs of donations to date compared to previous years. I have graphs of volunteer hours, in-kind donation impact, new clients, numbers of positive outcomes. All driven from Salesforce and all updated automatically! You find them all over our website StreetYouthMinsitry.org but here are some screenshots.
Here is the donation graph I always wanted:
And then there are lots of other impact graphs:
I created a sheet in Google Apps to host my data. I installed the Sheets add-on for GConnector. I login to my production org using GConnector. Then I use GConnector to create a query of my data using SOQL. Fear not! The plugin walks you through the basics. I had never learned SOQL but it was simple to get started.
I started my donation graph with this simply query. I use payments, so I drove cash flow off payments:
Select npe01__Payment_Amount__c, npe01__Payment_Date__c from npe01__OppPayment__c WHERE npe01__Paid__c = TRUE
That’s it! I now it looks bad, but it’s only bad because of the API names of all the variables. I picked them all from the GConnector interface. I didn’t need to remember or lookup anything!
I did decide to only graph the last few years, so I added WHERE npe01__Payment_Date__c >= 2014-01-01.
This query results in a new tab in my sheet with columns of data that I asked for. Want to update it, just update the sheet or all sheets using GConnector. Want to modify the query, just use GConnector to edit the query (it is stored somewhere automagically so you don’t have to type anything again… just edit) and hit the “Get Data” button. And you can also schedule it to update hourly, daily, or weekly without you!
I typed in none of this data (except the notes in column D). It’ all came from Salesforce through GConnector.
You can still do anything you want on other tabs of your spreadsheet. Initially, I added a second sheet to summarize the giving data by month and year using spreadsheet formula. It wasn’t too hard but a little tricky. I wanted to graph it so I added a column for month labels and a row for year labels. Then I used formula to find the right information. I could have used a pivot chart function. I added another tab with the graph of that data, formatted it to match our website, and “published” the graph as an image. These are all stable and standard Google Apps functions. I inserted a link to the live image on my web page. When GConnector updates the data, the tab with data changes, the formulas work on the other tabs, the graph updates, the image updates, and my web page updates! Wow! That’s a lot of work that just happens automatically now!
Here is the page where I used some formula magic (Vlookup) to put the data into rows and columns the way I wanted. And I included more donor friendly labels for the months. I could do anything on the tab.
And then here is the graph tab. You can use any formatting tricks you know to get the graph and labels you want:
I wanted more charts! GConnector works with custom and standard objects, too! So now I was going to query volunteer hours and client goal notes. I learned to add a sorting statement just to name my life easier. I had to lookup the SOQL language reference on the web to learn how to do this.
I added sorting with a ORDER BY npe01__Payment_Date__c clause.
I got rid of my intermediate complex spreadsheets formula by asking SOQL to do more of the work. I added formula to extract the date and month of the donations by adding to the select statement: SELECT CALENDAR_YEAR(npe01__Payment_Date__c), CALENDAR_MONTH(npe01__Payment_Date__c), npe01__Payment_Date__c, npe01__Payment_Amount__c. There are only a few of these operators but they can manipulate your data during the query without the need to do anything in your database itself. CALENDAR_MONTH returns a number 1 to 12.
I got rid of even more work by grouping items using SOQL. This statement returns the Amount grouped by Year and Month instead of individual donations: GROUP BY CALENDAR_YEAR(npe01__Payment_Date__c), CALENDAR_MONTH(npe01__Payment_Date__c). This grouping does seem to close down the query and I have gotten timeouts on occasion. So if you’re doing this with thousands or more donations, you might want to test some optimizations.
So my completed SOQL for my year on year donation chart looks like this:
SELECT CALENDAR_YEAR(npe01__Payment_Date__c), CALENDAR_MONTH(npe01__Payment_Date__c), SUM(npe01__Payment_Amount__c) FROM npe01__OppPayment__c WHERE npe01__Paid__c = TRUE AND npe01__Payment_Date__c >=2014-01-01 GROUP BY CALENDAR_YEAR(npe01__Payment_Date__c), CALENDAR_MONTH(npe01__Payment_Date__c) ORDER BY CALENDAR_YEAR(npe01__Payment_Date__c), CALENDAR_MONTH(npe01__Payment_Date__c)
I know it looks scary but it’s built step by step. And you see the results right in your tab as you develop it. You could always leave the hard work to the spreadsheets and stay with the simple query I started with. You just end up with lots of data to crunch in your spreadsheet. No biggie!
Here is what the query looks like in CGonnector:
I don’t like exporting data to use from Salesforce. I have to run the report, export to XLS, then open Excel and copy/paste so I can then move it to Google Apps. And Salesforce puts in extra formatting that I have to remove. But GConnector can pull the data for any report right into the Google sheet for you! So that would mean no defining any SOQL at all if you want to go that route.
I have a grant writer. He doesn’t know our Salesforce installation. I have always updated his grants for him. But I quickly created this SOQL in a spreadsheet. The only thing new where is sorting by descending order.
Select ID, Name, Description, Notes__c, StageName, Type, Amount, Initial_Approach__c, Due_Date__c, Award_Date__c, CloseDate, Probability FROM Opportunity WHERE RecordTypeID = ‘012i0000000GxjA’ AND Probability !=0 AND Probability !=100 ORDER BY Probability DESC
And here is the amazing Spreadsheet tab formatted and ready to use. All I did was adjust column widths and turn on word wrapping. In order to push data back, you must have have the ID.
NOTE: I blurred the first couple of columns intentionally but you can still see the awesome automatic formatting created by GConnect
I shared the spreadsheet with him and asked him to update it. GConnect created an amazingly friendly tab for him to update. Picklists are pulldown lists! He updates it, and I look it over and then use GConnect to push back all the changes. That’s right! One SOQL button updates the grants, he updates, I check, and one button pushes all the changes back! Amazing amounts of time saved. And my grant writer doesn’t need a license (at least not for this purpose).
I also found it very convenient to update the status of my open tasks using the spreadsheet interface. This single SOQL gets everything open. I update what I like (using pulldown interfaces) and then push the button.
SELECT Id, Status, ActivityDate, Subject, Description, Type, CreatedDate, Assigned_To_Name__c FROM Task WHERE Status!=’Completed’ AND Assigned_To_Name__c =’Terry Cole’ ORDER BY ActivityDate NULLS LAST
This might be my new favorite way of updating tasks assigned to me in Salesforce.
You can have multiple SOQL queries in one spreadsheet. They can connect to multiple objects. So now I can graph totally unrelated items such as my custom financial object that has the budget compared to the sum of revenue.
You can get email alerts when the query doesn’t (or does) update. You can also get alerts when certain values cross thresholds. I haven’t used this but it seems like a nice way to get alerts on things that aren’t because data changed. Salesforce can only give you an alert based on a data change. Now I could get an alert because things don’t change.
You can connect your SOQL to the value in a spreadsheet. One can imagine pretty easily a volunteer sitting in front of a spreadsheet with some nicely formatted tab for querying some information. It goes to another tab. They update it or use it. No volunteer having to learn Salesforce!
GConnector is the latest app in my arsenal of great tools. I love Apsona for all types of bulk editing and importing structured data that I created onto one or multiple objects. I love People Import for importing data that I didn’t create for contacts or leads because it does such a good job of finding existing contacts and leads. I love Demand Tools for finding duplicates among my custom objects (duplicate volunteer hours, duplicate client notes) as well as the occasional hidden duplicate contacts or accounts. I also love it for data normalization of state, country, addresses, capitalization of first and last names, etc.