Connect And Schedule Reports with Google Sheet

Definition 

By using Data Connector for salesforce, we can easily obtain records of Salesforce reports to Google sheet. We can additionally insert, delete, update, and refresh the Salesforce data from the google sheet itself. We can also import Salesforce data based on a custom SOQL Query. 

Prerequisites 

• Salesforce org (Developer edition, Sandbox) 
• Google spreadsheet with connected google account. 

Data Connector for Salesforce 

It is the google add-on developed by Google cloud. Add-ons are scripts that are executed on Google Sheets, Docs, Slides. At any time, we can turn it on or off. Users can also edit the data on the worksheets to push the changes into Salesforce. 

Installation of Data Connector for Salesforce 

1. Sign into your Gmail and click on the app launcher. Select Sheets. 

2. Click the Blank sheet. 

3. Click Extension — > Add-ons — > Get Add-ons 

4. In the search bar, search for Salesforce Connector. Choose the highlighted tile. 

5. Click Install — >Continue 

6. Choose the connected Google account. Click allow to grant permission. 

7. Now, Salesforce Connector has been installed. Click Done 

8. Once installed, you can see this Salesforce connector under the Extension tab. Navigate to that tab. 

9. Click Authorize 

10. Login with your Salesforce org credentials — > Click Allow Access 

11.  Data connector for Salesforce is successfully Connected. Close this page. 

Fetching Reports from Salesforce to Google Spread Sheet 

1. Click on Reports and search for specified report name and click Get Data 

2. After clicking Get Data, you can get the data from the Salesforce report to the spread sheet. 

3. Click Refresh from Menu bar. 

There are two ways you can update Google Sheet with Salesforce data: 

• Manual Refresh 
• Auto Refresh 

      Manual Refresh:  

              This allows you to instantly update data in a single click. 

      Auto Refresh:  

This allows you to set a timeline to update the data after certain times. You can opt for a frequency of 4, 8 or 24 hours. 

4. Click Auto Refresh. Choose the sheet name and enter the refresh interval hours and click create. 

Other Data connector Operations 

• Reports 

• Import 
• Update 
• Delete 
• Refresh 

Example: To import records, we can import the data from salesforce using custom SOQL query. Click — > Import — >Select Object — > Select field — > Click Get Data  

Once the “Get Data” button is clicked, you can view the records in the Google sheets returned from Salesforce. 

Protect the Google sheet from other users 

For security purposes, we can restrict the google sheet in the ‘View only’ mode for others. 

1. In google Sheet, Click Data –> Protected sheets and ranges. 

2. Click Add a sheet or range.  

3. Click Range tab and choose your google sheet name.  

4. Click Set Permission. 

5. Choose Only you in in Restrict and click Done. Now, the allowed user only can edit the sheet, while others can only view the sheet. 

References: 

https://workspace.google.com/marketplace/app/salesforce_connector/857627895310

https://help.salesforce.com/s/articleView?id=sf.rd_reports_export_connect.htm&type=5

https://help.salesforce.com/s/articleView?id=sf.rd_reports_overview.htm&type=5