Salesforce with Google Sheets

Query Salesfoce from Google Sheets

This guide shows you how you can use the Fusebit Add-On For Google Sheets to import data from Salesforce into Google Sheets by running an arbitrary SOQL query.

802802

Import data from Salesforce to Google Sheets

Before you continue, you must complete the setup steps from Getting Started with Google Sheets and have a Google Spreadsheet configured and ready to use with the Fusebit Add-On. Follow this guide in sequence as sections build on one another.

Overview

In this guide, we will create a Node.js function that uses a Fusebit Connector for Salesforce to enable you to run any SOQL query in Salesforce and get the data into your spreadsheet using the FUSEBIT spreadsheet function provided by the Fusebit Add-On for Google Sheets.

We will start by adding a Fusebit Connector for Salesforce along with a data manipulation Snippet to the solution and authorizing access to your Salesforce instance. Then, we will use the Salesfoce snippet to implement a Node.js function that runs an arbitrary SOQL query passed to it from the Google Spreadsheet and returns the results to the spreadsheet.

When we are done, you will be able to run a SOQL query specified in your spreadsheet against your Salesforce instance and get the results back to your spreadsheet.

Add Snippets and Authorize Salesforce

In your Google Spreadsheet, go to Extensions | Fusebit | Open, click Edit, and in the Fusebit Editor that will open in a separate tab, select Snippets. In the Add Snippet dialog that shows up, type salesforce in the search field, and then choose Salesforce - Run query snippet:

26502650

Add "Salesforce - Run query" snippet in the Fusebit Editor

If you scroll down to the bottom of integration.js, you should see a new Node.js function added: sfdcRunQuery. We will be using it in the next section. You can read more about the Fusebit Connectors and Snippets here.

Back in the Fusebit Editor, click Run and follow the prompts to Authorize access to your Salesforce instance:

26502650

Authorize access to Salesfoce

You are now set up to start configuring your spreadsheet and writing some Node.js code.

Running SOQL Query in Salesforce

Set up a sheet in your Google Spreadsheet as follows:

15641564

Set up spreadsheet for running SOQL query in Salesforce

Using the Fusebit Add-On, you can write Node.js code to call any Salesforce API you want. In this guide, we demonstrate this capability by using the query API of Salesforce to run a SOQL query specified in Google Sheets. Cell B1 will hold the SOQL query to run. Take note of the checkbox in cell B2 - it is part of the mechanism that will allow you to refresh data on demand.

The results of a query can either be tabular data (in case of a collection of records), or an atomic value (e.g. the count of records). We will show those results starting in cell C1, but you can change the result projection later by making changes to the Node.js code.

With the spreadsheet set up, let's switch back to the Fusebit Editor tab. (If you closed it, you can always reopen it with Extensions | Fusebit | Open and then Edit). This is where we will implement a Node.js function that will use the Salesforce - Run query snippet we added previously. Locate the declaration of the googleSheetsFunctions object around line 9 in integration.js, and add a new property to it that defines and implements the Node.js function you will be able to call from Google Spreadsheets, and click Save:

sfdcQuery: async (ctx, data) => {
  // extract the SOQL query to run from input
  const soql = (Array.isArray(data) ? data[0][0] : data);
  if (typeof soql !== 'string') {
    throw new Error('The function expects SOQL query as input.');
  }
  // execute the SOQL query
  const response = await sfdcRunQuery(ctx, soql);
  // compute union of all attribute names in the returned record set
  let attributeNameUnion = {};
  response.records.forEach(record => attributeNameUnion = { 
    ...attributeNameUnion,
    ...record
  });
  delete attributeNameUnion.attributes;
  const attributeNames = Object.keys(attributeNameUnion).sort();
  // format the response to Google Sheets
  const result = attributeNames.length
    ? [
      attributeNames,
      ...response.records.map(record => attributeNames.map(attribute => record[attribute]))
    ]
    : [
      [ response.totalSize ]
    ];
  return result;
},

The function first processes the input data from the Google Spreadsheet to determine the SOQL query to run (which we will source from cell B1). Next, it calls the sfdcRunQuery snipped we previously added to fetch the results from Salesforce. Then, it computes the union of all attribute names found across all returned records and sorts them for the predictable presentation of results. Lastly, it formats the records obtained from Salesforce into a two-dimensional array that is returned back for rendering in the Google Spreadsheet. You can read more about the programming here.

With the code in place, let's go back to the spreadsheet and call the Node.js function we just implemented by adding =FUSEBIT(B1:B2,"sfdcQuery") in the C1 cell. The function evaluates by invoking the sfdcQuery Node.js function we've added in the Fusebit Editor, and the results are displayed in the spreadsheet. The function re-evaluates by calling the Node.js function again every time the input parameters change - either the SOQL query in cell B1, or the checkbox status in cell B2:

802802

Calling the Node.js function from Goggle Sheets to run SOQL query in Salesforce

Congratulations! You've just implemented a mechanism to run an arbitrary SOQL query specified in your Google Sheets and load the results back into your spreadsheet. You can refactor this mechanism in a variety of ways:

  • Change the type of the parameters passed to the FUSEBIT function to be less open-ended, for example by parameterizing the SOQL query specified in Node.js with variables passed in from Google Sheets, which may be more suitable for use by less technical spreadsheet users.
  • Change the projection of the issue by modifying the transformation of Linear issues into data returned to Google Sheets in the Node.js function.
  • Enhance, aggregate, or mash up the data returned from Salesforce using data from other services you can access using Fusebit Connectors or vanilla Node.js and NPM.

Did this page help you?