Use Case – Salesforce REST Connector + Power Bi connector

This page describes a connector scenario by integrating the Salesforce REST Connector with the Power Bi connector. For example you can use the Salesforce REST Connector to get the account details (Eg :- Id, Name, Account type of the records in your organization) from Salesforce and add that account details into table in the dataset of Power Bi using addRows operation in the Power Bi connector.

1. Get the account details from the Salesforce using query operation.
2. Add the account details into the Power Bi using the addRows operation.

Environment setup for running the above use cases

• Download ESB 5.0.0 from here.
• Upload the following connectors to the ESB:
  • salesforcerest-connector-1.0.2
  • powerbi-connector-1.0.1
• Follow the instructions given in the developer guide of the above connectors.
•  Import the certificate into the ESB client keystore to use the Salesforce REST connector. Refer this link to import the certificate.  
• Creating the credentials to use the Power Bi Connector:
1. Create a Office 365 E3 trial account.
2. Navigate to the URL https://login.microsoftonline.com/login.srf and login with Microsoft account which is created in step 1 .
3. Navigate to the URL https://powerbi.microsoft.com/en-us/ and signup for a Power Bi account using the previously created Microsoft account.
4. Create a dataset and keep the dataset name for further use.
5. Enable PowerBI pro by using the popup by selecting the "Create Group" option.
6. Create a group in PowerBI.
7. Navigate to the URL https://app.powerbi.com/apps.
          Step 1 - Login using Power BI account which used in step 2 .
          Step 2 - Provide App Name, Redirect URL, App Type and Home Page URL and for App Type use "Server-side Web app". Note Redirect URL for further use.
          Step 3 - select all the check boxes to enable access for the api.
          Step 4 - Click "Register App" and note client-id and client-secret that are returned for further use.
   
