Google Sheet Integration With Salesforce Using Google Rest API

August 17, 2023


 

In this blog post, we will learn about integration between Google Sheet and Salesforce using Google Rest API.

After completing this unit, you’ll be able to:

Create Auth. Provider in Salesforce

To create auth provider in salesforce you need to follow these steps:

  1. Go to Quick Find Box in salesforce org.
  2. Search Auth. Provider in the Quick Find box and click
  3. Provider Type  = Google
  4. Name = Give name 
  5. Consumer Key = Google Console App Client Id
  6. Consumer Secret = Google Console App Secret Key
  7. Authorize Endpoint URL = https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
  8. Token Endpoint URL = https://accounts.google.com/o/oauth2/accessToken
  9. Default Scopes = https://www.googleapis.com/auth/spreadsheets
  10. Click on Save button

 

 

 

Create Named Credential in Salesforce

To create auth provider in salesforce you need to follow these steps:

  1. Search Named Credential in Quick Find box
  2. Click on arrow and Click on New Legacy
  3.  Label = Give name
  4. Name = Automatically populate value of Label
  5. URL = https://sheets.googleapis.com
  6. Identity Type = Named Principal
  7. Authentication Protocol = OAuth 2.0
  8. Authentication Provider = Choose Auth. Provider which is created above
  9. Scope = openid https://www.googleapis.com/auth/spreadsheets
  10.  Allow Merge Fields in HTTP Header = Select Checkbox
  11.  Allow Merge Fields in HTTP Body = Select Checkbox

 

 

Create App on Google Cloud Console

To create auth provider in salesforce you need to follow these steps:

 

  1. Go to https://console.cloud.google.com/ 
  2. Log in with your gmail account
  3. This screen will appear

 

 

       4. Click on red square box named SFDC then this screen will appear

 

 

        5. Click on NEW PROJECT

 

 

         6. Give Project Name and Click on CREATE

         7. Then Click on three lines top left corner

 

 

         8. Choose APIs & Services

 

 

         9. Choose Credentials > API Key

 

 

         10. API Key will be generate

         11. Now Create OAuth client ID

 

 

         12. If you not configured OAuth consent Screen then first Configure and after that you will be able to create oAuth client ID

         13. Choose OAuth client ID and Choose Application Type = Web Application

 

 

          14. Scroll Down and Add Redirect URIs = Auth. Provider Callback URL

          15. Click on Create Button

          16. After that your screen will look like this

 

 

 

Salesforce and Google Sheet Integration Using Rest API

 

1. Create Sheet using Rest API

Creates a spreadsheet, returning the newly created spreadsheet.

HTTP request

POST https://sheets.googleapis.com/v4/spreadsheets

Request body

The request body contains an instance of Spreadsheet.

Response body

If successful, the response body contains a newly created instance of Spreadsheet.

Authorization scopes

Requires one of the following OAuth scopes:

 

public static String createSheet()

    {

        String spreadSheetId='';

        HttpRequest req = new HttpRequest();

        req.setEndpoint('callout:GoogleSheetApi/v4/spreadsheets?key='+apikey);

        req.setHeader('Accept', 'application/json');

        req.setHeader('content-type', 'application/json');

        req.setHeader('Content-Length', '0');

        req.setMethod('POST');

        String jsonString = '{"properties": {"title": "CopiedSpreadsheet"}}';

        req.setBody(jsonString);

        Http http = new Http();

        HttpResponse response = http.send(req);

        if(response.getStatusCode() == 200)

        {

            System.debug('Success response.getBody() >> '+response.getBody());

            Map<String,Object> mapJson = (Map<String,Object>)JSON.deserializeUntyped(response.getBody());

            spreadSheetId = (String)mapJson.get('spreadsheetId');

            updateSheet(spreadSheetId);

        }

        else

        {

            System.debug('Error response.getBody() >> '+response.getBody());

            spreadSheetId='';

        }

        return spreadSheetId;        

    }

 

2. Append Spread REST API

Appends values to a spreadsheet. The input range is used to search for existing data and find a "table" within that range. Values will be appended to the next row of the table, starting with the first column of the table.

HTTP request

POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append

Where range = Sheet1!A1:B2  refers to the first two cells in the top two rows of Sheet1.

 

Request body

The request body contains an instance of ValueRange.

Json Request

{

  "range": string,

  "majorDimension": enum (Dimension),

  "values": [

    array

  ]

}

 

public static void appendSheet(String spreadSheetId)

    {

        Contact con = [Select Id, FirstName, LastName From Contact limit 1];

        HttpRequest req = new HttpRequest();

        req.setEndpoint('callout:GoogleSheetApi/v4/spreadsheets/'+spreadSheetId+'/values/Sheet1:append?valueInputOption=USER_ENTERED');

        req.setHeader('Accept', 'application/json');

        req.setHeader('content-type', 'application/json');

        req.setMethod('POST');

        String jsonString = '{'+

            '"majorDimension": "ROWS",'+

            '"values":['+

            '["'+con.FirstName+'","'+con.LastName+'"]'+

            '],'+

            '"range": "Sheet1",'+

            '}';

        req.setBody(jsonString);

        Http http = new Http();

        HttpResponse response = http.send(req);

        if(response.getStatusCode() == 200)

        {

            System.debug('Success response.getBody() >> '+response.getBody());

        }

        else

        {

            System.debug('Error response.getBody() >> '+response.getBody());

        }

}

 

3. Copy Spreadsheet REST API

Copies a single sheet from a spreadsheet to another spreadsheet. Returns the properties of the newly created sheet.

 

HTTP request

POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/sheets/{sheetId}:copyTo

Path parameters

spreadsheetId String

The ID of the spreadsheet containing the sheet to copy.

sheetId Integer

The ID of the sheet to copy.

Request body

The request body contains data with the following structure:

JSON representation

{

  "destinationSpreadsheetId": string

}

Response body

If successful, the response body contains an instance of SheetProperties.

 

public static void copySheet()

    {

        String spreadSheetId = createSheet();

        if(!String.isEmpty(spreadSheetId))

        {

            appendSheet(spreadSheetId);

        }                

        if(!String.isEmpty(spreadSheetId))

        {

            HttpRequest req = new HttpRequest();

            req.setEndpoint('callout:GoogleSheetApi/v4/spreadsheets/1HN4Ss30eb0-jv9m37dYgbLcv1zjtNOTXVRkz4tAH7yg/sheets/0:copyTo?key='+apikey);

            req.setHeader('Accept', 'application/json');

            req.setHeader('content-type', 'application/json');

            req.setMethod('POST');

            String jsonString = '{"destinationSpreadsheetId": "'+spreadSheetId+'"}';

            req.setBody(jsonString);

            Http http = new Http();

            HttpResponse response = http.send(req);

            if(response.getStatusCode() == 200)

            {

                System.debug('Success response.getBody() >> '+response.getBody());

            }

            else

            {

                System.debug('Error response.getBody() >> '+response.getBody());

            }   

        }        

    }

 

 

 

I hope this blog helped you!