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. 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]’
SELECT Id, Name, StageName FROM Opportunity WHERE Id IN [Sheet1!A2:A20]