8. Generate the authorization code by sending a GET request using url https://login.windows.net/common/oauth2/authorize?response_type=code&client_id=<client-id>&resource=https://analysis.windows.net/powerbi/api&redirect_uri=<Redirect URL>.
• Creating the credentials to use the Salesforce REST Connector:
  1. First, create a developer account in Salesforce using https://developer.salesforce.com/signup
  2. After, you need to create a connected app to get the clientId, clientSecret for getting the accessToken and refreshToken to access the Salesforce API.
      
     If you have a Developer Edition account, log in to salesforce.com with your developer credentials, navigate to Setup ➤ Create ➤ Apps, and in the Connected Apps section, click Newto create a new connected App.  
     
     Then, Provide the App name, callback URL, Check Enable OAuth Settings to expand the API section and select the OAuth Scopes as below.
     
      
     From these steps, you will get the Consumer key(Client ID) and Consumer Secret(Client Secret) for your app. 
  3. Copy the following URL and paste it into a web browser URL bar and you have to replace the <your_client_id> and <your_redirect_uri> with the created app values. Then, you need to approve the application to access your salesforce account.
     
     https://login.salesforce.com/services/oauth2/authorize?response_type=code&client_id=<your_client_id>&redirect_uri=<your_redirect_uri>
  4. After, successful authorization, the user's browser is redirected back to the redirect URI at the client application, with a URL of the form:
     
     https://www.yahoo.com/?code=aPrxqJ8A8kLOza.tLq5SDTDZbLiQ.tRsRLaXCIN0DsvDG5VK6F7_T6XDsDtRhHuchcJASkBCUA%3D%3D
  5. The client application can now extract the authorization code from its URL parameter. For that you just copy the below URL and make POST request with the respected values for the application that created above. You can set format as urlencoded, json or xml to getting the response as the specified format option.  
     From the above response, you can get the accessToken to access the salesforce organization via the created app and refreshToken to refresh the accessToken when it will be expired.
     https://login.salesforce.com/services/oauth2/token?code= aPrxqJ8A8kLOza.tLq5SDTDZbLiQ.tRsRLaXCIN0DsvDG5VK6F7_T6XDsDtRhHuchcJASkBCUA%3D%3D==&grant_type=authorization_code&client_id=<your_client_id>&client_secret=<your_client_secret>&redirect_uri=<your_redirect_uri>&format=xml
  
  

  Sample configuration

  
  

  Following is a sample proxy service that illustrates how to test the above scenario.

  
  

  Sample proxy

  
  
  

   <?xml version="1.0" encoding="UTF-8"?>  
   <proxy xmlns="http://ws.apache.org/ns/synapse"  
       name="importdataintoPowerbi"  
       startOnLoad="true"  
       statistics="disable"  
       trace="disable"  
       transports="http,https">  
     <target>  
      <inSequence>  
        <property expression="json-eval($.salesforceRESTHostName)"  
             name="salesforceREST.hostName"/>  
        <property expression="json-eval($.salesforceRESTRefreshToken)"  
             name="salesforceREST.refreshToken"/>  
        <property expression="json-eval($.salesforceRESTClientSecret)"  
             name="salesforceREST.clientSecret"/>  
        <property expression="json-eval($.salesforceRESTClientId)"  
             name="salesforceREST.clientId"/>  
        <property expression="json-eval($.salesforceRESTAccessToken)"  
             name="salesforceREST.accessToken"/>  
        <property expression="json-eval($.salesforceRESTQueryString)"  
             name="salesforceREST.queryString"/>  
        <property expression="json-eval($.salesforceRESTApiVersion)"  
             name="salesforceREST.apiVersion"/>  
        <property expression="json-eval($.salesforceRESTApiUrl)"  
             name="salesforceREST.apiUrl"/>  
        <property expression="json-eval($.salesforceRESTIntervalTime)"  
             name="salesforceREST.intervalTime"/>  
        <property expression="json-eval($.salesforceRESTRegistryPath)"  
             name="salesforceREST.registryPath"/>  
        <property expression="json-eval($.powerbiAccessToken)" name="powerbi.accessToken"/>  
        <property expression="json-eval($.powerbiRefreshToken)"  
             name="powerbi.refreshToken"/>  
        <property expression="json-eval($.powerbiClientId)" name="powerbi.clientId"/>  
        <property expression="json-eval($.powerbiClientSecret)"  
             name="powerbi.clientSecret"/>  
        <property expression="json-eval($.powerbiApiUrl)" name="powerbi.apiUrl"/>  
        <property expression="json-eval($.datasetId)" name="powerbi.datasetId"/>  
        <property expression="json-eval($.tableName)" name="powerbi.tableName"/>  
        <property name="fields" scope="operation" value="["/>  
        <property name="length" scope="operation" value=""/>  
        <salesforcerest.init>  
         <apiVersion>{$ctx:salesforceREST.apiVersion}</apiVersion>  
         <hostName>{$ctx:salesforceREST.hostName}</hostName>  
         <accessToken>{$ctx:salesforceREST.accessToken}</accessToken>  
         <refreshToken>{$ctx:salesforceREST.refreshToken}</refreshToken>  
         <clientSecret>{$ctx:salesforceREST.clientSecret}</clientSecret>  
         <clientId>{$ctx:salesforceREST.clientId}</clientId>  
         <apiUrl>{$ctx:salesforceREST.apiUrl}</apiUrl>  
         <intervalTime>{$ctx:salesforceREST.intervalTime}</intervalTime>  
         <registryPath>{$ctx:salesforceREST.registryPath}</registryPath>  
        </salesforcerest.init>  
        <salesforcerest.query>  
         <queryString>{$ctx:salesforceREST.queryString}</queryString>  
        </salesforcerest.query>  
        <property expression="//records" name="records"/>  
        <iterate continueParent="true"  
            description=""  
            expression="//records"  
            id="records_iterator"  
            sequential="true">  
         <target>  
           <sequence>  
            <property expression="json-eval($.records)" name="records"/>  
            <property expression="get-property('operation','fields')" name="fields"/>  
            <script language="js">  
                 var records = mc.getProperty("records");                
                 var obj = eval ("(" + records + ")");  
                 var ans;  
                 var fields = mc.getProperty("fields");  
                 fields = fields.concat("{");               
                 var Id = obj['Id'];  
                 var Name = obj['Name'];  
                 var AccountType = obj.attributes['type'];  
                 if(Id !="" &amp;&amp; Id !=null &amp;&amp; Name !="" &amp;&amp; Name !=null){  
                   fields = fields.concat('"Id"' + ':"' + Id + '","Name"' + ':"' + Name + '","AccountType"' + ':"' + AccountType + '"');  
                 }  
                 fields = fields.concat("},");  
                 mc.setProperty("fields",fields);  
                 mc.setProperty("length",fields.length());  
            </script>  
            <property expression="get-property('fields')" name="fields" scope="operation"/>  
            <property expression="get-property('length')" name="length" scope="operation"/>  
           </sequence>  
         </target>  
        </iterate>  
        <property expression="get-property('operation','fields')"  
             name="fields"  
             scope="operation"/>  
        <property expression="get-property('operation','length')"  
             name="length"  
             scope="operation"/>  
        <property expression="fn:concat(fn:substring(get-property('operation','fields'),0,get-property('operation','length')),']')"  
             name="values"/>  
        <powerbi.init>  
         <accessToken>{$ctx:powerbi.accessToken}</accessToken>  
         <refreshToken>{$ctx:powerbi.refreshToken}</refreshToken>  
         <clientId>{$ctx:powerbi.clientId}</clientId>  
         <clientSecret>{$ctx:powerbi.clientSecret}</clientSecret>  
         <apiUrl>{$ctx:powerbi.apiUrl}</apiUrl>  
        </powerbi.init>  
        <powerbi.addRows>  
         <datasetId>{$ctx:powerbi.datasetId}</datasetId>  
         <tableName>{$ctx:powerbi.tableName}</tableName>  
         <rows>{$ctx:values}</rows>  
        </powerbi.addRows>  
        <property expression="$axis2:HTTP_SC" name="addRowsStatusCode"/>  
        <filter regex="true"  
            source="get-property('addRowsStatusCode') != 200">  
         <then>  
           <log level="full"/>  
           <property name="message" value="Unable to Insert the row "/>  
           <loopback/>  
         </then>  
         <else>  
           <log level="full"/>  
           <property name="message" value="Success: Successfully Import the Data"/>  
           <loopback/>  
         </else>  
        </filter>  
        <respond/>  
        <send/>  
      </inSequence>  
      <outSequence>  
        <property name="messageType" scope="axis2" value="application/json"/>  
        <payloadFactory media-type="json">  
         <format>  
             {  
             "Response":{  
             "process":"ImportDataIntoPowerBi",  
             "activityResponse":"$1"  
             }  
             }  
           </format>  
         <args>  
           <arg evaluator="xml" expression="get-property('message')"/>  
         </args>  
        </payloadFactory>  
        <log/>  
        <send/>  
      </outSequence>  
     </target>  
     <description/>  
   </proxy>
  

  Sample request

  
  

  
  

  {  
    "powerbiAccessToken": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6Ikk2b0J3NFZ6QkhPcWxlR3JWMkFKZEE1RW1YYyIsImtpZCI6Ikk2b0J3NFZ6QkhPcWxlR3JWMkFKZEE1RW1YYyJ9.eyJhdWQiOiJodHRwczovL2FuYWx5c2lzLndpbmRvd3MubmV0L3Bvd2VyYmkvYXBpIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvNDg1NWRjNDEtNmU4MS00YTdkLWJhOGYtZGM5NGNiMGI3NTE3LyIsImlhdCI6MTQ3OTAzMTg1MywibmJmIjoxNDc5MDMxODUzLCJleHAiOjE0NzkwMzU3NTMsImFjciI6IjEiLCJhbXIiOlsicHdkIl0sImFwcGlkIjoiNTAyMDgxM2QtOTgzMC00NDk0LWE4MjYtYTU3MDVhMmIxZTRhIiwiYXBwaWRhY3IiOiIxIiwiZmFtaWx5X25hbWUiOiJHbmFuZXN3YXJhbiIsImdpdmVuX25hbWUiOiJCaXJ1bnRoYSIsImlwYWRkciI6IjE3NS4xNTcuMjQ0LjIiLCJuYW1lIjoiQmlydW50aGEgR25hbmVzd2FyYW4iLCJvaWQiOiIzZWM1MDc0ZC1jOTVhLTRmZWQtOWEzZS1hNTM0YzE1ZjE5YzQiLCJwbGF0ZiI6IjE0IiwicHVpZCI6IjEwMDNCRkZEOUJGM0ZGQ0EiLCJzY3AiOiJEYXNoYm9hcmQuUmVhZC5BbGwgRGF0YXNldC5SZWFkV3JpdGUuQWxsIEdyb3VwLlJlYWQgUmVwb3J0LlJlYWQuQWxsIiwic3ViIjoidG9ZaTAteFNDT3hYSTV4eTB2QkZuWmdzTjc5N2hXV3l6YWxBQnJISVF0USIsInRpZCI6IjQ4NTVkYzQxLTZlODEtNGE3ZC1iYThmLWRjOTRjYjBiNzUxNyIsInVuaXF1ZV9uYW1lIjoiYmlydW50aGFAZ25hbmVzLm9ubWljcm9zb2Z0LmNvbSIsInVwbiI6ImJpcnVudGhhQGduYW5lcy5vbm1pY3Jvc29mdC5jb20iLCJ2ZXIiOiIxLjAiLCJ3aWRzIjpbIjYyZTkwMzk0LTY5ZjUtNDIzNy05MTkwLTAxMjE3NzE0NWUxMCJdfQ.f5HoxDyvi3EcjmKzUCzKobq1jjGECm68bfbGAn72SXOOwqYG9IkvzOuhR_4UL0BSp-E7wex9MqkrGUK0b0rJ1awg8kF7Y5ICjnIiuEHK7jLJf5DEEMb91tBRo3mMgBrri_UR7V0MIBG5IRMtbBiAIZCn-dZw9izCc3pOb9J3rx7ifMdda5frKqF3gQXUGl_814PXQia5EGxcTAfVcBbvcgTZ7FQjzidgI0qzoirNtZshrFzAqj7N1_2WpOlVC-XugA4Oi5vOcm2A3LHZYhYrk1GoqJga8liyUALWDyrKsvyPHGO9dEoYFVCgBK-48envJWlzOO987rI6MACWz8QmIg",  
    "powerbiRefreshToken": "AQABAAAAAADRNYRQ3dhRSrm-4K-adpCJ0Bc0NMKTefb1M1UhZxzmA3v3afmAGpsz_3ISKe49U594Luh4vS53BoCWlAG0vlAv3y5gwhtln1smhbBM6-XZSjYvhgYrGMExiCnX92VPbqI61kYOnOrLtQ8MypdDMdoEotp0VIw3wMV-Pa9qU2J7DhPG0_r7V1b-ZqhQy0PZRRH7RTHelDLpCGsKHYNTqdgFH10GO8JOCHDwImXO5uMBgrxkI9hsAgYw-kc4HvSX8Ah8HXjfHGD3i90Rc79J0mA0eeL4knNkEKyBuXE7CVrEyFUKk6ijybCneHRUOC2_ejY2C04W3S4Us7TlC8rLOAo3Tz6JTF5gpTlmJ7QSF5k_7yiIT0ZmnloVfzCorkqxpcVX0rheQ3b83zX7I4VnEjsdUlIsvcV4QXPbQTcuNRsmEKZHErRph_8F-eG-gxUOBh4DWMss8FPylD8_tioscFIcMhC7aOXglOVnUNESFgJhcp8L0duu64Twn_9b1mCSlMXw8bM8ASm7QabHTnxXGmQHCGmehtd3Df9dIaAbXjwtwkxOSJjAlvGdjGoThO1IJ9gO5fMhaCRicfC8rkVQVsWaP_-sgeZtMFKyItT43C3CiWhPLQbKYwuOeJvWrHtw6z4A4oKPLJ-ALwMBDj8ffkiKjtdAHvpT5K3NQeVcMCkJnvkinLfSUo_--GSoxeWmJpktAhVIv8f8MB-TLUXc5DhufPUrZs1ofPTTZegzild9e-aob_K95zE_tzbvJa_cDF8gAA",  
    "powerbiClientId": "5020813d-9830-4494-a826-a5705a2b1e4a",  
    "powerbiClientSecret": "I+mhnYnSKqnsZmORLtPO5vbJCSnDRdP9J3xUyTPez24=",  
    "powerbiApiUrl": "https://api.powerbi.com",  
    "datasetId": "dd2defac-d062-4262-a9a9-00c1071723b8",  
    "tableName":"Records",  
    "salesforceRESTHostName": "https://login.salesforce.com",  
    "salesforceRESTRefreshToken": "5Aep861TSESvWeug_xCxj8yFaTjeV0kXa8X.gg5d1ipArZn7qABXj_BUgpsSVDvRfMNJtYwqnie1JejvB_GrLnJ",  
    "salesforceRESTClientSecret": "3543063387981089204",  
    "salesforceRESTClientId": "3MVG9ZL0ppGP5UrDt0n2ptTJ_fWBXDlYYXTic9.ApotX3azPQhMX2dw_gXGQ1JyPwTLWjrnCB.0K3vpUJV3Jv",  
    "salesforceRESTAccessToken": "00D28000000avoo!ARoAQGVqGhimwqewU5TNG1Uf4geWWRx9gV13sDffT1zXFHY_mAl158PI1wqfiAsH6uMOJfp5kzps0ERHXxTXpY8GWfNIltID",  
    "salesforceRESTQueryString": "select id, name from Account limit 4",  
    "salesforceRESTApiVersion": "v32.0",  
    "salesforceRESTApiUrl": "https://ap2.salesforce.com",  
    "salesforceRESTIntervalTime": "2400000",  
    "salesforceRESTRegistryPath": "connectors/salesforcerest"  
   }
  

