August 17, 2023
In this blog post, we will learn about integration between Google Sheet and Salesforce using Google Rest API.
Create Auth. Provider in Salesforce
To create auth provider in salesforce you need to follow these steps:
Create Named Credential in Salesforce
To create auth provider in salesforce you need to follow these steps:
Create App on Google Cloud Console
To create auth provider in salesforce you need to follow these steps:
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
Creates a spreadsheet, returning the newly created spreadsheet.
POST https://sheets.googleapis.com/v4/spreadsheets
The request body contains an instance of Spreadsheet.
If successful, the response body contains a newly created instance of Spreadsheet.
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;
}
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.
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.
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());
}
}
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!