Importing Data from Salesforce.com into Google Sheets using SOQL

To get any data from Salesforce.com into your Google Spreadsheet using SOQL (Salesforce Object Query Language), click Get Data Using SOQL on the G-Connector for Salesforce add-on menu or Launch on theG-Connector for Salesforce add-on menu to display the side panel and then click Import Data -> SOQL query:

The following dialog box will be displayed:

One important thing to remember when using the Get Data dialog is that it will run any text entered in the big text box as a SOQL query against the Salesforce database you’re currently logged in to. Essentially, you don’t need to use the rest of the controls in this dialog box at all, as long as you can craft a syntactically correct SOQL query. If you’re experiencing any troubles with SOQL, please refer to Salesforce’s documentation.

All other controls in the Get Data dialog are there to help you build a correct query providing you a view into your Force.com schema and making sure you’re using the correct object names, field names, and pick list values. These controls are called ‘query builder controls’.

You can use the query builder controls to put together a simple query with one filter. However, if you need to have more filters or want to use subqueries – you’ll need to type them in. Here are just a couple of query examples that you won’t be able to build using query builder controls, but can still use in the query box to retrieve data into your spreadsheet:

• Select Id, Name, (Select Id, Name From Opportunities) From Account
• Select Id, Name, Account.Id, Account.Name, Account.Owner.Name From Opportunity
• Select Id, Name From Account Where Id IN (Select AccountId From Opportunity Where Owner.Name = ‘John Snow’)

You can also reference existing values in your spreadsheet by specifying the cell address in square brackets, for example:

SELECT Id, Name FROM Opportunity WHERE Owner.Name = [Sheet1!C15]

or

SELECT Id, Name, StageName FROM Opportunity WHERE Id IN [Sheet1!A2:A20]

Explanation of the dialog box’s user interface

• Display labels
  This box determines if object and field API names or labels will be displayed in the dialog box.
• Create drop-downs in picklist columns
  If checked, we’ll add a drop-down into each cell of a column coming from a ‘picklist’ field in Salesforce as follows:
  
• Put to: New sheet(s) or Active Sheet
  This option indicates whether the exported data will be placed into the currently active sheet or a new sheet will be created for it. By default this option is New sheet(s) when running an SOQL query for the first time, and Active sheet if running a query from a sheet where data has been previously downloaded.
• Query all
  If this box is checked, the query will retrieve deleted and archived records, similarly to specifying the ALL ROWS keyword in the query text.
• Start cell
  This option will let you determine the starting point for the exported data. It indicates the top leftmost corner of the data when exported to a Google sheet. There can be only one SOQL run into one given tab.
• Freeze header row
  If on, the header row will be frozen after the pull to allow for data scrolling while keeping the headers visible.
• Auto-fill formulas
  Set this option if you have additional formula columns to the right of your data and would like them to expand/contract as the number of rows in the exported data changes on each refresh.
• Conditions
  Provides point-and-click interface to define one or more filters for the query.
• On refresh: Overwrite, Append, Create new sheet
  This drop-down determines the behavior of the exported report when it is refreshed using the Refresh current sheet (all sheets) G-Connector command or the refresh performed by the automatic scheduler. The following options are available:
  – Overwrite: the default mode. Will overwrite the data in the sheet with newly exported data. This operation will clean up any remaining data (if the exported data is smaller than the one already in the sheet).
  – Append: in this mode, each refresh will append all export data at the end of the previously exported data. Having duplicates after the repeated refreshes is highly likely in this mode. Therefore, it’s up to you to make sure the report returns new data or has a DateTime column that is different in all rows to be able to analyze the data by.
  – Create new sheet: in this mode, a new timestamped tab will be created for each report refresh. This mode is good if you want to keep snapshots of your data in the same Google sheet. Keeping snapshots of your data in different Google Sheets is achieved using G-Connector’s snapshotting functionality.