Use Case – Salesforce REST Connector + Google Spread Sheet connector

The below section describes the Google Spreadsheet connector use case by integrating the spreadsheet connector with the Salesforce REST Connector. For example you can use the Salesforce REST Connector to get the account details (Eg :- Id, Name of the records in your organization) from Salesforce and add that account details into Google Spreadsheet using Google Spreadsheet connector.

Overview

The following diagram illustrates the use case for the Google Spreadsheet connector.

1. Get the account details from the Salesforce using query operation.
2. Add the account details into the Spreadsheet using the addRowsColumnsData  operation.

Environment setup for running the Google Spread Sheet use cases

• Download ESB 5.0.0 from here.
• Upload the following connectors to the ESB:
  • salesforcerest-connector-1.0.2
  • googlespreadsheet-connector-3.0.0
• Follow the instructions given in the developer guide of the above connectors.
•  Import the certificate into the ESB client keystore to use the Salesforce REST connector.Refer this link to import the certificate.  
• Creating the credentials to use the Google Spreadsheet connector:

 Creating a Client ID and Client Secret

• • 1. As the email sender, navigate to the URL  https://console.developers.google.com/projectselector/apis/credentials  and log in to your google account.
    2. If you do not already have a project, create a new project and navigate to Create Credential -> OAuth client ID.
    3. At this point, if the consent screen name is not provided, you will be prompted to do so.
    4. Select the Web Application option and create a client. Provide  https://developers.google.com/oauthplayground  as the redirect URL under Authorized redirect URIs and click on Create. The client ID and client secret will then be displayed.
    5. See Google Spreadsheet API documentation for details on creating the Client ID and Client Secret.
    6. Click on the Library on the side menu, and select Google Sheets API. Click enable.

