Using Google Sheets To Track Your Partner Enablement Business: Syncing Contact Data

I come from a low-code background, and when I joined OutSystems the Low-Code space was still relatively new. However, now every platform offers a version of Low-Code, as well as AI-assisted tools, so anyone can build the technology and dashboards they need to manage their business.

In the Channel Enablement space, most of my work revolves around creating enablement paths and programs to enable our partners to sell and deliver Contrast Security . And that program needs to be measured continuously, to ensure that we're targeting the right people, that partners meet their enablement and certification goals, etc. Mostly it means that I need to both supercharge typical CRM functions to

In the past, if I wanted to use Salesforce to track my partner enablement, I'd have to request a number of custom fields. This causes major headaches for Sales Ops, and over time these custom fields result in broken Apex code, broken relationships over upgrades, and just do not work with newer features of Salesforce. But low-code has taught me that if you're looking to augment and enhance data in one system, without altering its underlying structure, the best way to do it is to sync it from that system, into another, and decorate it there. That's exactly what I do today.

Is this simple? Oh no, just the opposite. However, 谷歌 Sheets is my favorite tool for this, since I need to roll-up metrics, get charts on progress, and check boxes on completion. I also needed a low-code tool which had an API to connect and join OTHER data, as well as something that allowed me to code new functionality. And last but not least, it had to be secure and IT approved. Not only is Google Sheets my favorite tool for this, it's the only one that will work in my situation, and most organizations use Google Workspace today and have approved the use of Google Sheets, Confluence, Salesforce, and these connectors, so my methods will probably work for you, too! Since I've had a lot of success building and using these tools I would start a new series breaking down exactly how I manage my channel partner business by supercharging Google Sheets.

Let's start this new series off by detailing a simple use case of using the Salesforce Connector to create a bi-directional link between your sheet and Salesforce. A simple and effective use case (which we'll build on later) is adding contacts to your Salesforce Partner Accounts. In the channel business, when we onboard a new partner, we usually exchange contact information for upload to our CRM. And that data comes in MANY formats, it must be sanitized, and then uploaded. Keeping the CRM updated will be essential for our other tracking and automations, so let's start here.

First, you'll want to add the Salesforce Connector for Google Sheets Extension. You can do this by selecting Extensions -> Add-ons -> Get add-ons:

Search for "salesforce connector" and look for the one made by Google. Since it's made by Google, it will be easy to audit and trust for security:

If you're not allowed to install it, request it from your IT department. It may also require additional permissions (or else it remains greyed out), so make sure you work with IT to add it.

Let's start with a sample spreadsheet I might receive from a partner. It can come in many formats, this one is a particularly good one, it's got lots of data about the employees:

Now we'll start by importing all the existing contacts under the partner record. This will give us a baseline of what the expected format will be for the records to import. Make a new tab, and this time name it "Merged Contacts" Then open up the Salesforce Extension and select "Import".

You'll need to use a custom SOQL query (Salesforce Object Query Language) to bring the data in. I like to use this one:

SELECT 
AccountId,
Account.Name,
Id,
FirstName,
LastName,
Email,
Phone,
Title,
Department,
MailingStreet,
MailingCity,
MailingState,
MailingPostalCode,
MailingCountry
FROM Contact
WHERE Account.Name LIKE 'Partner Account Name%'        

Make sure to replace the Partner Account Name part with the name of the Partner Account Record.

You will get all the contacts back in your spreadsheet. In this case, we only have one record added to the Partner, so this import will greatly add to the contacts.

Notice it's also missing some crucial details - for example, we send out automated mails for enablement based on country, and so this contact is missing some details we could add. Also notice some crucial info - the id of the Account this contact is related to and the id of the contact is included. This will be important when we update records.

For now, we have to massage the data into this format. So I'm going to shape this sheet's data into this format, which is our target format.

As you can see, I will unmerge a number of cells, rearrange some columns, remove some extraneous rows and columns, add placeholders for blank columns, so that I can copy/paste this info into the Merged Contacts Sheet. After some massaging, my original sheet now looks like this:

Note, there are a few things you need to pay careful attention to:

1. Salesforce is particular about zip codes - they must have 5 digits. When you paste a zip code into a spreadsheet, Google Sheets likes to remove leading zeros - 04563 becomes 4563. Make sure you change the format of this column to text only so your leading zero is present
2. Salesforce only imports valid state names - long names, not abbreviations. You'll need to replace these with the full, correctly spelled names of the states before import
3. If you're adding the country, this needs to be a correctly spelled, valid country in SF, so pay attention to what is in this column:

Copy/paste these into your "Merged Contacts" sheet that you used to import the existing contacts, it should fit perfectly. Create a filter at the top row, and sort on First Name, A-Z. This will allow you to find duplicates and eliminate them prior to import. Make sure you add / change anything you would like with the existing contacts, as you'll be able to update them back to Salesforce. After you've eliminated the duplicates, it now looks like this:

Notice only one contact has an id, because only one existed before. We'll start with the import. First, filter on the Id, column C, on Blanks only. Then select all the underlying items from A2->N22, and open the Extension again, and select Update:

Ensure that the right range of cells is specified, the Contact type is selected, and that Insert is your mode. Scroll down, it will pick the next column over to put the results, which is fine. Click Next:

On the next screen, it will map the fields in the sheet to the appropriate object fields. Since you already did an import, it should do a great job of mapping the fields appropriately. Click EXECUTE. It will ask you if you are ready to insert these contacts, select Yes:

This may take some time. In the end, you should get a Success if it goes well:

If you have any errors, read the message carefully. You can continue to try and import again by using our filters. Filter out the Successes and reimport the remaining contacts.

Next, let's update our existing contacts. Using our filters, filter your contacts until just the previous contacts are shown. Select these fields, and select Update again. This time, select Update, and ensure the Id field (field C) is mapped correctly:

This looks exactly the same, the fields should be mapped correctly. Click Execute, and confirm:

If it all goes well, the status should say "Updated":

Finally, let's confirm the contacts with a report in Salesforce:

We had 21 total contacts, and the report shows we have 21 total contacts. You'll also notice our one contact, Bobbi Brown, now has an updated country:

We've now successfully imported our contact sheets, and we can now use our Salesforce CRM as our source of truth.

Stay tuned for future editions, where I show you how I add the partner enablement tracking info and publish it to my team! And if you haven't already subscribed to this newsletter, please do, because I do these every month!