Back to pre-built integrations

Google Sheets to Salesforce integration

Office productivity tools like Google Sheets are vital for employees' daily tasks, including capturing customer and sales data. This data must be pumped into Salesforce to maintain up-to-date records, often requiring validation and cleansing due to the ad-hoc nature of entering data. Ballerina can streamline this process by fetching data from these tools, performing necessary data processing, and pushing updates to Salesforce, either periodically or by listening to changes.

The example below demonstrates an integration scenario in which contact details added to Google Sheets get synced with the Salesforce Contacts.

import ballerina/log;
import ballerinax/googleapis.sheets;
import ballerinax/salesforce as sf;

public type Contact record {|
    string Id;
    string Email;
|};

const int HEADINGS_ROW = 1;

// Google sheets configuration parameters
configurable string spreadsheetId = ?;
configurable string worksheetName = ?;
configurable string duplicateWorksheetName = ?;
configurable string sheetsAccessToken = ?;

// Salesforce configuration parameters
configurable string salesforceAccessToken = ?;
configurable string salesforceBaseUrl = ?;

sheets:Client sheets = check new ({auth: {token: sheetsAccessToken}});

sf:Client salesforce = check new ({
    baseUrl: salesforceBaseUrl,
    auth: {
        token: salesforceAccessToken
    }
});

public function main() returns error? {
    sheets:Range range = check sheets->getRange(spreadsheetId, worksheetName, "A1:G");
    (int|string|decimal)[] headers = range.values[0];
    foreach (int|string|decimal)[] item in range.values.slice(HEADINGS_ROW) {
        int? indexOfEmail = headers.indexOf("Email");
        if indexOfEmail is () {
            return error("Email column not found");
        }
        stream<Contact, error?> retrievedStream = check salesforce->query(
            string `SELECT Id, Email FROM Contact WHERE Email='${item[indexOfEmail]}'`);
        if retrievedStream.next() !is () {
            log:printInfo(string `Contact already exists. Email : ${item[indexOfEmail]}`);
            _ = check sheets->appendValue(spreadsheetId, item, {sheetName: duplicateWorksheetName});
            continue;
        }
        record {} newContact = map from int index in 0 ..< headers.length()
            let int|string|decimal header = headers[index]
            select [header.toString(), item[index]];
        _ = check salesforce->create("Contact", newContact);
        log:printInfo(string `Contact created successfully!. Email : ${item[indexOfEmail]}`);
    }
}