Obtaining the Access Token and Refresh Token 

Follow these steps to automatically refresh the expired token when connecting to Google API:

1. Navigate to the URL  https://developers.google.com/oauthplayground and click on the gear wheel at the top right corner of the screen and select the option Use your own OAuth credentials. Provide the client ID and client secret you previously created and click on Close.
2. Now under Step 1, select Google Sheets API v4 from the list of APIs and check all the scopes listed down and click on Authorize APIs. You will then be prompted to allow permission, click on Allow.
3.  In Step 2, click on Exchange authorization code for tokens to generate and display the access token and refresh token. 

• Creating the credentials to use the Salesforce REST Connector:
  1. First, create a developer account in Salesforce using https://developer.salesforce.com/signup
  2. After, you need to create a connected app to get the clientId, clientSecret for getting the accessToken and refreshToken to access the Salesforce API.
      
     If you have a Developer Edition account, log in to salesforce.com with your developer credentials, navigate to Setup ➤ Create ➤ Apps, and in the Connected Apps section, click New to create a new connected App.  
     
      
     Then, Provide the App name, callback URL, Check Enable OAuth Settings to expand the API section and select the OAuth Scopes as below.
      
     
      
     From these steps, you will get the Consumer key(Client ID) and Consumer Secret(Client Secret) for your app.
      
  3. Copy the following URL and paste it into a web browser URL bar and you have to replace the <your_client_id> and <your_redirect_uri> with the created app values. Then, you need to approve the application to access your salesforce account.
     
     https://login.salesforce.com/services/oauth2/authorize?response_type=code&client_id=<your_client_id>&redirect_uri=<your_redirect_uri>
  4. After, successful authorization, the user's browser is redirected back to the redirect URI at the client application, with a URL of the form:
     
     https://www.yahoo.com/?code=aPrxqJ8A8kLOza.tLq5SDTDZbLiQ.tRsRLaXCIN0DsvDG5VK6F7_T6XDsDtRhHuchcJASkBCUA%3D%3D
  5. The client application can now extract the authorization code from its URL parameter. For that you just copy the below URL with the respected values for the application that created above. You can set format as urlencoded, json or xml to getting the response as the specified format option.  
     From the above response, you can get the accessToken to access the salesforce organization via the created app and refreshToken to refresh the accessToken when it will be expired.
     https://login.salesforce.com/services/oauth2/token?code= aPrxqJ8A8kLOza.tLq5SDTDZbLiQ.tRsRLaXCIN0DsvDG5VK6F7_T6XDsDtRhHuchcJASkBCUA%3D%3D==&grant_type=authorization_code&client_id=<your_client_id>&client_secret=<your_client_secret>&redirect_uri=<your_redirect_uri>&format=xml

