The arsenal of tools at a Salesforce user’s disposal continues to grow. Data management tools sit at the unique intersection of address business users, developers, and administrators. Arguably, there is no one-size-fits-all tool to address every need; sometimes your data set is huge (Einstein Analytics), sometimes it is complex or needs structuring (Mulesoft), sometimes Salesforce reports and dashboards are perfect, and sometimes it demands a human-scale approachable to perform ad hoc reviews, thrash filters, and, inevitably, send a spreadsheet to someone.
Enter Data Connector for Salesforce (let’s call it DC4SF from here, because I’m a slow typer), my new favorite tool that enables data management and analysis of your Salesforce org via Google Sheets. Over the past few months, I’ve “forced” myself to use it as much as possible (which really was not a chore). Again, it’s not a panacea. But as a business owner and technologist, it supports a lot of my day-to-day behaviors as I straddle development, configuration, and business operations duties as Red Argyle. DC4SF is published by the fine folks at Google Cloud, so it’s got some big brains supporting it.
How to Get It
DC4SF is a Google Sheets add-on, which implies you have access to Google Sheets. Depending on your organization, add-ons may be installable by anyone, or they may need to be (easily) whitelisted by a Google Workspace administrator. If the former, visit the add-on and click “install”. If the latter, bribe your administrator with cookies and flowers, and you will (eventually) see it appear in the “Add-ons” menu in Google Sheets.
Once installed, the first step is to connect to your org. Note: DC4SF will remember your connection over time. Keep this in mind if you work in a consultancy (like Red Argyle) or you live in sandboxes. If in doubt, check the “Connection Information” menu for the Salesforce org DC4SF is currently connected, and click disconnect to change connections.
Now Get Some Data
You can import into your DC4SF-enabled Google Sheet from a Salesforce report or from a query (via object and field selection, or custom SOQL code). Let’s take a look at each.
Data from Reports
With DC4SF, you can import data into your spreadsheet from a Salesforce report. I’ve found this feature to be useful from a mindset of setting up accurate “source data reports” inside Salesforce that I pull data from regularly (if Salesforce is the source of your data, then it makes sense that a Salesforce report is the source of your filtered data). For example, I have a cash flow projection spreadsheet that uses invoicing data in Salesforce along with historic expense data from Quickbooks. I use DC4SF to pull the invoicing data from a Salesforce report to ensure that the filters are always correct and the data is always up-to-date.can do the work I need to do to process Quickbooks data and begin simulating possible future projection outcomes.
Data from Queries
DC4SF allows you to construct a query to pull data by choosing an object, fields, sorts and filters. You can also write your own custom SOQL query. If you’ve used Salesforce’s Data Loader tool (read about the comparisons further down), then this functionality will be pretty familiar and comfortable. I find myself using the query functionality to do more exploratory and ad hoc data analysis. Often, when I’ve landed on something useful, I’ll go make a report to codify the intention of the data, then use that report as a data source going forward.
One excellent feature of query imports is aggregate query support. Likely most-familiar to developers, this means you can return aggregate data quickly (ex. “sum of opportunities by month for the last two years”) rather than querying the raw data and using pivot tables or the like in your spreadsheet. Philosophical arguments about what approach is better aside, DC4SF gives you a lot of flexibility to approach accessing your Salesforce data however it will best serve you.
Refresh Data: The Secret Sauce
Remember all those reports and queries you’re using to import data? DC4SF has this great feature that can refresh said reports and queries on-demand or even on a schedule.
To manually refresh, navigate to a tab in your spreadsheet that was previously populated by a report or query, access the DC4SF add-on, choose the “Refresh” operation, then click “Refresh” under the “Manual Refresh” section.
To schedule refreshes every 4, 8, or 24 hours, take the same steps. Then click “Create” under the “Auto Refresh” section, pick a tab, and create the scheduled refresh.
I really like the workflow that refreshes set up for me, as it turns my spreadsheet into a living workbook for the analysis I need to do. I admit, most of my refreshes are done manually, but I’m coming around to letting the robots auto refresh my data more and more.
Now Give Some Data
We’ve talked about using DC4SF to get data out of Salesforce into your GSheet. What about inserting or updating data? Yep, you can do that too.
Inserting Data
DC4SF can insert data into Salesforce from any GSheet. DC4SF always operates in the context of selected cells on the sheet. So, select the columns and rows you want to insert, then access the DC4SF add-on to get started. Select the “Update” operation (a bit of a misnomer because you can also insert data with it), and DC4SF will walk you through steps to select an object, the operation to perform (insert, update, or upsert), and a “results columns”, which is where DC4SF will output the success or error message related to each row that is inserted. Finally, DC4SF lets you map the fields in your selection to the fields in the object you’re inserting. It does a decent job matching columns-to-fields by name, but always double check your field mappings to avoid any unintended consequences.
The speed of the import will vary depending on the size of your data set. I have yet to experience any timeout errors.
Updating Data
DC4SF updates data with the same steps as the inserting steps above, but it expects that you have a Salesforce Id field present in your data set.
By combining the report (or query) and update operations, you can turn your spreadsheet into a “list view on steroids”, extracting data from Salesforce, making updates to multiple records, then pushing those changes back into Salesforce with the Update function. I select as many (or as few) rows as a need to update, too. Just make sure you query the “Id” field to map the changes to the right records. I’ve gotten to the point where I prefer this approach to list views with editable rows in many cases.
Upserting Data?
Not exactly. DC4SF has a “Insert or Update” function but, as best as I can tell, it does not allow you to map an External Id field to perform an upsert as you may be accustomed to in other apps., look for the presence or absence of a value in the “Id” column you’ve mapped and use that to decide if an insert vs. update should happen for the given row.
Day to Day Spreadsheeting
We use Salesforce for just about everything related to sales, marketing, and professional services. In my world, I wade through a lot of project management, time accounting, and invoicing data to understand Argyler productivity, project profitability, and company financial performance. We sling some data in from Quickbooks to help with some of this…
I have “one spreadsheet to rule them all” that we share quarterly with our advisors. They need a spreadsheet, not a Salesforce report. Enter DC4SF: I have multiple tabs, each connected to one Salesforce report that pulls in relevant data. When it’s time to send the report, I step through each tab and use DC4SF’s “refresh” function. The top sheet updates with relevant sales (bookings and pipeline), financial (costs and margins), and productivity (Argyler count and utilization) data. Boom. Done.
Versus Data Loader
Salesforce Data Loader has long been a staple of data management for Salesforce orgs. I’ve trusted it for years. For me, the biggest challenge with Data Loader is file management; I need to create and store CSV files somewhere on my device, worry about proper CSV file formatting, and generally shuffle a bunch of files around. All the CSV file management goes away with DC4SF because you’re already working in a spreadsheet. I can add more tabs to the spreadsheet to work with different data sets, and I can flip between them to review much more easily. DC4SF also seems to be more forgiving when writing custom SOQL queries. Whereas Data Loader just fails and forces you back to step one if things aren’t perfect. Finally, Data Loader has become harder to install lately (at least on a Mac, requiring Java versions and admin-level access to devices). DC4SF is all browser-based, making it much more approachable.
Security Sidebar
The weakest link in security is often human behavior, not technical vulnerabilities (as of writing this, I know of no technical vulnerabilities with the Data Connector for Salesforce). Whatever tool you use to import, migrate, or analyze Salesforce data outside of Salesforce, remember the power that data management tools provide: fast access to large volumes of (potentially sensitive) data, often with that data resting in a non-Salesforce environment for a period of time. Treat your data with care, check your organization’s Salesforce governance plan (your organization has one of those, right?), and chat with your friendly neighbor compliance officer if you have any concerns, then get back to work with confidence!
In Conclusion
Yes, I’m a fan of DC4SF. It’s changed a lot of my day-to-day interactions with Red Argyle’s Salesforce data for the better. I feel more nimble in a DC4SF-enabled GSheet than hucking CSV files around, The steps to perform imports and exports of data feel very natural alongside the other work I’m performing in a spreadsheet full of data.
DC4SF is the right tool for the right job: human-scale data analysis and management of Salesforce data. Big-data analysis is better served by the likes of Einstein Analytics or Tableau. Robust integrations demand MuleSoft or other middleware. DC4SF doesn’t replace the use of Salesforce reports and dashboards. But if you need a workspace to push and pull Salesforce data and use it for specific purposes, DC4SF in GSheet is a solid tool to support you.
Are you a DC4SF user? How has it made your life easier?
Want to learn more about DC4SF and how it might streamline your Salesforce data management work? Drop us a line and let’s chat.
