How to mass update Salesforce contacts, fields, and objects from google sheets

Overview

Salesforce provides a handful of ways to update data within their system — from Record Pages and List Views to Data Loader.

However, while making most changes in the system is pretty straightforward, bulk jobs or jobs with complex logic can get tedious.

Bulk jobs also require special access and are usually on company-owned computers that may need IT or management authorization.

Enter Coefficient, a one-stop add-on for exporting and updating data from Salesforce and other tools. It requires little to no setup and adds the power of Google Sheets formulas, which are sorely missing from Salesforce.

Read on to learn how to update your Salesforce data from Google Sheets seamlessly and how using Coefficient can streamline the process.

Why would someone want to update in mass or just a specific object?

Modern sales and marketing teams work with various solutions for everything — from hosting, infrastructure, and online marketing to payment and subscription management.

However, while some tools integrate directly with Salesforce, many don’t, making it challenging to load data from the sources.

There is also this awkward middle ground where data might have changes that can take a long time to do in list views in Salesforce, but not long enough to warrant breaking out Data Loader.

Additionally, some instances don’t require much work, with only 20 or 30 records needing a field update, but the necessary logic to determine the value is complex. Also, that said number of records is not big enough to ensure a batch job in Salesforce.

These are some of the gaps that Coefficient is designed to close.

Benefits of Coefficient vs other options

As mentioned before, Salesforce provides many ways to load data. The downside is that Salesforce’s solutions tend to cover the extremes while missing some important areas.

Below is a quick breakdown of Salesforce’s data loading options and their pros and cons.

Salesforce Record Pages

• Easy to update
• Within the Salesforce interface

• Takes a long time to make multiple changes
• No intelligent features (you have to know the value you want to add)
• No snapshots

Salesforce List Views

• Easy to update
• Allows you to view and update multiple records at once
• Within the Salesforce interface
• Requires time to enter multiple changes (but less than record pages)
• No intelligent features
• No snapshots

Data Loader

• Allows updating enormous quantities of records
• Offers a configuration to accommodate org limits (bulk and batch API support)
• Can be automated
• Supported by Salesforce
• Has the ability to do batch loads and configure limits to keep them within org processing limits
• Can pull from SQL server
• Exporting snapshots are possible but it requires external automation
• Non-viable for smaller update quantities
• Requires application installation
• Can be a massive pain to automate
• The interface can be a bit awkward to use
• Smart values have to come from another tool, such as Excel or SQL scripts
• Requires batch data load permissions
• Requires SOQL, XML, Java, SQL, and Batch/Powershell to automate

Dataloader.io

• Does not require an application to install
• Relatively easy to use
• Well-supported
• Has configuration to accommodate org limits (bulk and batch API support)
• Has no export snapshots
• Requires additional cost for over 10,000 record updates per month
• No smart updates
• Not good for small or large updates

Want to see a better alternative? Check this out.

Coefficient.io

• Quick add-on installation to Google Sheets
• Extremely simple to use
• Supports snapshotting exports
• Smart updates (calculate the value within Google Sheets and update directly)
• Google Sheets smarts and graphing
• Integrates with other applications for easy data loading
• Allows exporting and updating from existing reports
• Freemium
• Only for Google Sheets

Steps

We’ll go over the steps to update your Salesforce data on Google Sheets using Coefficient.

Step 1: Set up your Salesforce report

Let’s say your sales and marketing teams want you to upload data to Salesforce for Telephone Consumer Protection Act (TCPA) compliance.

They have an existing export file containing a list of emails and data on whether the customer has opted out of phone calls or emails and requested information from your company (allowing you to reach back out to them).

You could do this manually using Dataloader, but you’re better off showing your teams how to do data loading themselves to streamline their workflows.

Start by setting up the Salesforce data. Create a simple contacts report including only the relevant data points, such as the Email, Contact ID, Do not call, Email opt-out, and TCPA authorized.

Step 2: Pull your report into Google Sheets

After installing Coefficient on Google Sheets, launch the app by clicking Add-ons on the menu. Hover over Coefficient: Salesforce and HubSpot Data Connector from the dropdown and click Launch.

On the Coefficient sidebar displayed on the right-hand panel of the Google Sheets interface, select Salesforce as your data source, then click Import from report.