Sample configuration

Following is a sample proxy service that illustrates how to test the above scenario.

 <?xml version="1.0" encoding="UTF-8"?>  
 <proxy xmlns="http://ws.apache.org/ns/synapse"  
     name="ImportDatatoSpreadsheet"  
     startOnLoad="true"  
     statistics="disable"  
     trace="disable"  
     transports="http,https">  
   <target>  
    <inSequence>  
      <property expression="json-eval($.salesforceRESTHostName)"  
           name="salesforceREST.hostName"/>  
      <property expression="json-eval($.salesforceRESTRefreshToken)"  
           name="salesforceREST.refreshToken"/>  
      <property expression="json-eval($.salesforceRESTClientSecret)"  
           name="salesforceREST.clientSecret"/>  
      <property expression="json-eval($.salesforceRESTClientId)"  
           name="salesforceREST.clientId"/>  
      <property expression="json-eval($.salesforceRESTAccessToken)"  
           name="salesforceREST.accessToken"/>  
      <property expression="json-eval($.salesforceRESTQueryString)"  
           name="salesforceREST.queryString"/>  
      <property expression="json-eval($.salesforceRESTApiVersion)"  
           name="salesforceREST.apiVersion"/>  
      <property expression="json-eval($.salesforceRESTApiUrl)"  
           name="salesforceREST.apiUrl"/>  
      <property expression="json-eval($.salesforceRESTIntervalTime)"  
           name="salesforceREST.intervalTime"/>  
      <property expression="json-eval($.salesforceRESTRegistryPath)"  
           name="salesforceREST.registryPath"/>  
      <property expression="json-eval($.googlespreadsheetAccessToken)"  
           name="googlespreadsheet.accessToken"/>  
      <property expression="json-eval($.googlespreadsheetRefreshToken)"  
           name="googlespreadsheet.refreshToken"/>  
      <property expression="json-eval($.googlespreadsheetClientId)"  
           name="googlespreadsheet.clientId"/>  
      <property expression="json-eval($.googlespreadsheetClientSecret)"  
           name="googlespreadsheet.clientSecret"/>  
      <property expression="json-eval($.googlespreadsheetApiUrl)"  
           name="googlespreadsheet.apiUrl"/>  
      <property expression="json-eval($.spreadsheetId)"  
           name="googlespreadsheet.spreadsheetId"/>  
      <property expression="json-eval($.range)" name="googlespreadsheet.range"/>  
      <property expression="json-eval($.valueInputOption)"  
           name="googlespreadsheet.valueInputOption"/>  
      <property name="fields" scope="operation" value="["/>  
      <property name="length" scope="operation" value=""/>  
      <salesforcerest.init>  
       <apiVersion>{$ctx:salesforceREST.apiVersion}</apiVersion>  
       <hostName>{$ctx:salesforceREST.hostName}</hostName>  
       <accessToken>{$ctx:salesforceREST.accessToken}</accessToken>  
       <refreshToken>{$ctx:salesforceREST.refreshToken}</refreshToken>  
       <clientSecret>{$ctx:salesforceREST.clientSecret}</clientSecret>  
       <clientId>{$ctx:salesforceREST.clientId}</clientId>  
       <apiUrl>{$ctx:salesforceREST.apiUrl}</apiUrl>  
       <intervalTime>{$ctx:salesforceREST.intervalTime}</intervalTime>  
       <registryPath>{$ctx:salesforceREST.registryPath}</registryPath>  
      </salesforcerest.init>  
      <salesforcerest.query>  
       <queryString>{$ctx:salesforceREST.queryString}</queryString>  
      </salesforcerest.query>  
      <property expression="//records" name="records"/>  
      <iterate continueParent="true" description="" expression="//records" id="records_iterator" sequential="true">  
       <target>  
         <sequence>  
          <property expression="json-eval($.records)" name="records"/>  
          <property expression="get-property('operation','fields')" name="fields"/>  
          <script language="js">  
               var records = mc.getProperty("records");                
               var obj = eval ("(" + records + ")");  
               var ans;  
               var fields = mc.getProperty("fields");  
               fields = fields.concat("[");               
               var Id = obj['Id'];  
               var Name = obj['Name'];  
               if(Id !="" &amp;&amp; Id !=null &amp;&amp; Name !="" &amp;&amp; Name !=null){  
                   fields = fields.concat('"' + Id + '","' + Name + '"');  
               }  
               fields = fields.concat("],");  
               mc.setProperty("fields",fields);  
               mc.setProperty("length",fields.length());  
          </script>  
          <property expression="get-property('fields')" name="fields" scope="operation"/>  
          <property expression="get-property('length')" name="length" scope="operation"/>  
         </sequence>  
       </target>  
      </iterate>  
      <property expression="get-property('operation','fields')" name="fields" scope="operation"/>  
      <property expression="get-property('operation','length')" name="length" scope="operation"/>  
      <property expression="fn:concat(fn:substring(get-property('operation','fields'),0,get-property('operation','length')),']')" name="values"/>  
      <googlespreadsheet.init>  
       <accessToken>{$ctx:googlespreadsheet.accessToken}</accessToken>  
       <refreshToken>{$ctx:googlespreadsheet.refreshToken}</refreshToken>  
       <clientId>{$ctx:googlespreadsheet.clientId}</clientId>  
       <clientSecret>{$ctx:googlespreadsheet.clientSecret}</clientSecret>  
       <apiUrl>{$ctx:googlespreadsheet.apiUrl}</apiUrl>  
      </googlespreadsheet.init>  
      <googlespreadsheet.addRowsColumnsData>  
       <spreadsheetId>{$ctx:googlespreadsheet.spreadsheetId}</spreadsheetId>  
       <range>{$ctx:googlespreadsheet.range}</range>  
       <valueInputOption>{$ctx:googlespreadsheet.valueInputOption}</valueInputOption>  
       <values>{$ctx:values}</values>  
      </googlespreadsheet.addRowsColumnsData>  
      <property expression="$axis2:HTTP_SC" name="addRowsColumnsDataStatusCode"/>  
      <filter regex="true"  
          source="get-property('addRowsColumnsDataStatusCode') != 200">  
       <then>  
         <log level="full"/>  
         <property name="message" value="Unable to Insert the row "/>  
         <loopback/>  
       </then>  
       <else>  
         <log level="full"/>  
         <property name="message" value="Success: Successfully Import the Data"/>  
         <loopback/>  
       </else>  
      </filter>  
      <respond/>  
      <send/>  
    </inSequence>  
    <outSequence>  
      <property name="messageType" scope="axis2" value="application/json"/>  
      <payloadFactory media-type="json">  
       <format>  
           {  
           "Response":{  
           "process":"ImportDataIntoSpreadsheet",  
           "activityResponse":"$1"  
           }  
           }  
         </format>  
       <args>  
         <arg evaluator="xml" expression="get-property('message')"/>  
       </args>  
      </payloadFactory>  
      <log/>  
      <send/>  
    </outSequence>  
   </target>  
   <description/>  
 </proxy>

