Created
May 25, 2011 08:40
-
-
Save stomita/990589 to your computer and use it in GitHub Desktop.
A Google Apps Script, which simply connects and fetches data from Salesforce RESTful API with OAuth authentication.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Connect and fetch Salesforce data via OAuth | |
*/ | |
function queryDataFromSalesforce() { | |
// Read OAuth consumer key / secret of this client app from script properties, | |
// which can be issued from Salesforce's remote access setting in advance. | |
var sfConsumerKey = ScriptProperties.getProperty("sfConsumerKey"); | |
var sfConsumerSecret = ScriptProperties.getProperty("sfConsumerSecret"); | |
if (!sfConsumerKey || !sfConsumerSecret) { | |
Browser.msgBox("Register Salesforce OAuth Consumer Key and Secret in Script Properties"); | |
return; | |
} | |
// Register new OAuth service, named "salesforce" | |
// For OAuth endpoint information, see help doc in Salesforce. | |
// https://na7.salesforce.com/help/doc/en/remoteaccess_oauth_1_flows.htm | |
var oauth = UrlFetchApp.addOAuthService("salesforce"); | |
oauth.setAccessTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/AccessTokenHandler"); | |
oauth.setRequestTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/RequestTokenHandler"); | |
oauth.setAuthorizationUrl("https://login.salesforce.com/setup/secur/RemoteAccessAuthorizationPage.apexp?oauth_consumer_key="+encodeURIComponent(sfConsumerKey)); | |
oauth.setConsumerKey(sfConsumerKey); | |
oauth.setConsumerSecret(sfConsumerSecret); | |
// Convert OAuth1 access token to Salesforce sessionId (mostly equivalent to OAuth2 access token) | |
var sessionLoginUrl = "https://login.salesforce.com/services/OAuth/u/21.0"; | |
var options = { method : "POST", oAuthServiceName : "salesforce", oAuthUseToken : "always" }; | |
var result = UrlFetchApp.fetch(sessionLoginUrl, options); | |
var txt = result.getContentText(); | |
var accessToken = txt.match(/<sessionId>([^<]+)/)[1]; | |
var serverUrl = txt.match(/<serverUrl>([^<]+)/)[1]; | |
var instanceUrl = serverUrl.match(/^https?:\/\/[^\/]+/)[0]; | |
// Query account data from Salesforce, using REST API with OAuth2 access token. | |
var fields = "Id,Name,Type,BillingState,BillingCity,BillingStreet"; | |
var soql = "SELECT "+fields+" FROM Account LIMIT 100"; | |
var queryUrl = instanceUrl + "/services/data/v21.0/query?q="+encodeURIComponent(soql); | |
var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+accessToken } }); | |
var queryResult = Utilities.jsonParse(response.getContentText()); | |
// Render query result to Spreadsheet | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
sheet.clear(); | |
sheet.setFrozenRows(1); | |
// Render all field names in header row. | |
var cell = sheet.getRange('a1'); | |
fields = fields.split(','); | |
fields.forEach(function(field, j){ cell.offset(0, j).setValue(field) }) | |
// Render result records into cells | |
queryResult.records.forEach(function(record, i) { | |
fields.forEach(function(field, j) { cell.offset(i+1, j).setValue(record[field]) }); | |
}); | |
} |
Did you ever solve this problem? I'm having the exact same issue.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I am trying to access the SalesforceAPI from Google App script and I have used OAuth protocol for authorization. I was able to pull the salesforce information in the application successfully for the first time. However, on the next day I got the following error.
[ { "message": "Session expired or invalid", "errorCode": "INVALID_SESSION_ID" } ]
When I got the error first time. I have created new appscript and copy pasted the code from previous app script as it is and it worked for one day without any error again next day onwards I am getting the same error.
If there were any problem with access token or instance url then it would not work in the first place itself. I think problem is not with instance url or access token.
How can I fix this issue?
//Request:
function getService() {
return OAuth2.createService('Saleforce')
// Set the endpoint URLs.
.setAuthorizationBaseUrl('https://login.salesforce.com/services/oauth2/authorize')
.setTokenUrl('https://login.salesforce.com/services/oauth2/token')
// Set the client ID and secret.
.setClientId(CLIENT_ID)
.setClientSecret(CLIENT_SECRET)
// Set the name of the callback function that should be invoked to
// complete the OAuth flow.
.setCallbackFunction('authCallback')
// Requests offline access.
.setParam('access_type', 'offline')
// Set the property store where authorized tokens should be persisted.
.setPropertyStore(PropertiesService.getUserProperties());
}
//response:
var url1 = service.getToken().instance_url +parameter1;
var headers={
Authorization: 'Bearer ' + service.getAccessToken()
};
var options = {muteHttpExceptions: true,"headers":headers};
var response1 = UrlFetchApp.fetch(url1,options);
Logger.log(response1);
Output:
[{"message":"Session expired or invalid","errorCode":"INVALID_SESSION_ID"}]