We’re selecting the Import from report option since we already isolated a dataset by creating a report inside Salesforce. Alternatively, you can import information from objects and select your requirements.

Select the sample report and click Import Report.

Your data should immediately populate your Google spreadsheet:

Step 3: Import data from your sales and marketing

Import the dataset, the file containing a list of emails and opt-in (and opt-out) data, through the File menu option, and click Import.

You can either select the document from your Google Drive or upload a new one by navigating to the Upload tab and dragging and dropping your new file.

Your data should be imported to your active spreadsheet.

Go back to your Salesforce import. Use VLOOKUP to match your Salesforce records to the marketing dataset by way of the email address on the contact record:

=VLOOKUP($A3,‘All Contact Emails’!$A3:$D,2,FALSE)

The VLOOKUP formula above consists of four parts.

First is the value you want to use as your key in the other table. For instance, the value you want to match between this record and any available record in the table you want to return data from.

The second is the table that contains both the lookup value and the return value. This is on our imported dataset.

The third value is the column number containing the value you want to return:

The final value shows whether the values are sorted. If you choose TRUE or do not enter a value, then the lookup will stop and return the value before the first one that is mathematically greater than your lookup value in the first input.

In Excel, this is referred to as Exact Match, which is a little easier to understand. Enter FALSE for the last variable.

You’ve now overwritten the blank TCPA and contact fields with the values your marketing and sales teams requested.

The two contact records below that are blank and show errors are contacts with no email, so no match was returned. We’ll filter these out when we update our records in Salesforce.

Step 4: Push your data from Google Sheets to Salesforce

Now that you made appropriate changes, see how easy it is to export your work back to Salesforce using Coefficient.

Launch the Coefficient add-on again.

On the Coefficient sidebar, click Push to Salesforce.

Select Update from the dropdown menu since we are updating an existing Salesforce record. Choose Contact as your Salesforce Object since all the work that we did is within contacts.

Map the fields the importer will recognize and apply changes accordingly. In this case, the data starts on row number two, our Contact ID lives in column B, and the starting column for results is F.

Remember that some of these mappings will come pre-filled with suggestions based on the datasheet you’re trying to push to Salesforce.

Ensure you select all the rows and columns you’re pushing to Salesforce. Deselect any invalid records, such as the two contacts with no email we mentioned earlier.

When you’re done, click Update selected rows to Salesforce.

Check the confirmation prompt to ensure the selected number of rows matches the data you’re pushing, then click Yes, Update.

When it’s done, you will receive a confirmation sheet containing the Record ID, result, and timestamp showing when the row was updated.

You should see the changes appearing on the Salesforce report we created in Step 1.

Follow the same process to update your other Salesforce reports or objects and fields from Google Sheets.

Streamline updating your Salesforce data from Google Sheets

Updating Salesforce data is often a labor-intensive and time-consuming process, but with Coefficient’s robust data connector features, processes become smooth and fast.

The app’s easy-to-use yet powerful features take away the complexities and time-consuming aspects of updating your Salesforce data from Google Sheets. Set up your required datasets, link them to Google Sheets, filter and group your data, and import (or export) everything with a few clicks.

With this, you can speed up your sales and marketing workflows and make data analysis and reporting more efficient.

Updating your linked Google spreadsheet data is also a breeze with Coefficient’s auto-refresh feature.

Set up an hourly, daily, or weekly refresh schedule and Coefficient will automatically sync new information or any changes from your data source with your Google Sheets dataset.

It’s highly convenient and saves you from repeating your data imports, and in turn, your analyses and reports.

Do you want to ensure everyone stays in the loop on any changes and updates to your Salesforce data on Google Sheets? Coefficient’s got you covered by letting you set automated alerts that trigger based on specific conditions you configure.

The alert types include daily or weekly screenshots of a specific Google sheet or ranges and notifications when there are changes or new rows to your data.

You can set the frequency and send the alerts via email or Slack, which is a great way to weave in your sales and marketing data tracking to your workflows seamlessly.

Eliminate manual processes and automate critical parts of Salesforce data updating from Google Sheets with Coefficient.

Doing so allows you to free up your and your team’s time and resources so you can focus on more crucial aspects of your operations.

With the combined power of Coefficient and Google Sheets, you’ll have a more efficient way of updating and handling your Salesforce data.

Try Coefficient for free now!