Sample request

{   
   "googlespreadsheetAccessToken": "ya29.Ci-PA9JhMsGT5GL_wsbnI8pkoEkLja16d4ms1ugiobKGIC7dqAi9BCnL8JOd6uicUg",   
   "googlespreadsheetRefreshToken": "1/x4NQS273s3ISPcaTWJYz30nkyB-Qi_atiy4x7GupjMg",   
   "googlespreadsheetClientId": "219479486681-r9j113m7nan5nbf955if8e4are9o7mtj.apps.googleusercontent.com",   
   "googlespreadsheetClientSecret": "CArehV1yFnZi2J-9UNGuW9TE",   
   "googlespreadsheetApiUrl": "https://sheets.googleapis.com/v4/spreadsheets",   
   "spreadsheetId": "14PJALKcIXLr75rJWXlHhVjOt7z0Nby7AvcKXJGhMN2s",   
   "range":"Expenses!A1",   
   "valueInputOption":"RAW",   
   "salesforceRESTHostName": "https://login.salesforce.com",   
   "salesforceRESTRefreshToken": "5Aep861TSESvWeug_xCxj8yFaTjeV0kXa8X.gg5d1ipArZn7qCfwD9IORz9LedLqQ.HtkAr8fzUCzKKFlswxA_z",   
   "salesforceRESTClientSecret": "3543063387981089204",   
   "salesforceRESTClientId": "3MVG9ZL0ppGP5UrDt0n2ptTJ_fWBXDlYYXTic9.ApotX3azPQhMX2dw_gXGQ1JyPwTLWjrnCB.0K3vpUJV3Jv",   
   "salesforceRESTAccessToken": "00D28000000avoo!ARoAQI4rBfsxeB8OLh97Ebje7Ry0DpFXbP1wWyYUKuEWTrU70lzL2zOYXopgq6YB_ZgZz.JrSaVCC2b39mGSOTR7XZGiwCVe",   
   "salesforceRESTQueryString": "select id, name from Account limit 3",   
   "salesforceRESTApiVersion": "v32.0",   
   "salesforceRESTApiUrl": "https://ap2.salesforce.com",   
   "salesforceRESTIntervalTime": "2400000",   
   "salesforceRESTRegistryPath": "connectors/salesforcerest"   
  }