Winning reports with Google Sheets and G-Connector for Salesforce [Hacks]

I’ve recently asked by a team member to provide a list of people that combine Lead and Contact objects, filtered by a common denominator. While this sounds like a simple request - Salesforce does not provide a joint report of both Lead and Contact objects. There is one small exception to that rule, actually - if both leads and contacts are a member of the same SFDC campaign, you can create a report of the SFDC campaign members. This will, however, require creating a campaign and attaching the leads and contacts to it - doing this just for reporting sake, ‘won’t fly’ in all scenarios and businesses.

So how can we easily get an up to date list of both leads and contacts from a Salesforce report?

Easy! With Google Sheets and the G-Connector for Salesforce:

Step 1: Create your reports in SFDC.
You can create as many reports you would like - but as we wish to get them in a combined spreadsheet - use the same fields for all reports (if applicable).
Step 2: Get the G-Connector for Salesforce Add On
G-Connector for Salesforce is an add on by Xappex that allows you to query your SFDC instance from Google Sheets. After you install it, you need to connect it to your instance. This is rather straight forward as the add on will ask you to connect to your SFDC when you first open it via the Add-On menu in Google Sheets:

Connect your G-Connector first by going to the add-ons -> G-Connector for Salesforce

Step 3: Create a Google Sheet, and create a tab for each report you wish to connect to it.
G-Connector can query your SFDC instance in multiple ways, the easiest would be via 'Get Report(s)'. This, of course, require you to have a report ready :)
The G-Connector will draw data from your SFDC instance, on an interval of your choice. This way, the created sheets are always up to date!

Choose Get Reports, and choose from the menu the report you wish to add

You can either get the Report synced to your current sheet or create a new one

Step 4: Combine sheets.
Ok, so now we have multiple sheets - How do we merge them?
Natalia Sharashova from Ablebits has a great blog post on combining Google sheets. As our data is live and can change over time - the best bet here would be the 6th method - using Query formula.

We need to create an additional sheet, aside the sheets that contains our reports, and add the sheet headers (field names) to that sheet as well. After that, we can combine multiple sheets using the Query formula. It's recommended to have simple short names for the sheets we wish to combine. This is the formula I've used to combine the sheets in my case:

=QUERY({Leads!A2:F;Contacts!A2:F},"select * where Col1 <>''")

That’s It. You now have a google sheet that combines multiple reports from SFDC and updates over time. It’s also worth noting that Xappex also have a Google Data Studio connector for SFDC, which might be useful for Dashboarding purposes.