Cleaning and transforming data is time-consuming, even for the most seasoned professionals. It becomes even more complex and time-consuming without coding skills, which requires significant manual effort – or involving the Data Team, further delaying time to insight.
But what if there was a more efficient way to clean your data? A method that could simplify the process, save time, and allow you to push your cleaned data back to your CRM–all within your spreadsheet?
This blog will demonstrate how to use Coefficient and AI to streamline your data-cleaning process within Google Sheets. You’ll learn how to transform and upload your data directly into Salesforce.
Step-by-Step Guide: Clean and Update Salesforce Data in Google Sheets
Coefficient is a free Google Sheets add-on that allows you to connect your business systems to Google Sheets and sync real-time data into your spreadsheet.
Before you start cleaning your data, install Coefficient.
Open Google Sheets. Click Extensions in the top ribbon and select ‘Add-ons’ -> ‘Get add-ons.’
Input “Coefficient” in the Google Workspace Marketplace search menu and select the Coefficient app.
Click ‘Allow’ to grant Coefficient access to your Google account.
Wait a few moments for the installation to complete, then return to your spreadsheet menu.
Now we’re ready to start cleaning our dataset.
Wondering how to export your Salesforce data to Google Sheets? Click the link for a full tutorial.
In this example, we wish to separate the ‘Full Name’ column into ‘First Name’ and ‘Last Name’ and change the email addresses into a more uniform format.
Start by opening Coefficient and select ‘GPT Copilot.’
Click ‘Formula Builder.’
Describe the formula you want the formula builder to create: “Write a formula that splits the first and last name into two separate columns from a full name in cell A2.”
Click ‘Build.’
Copy the output and paste it into the empy cell.
Drag the cell down the column to apply it to each row.
Next, let’s clean up the email addresses using the ‘GPTX Edit’ function.
Type in the following formula in the empty cell in column E:
=GPTX_EDIT(D2:D11,”Remove spaces and return all in lowercase letters”
Hit enter on your keyboard to apply the function to the entire column.
Now that the data is clean, it’s time to upload it to Salesforce.
Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.
To open Coefficient, click on Extensions -> Coefficient -> Launch.
Select ‘Export to…’
Select ‘Salesforce.’
Select the tab and header row. In this example, Row 1.
Click ‘Next.’
Choose ‘Contact’ as your object from the dropdown.
Select ‘Insert’ from the action dropdown and click next.
Verify your fields are mapped properly and click ‘Next.’ In this example, we have First Name, Last Name, and Cleaned Email.
Click ‘Export.’
Select ‘All rows on sheet’ and click next.
Confirm the data you wish to export by clicking the ‘Insert 10 rows to Salesforce’ button.
And in just few clicks, you’ve used Coefficient and GPT in sheets to clean your data and export it to Salesforce!
Clean Your Data in No Time With Coefficient and GPT
Coefficient and ChatGPT functionality inside of spreadsheets makes data management easier than ever before. Not only does it allow you to sync live Salesforce data to Google Sheets, but it also closes the loop enabling you to update Salesforce (or other CRM like HubSpot) directly from your spreadsheet, creating a seamless, bidirectional data flow.
Get started for free today to discover how Coefficient can streamline your analyses.
