I am currently running some script on Google script to call the Salesforce REST API and download the data into Google Sheets.
I am using the Google Sheets library id 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF, which is the OAuth2 Library which can be found on github here.
I then, as instructed, use the following call to actually authenticate the Salesforce API with the following code:
var CLIENT_ID = {I get this from the connected SalesForce app};
var CLIENT_SECRET = {I get this from the connected SalesForce app};
function getService() {
// Create a new service with the given name. The name will be used when
// persisting the authorized token, so ensure it is unique within the
// scope of the property store.
return OAuth2.createService('salesforce')
// Set the endpoint URLs
.setAuthorizationBaseUrl('https://flolive.my.salesforce.com/services/oauth2/authorize')
.setTokenUrl('https://flolive.my.salesforce.com/services/oauth2/token')
// Set the client ID and secret, from the Google Developers Console.
.setClientId(CLIENT_ID)
.setClientSecret(CLIENT_SECRET)
// Set the name of the callback function in the script referenced
// above that should be invoked to complete the OAuth flow.
.setCallbackFunction('authCallback')
// Set the property store where authorized tokens should be persisted.
.setPropertyStore(PropertiesService.getDocumentProperties())
// .setCache(CacheService.getDocumentCache())
// Set the scopes to request (space-separated for Google services).
// .setScope('r_organization_social,r_basicprofile,r_liteprofile,rw_organization_admin,r_ads_reporting,r_ads')
// Below are salesforce-specific OAuth2 parameters.
.setParam('response_type', 'code')
// Forces the approval prompt every time. This is useful for testing,
// but not desirable in a production application.
//.setParam('approval_prompt', 'force');
}
function authCallback(request) {
var salesforceService = getService();
var isAuthorized = salesforceService.handleCallback(request);
if (isAuthorized) {
return HtmlService.createHtmlOutput('Success! You can close this tab.');
} else {
return HtmlService.createHtmlOutput('Denied. You can close this tab');
}
}
function logOut() {
PropertiesService.getDocumentProperties().deleteAllProperties();
PropertiesService.getUserProperties().deleteAllProperties();
var service=getService();
service.reset();
}
function salesforceAPI() {
var service = getService();
if (service.hasAccess()) {
//exchange authorisation code for access token
// sheet.getSheetByName('Settings').getRange(3,2).setValue(getService().getAccessToken());
var tokenURL = 'https://flolive.my.salesforce.com/services/oauth2/token';
var parameters = 'grant_type=authorization_code&code='+getService().getAccessToken()+'&redirect_uri='+getService().redirect_uri+'&client_id='+CLIENT_ID+'&client_secret='+CLIENT_SECRET;
var options = {
'method': 'POST',
'contentType': 'application/x-www-form-urlencoded',
'muteHttpExceptions': true,
'payload': parameters
}
var response = UrlFetchApp.fetch(tokenURL,options);
var json = JSON.parse(response.getContentText());
console.log(json);
//sheet.getSheetByName('Settings').getRange(3,3).setValue(json.items);
return json;
} else {
var authorizationUrl = service.getAuthorizationUrl();
var template = HtmlService.createTemplate(
'<a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>. ' +
'Reopen the sidebar when the authorization is complete.');
template.authorizationUrl = authorizationUrl;
var page = template.evaluate();
SpreadsheetApp.getUi().showModalDialog(page,'Authorise salesforce');
Logger.log(getService().getAccessToken());
}
}
Now I didn't have a problem with this. All the calls I made worked. I checked the callback url in the connected app, and everything was fine.
When I tried to output some of the data to a different worksheet, things stopped working.
So now I have 2 worksheets.
SF API Backend - This is where I put all the settings and references for the API Logged Meetings - This is where I output the data that I'm trying to pull, which are the activities.
At first everything worked and I could output properly. However, when I added the API backend worksheet, at first it didn't work. I didn't get any errors, no data got pulled in.
I thought it was because it needed a different callback url, so I added it. Then, for some reason, I used the logOut() function mentioned above to try and reset the connection. That's when I started getting this error Error: Access not granted or expired. Service_.getAccessToken @ Service.gs:466.
I looked online and thought it had something to do with the PropertyService. So I deleted all the PropertyService keys. I even switched in the getService() function from .setPropertyStore(PropertiesService.getDocumentProperties()) to .setCache(CacheService.getDocumentCache()) (which is now commmented out).
Still the same error.
Then I went into Salesforce and get a set access token and replaced +getService().getAccessToken() with that.
I get this error TypeError: Cannot read property 'setClientId' of undefined at getService(SalesForceAuthentication:15:7) at salesforceAPI(SalesForceAuthentication:56:17)
I'm not sure why I get that error, since the code I changed wasn't even in that function.
And that's currently where I am.
I don't see anything blacklisted or any settings that would mess around or block this.
I even tried rebuilding the entire thing and then copying the code (only the code) to a different script. I got the same error Error: Access not granted or expired. Service_.getAccessToken @ Service.gs:466.
This leads me to think that the Client Secret/Client Id is being blocked somehow.
When you use getDocumentProperties() to store the authorization data:
Gets a property store (for this script only) that all users can access within the open document, spreadsheet, or form
The data is not available if you use a different spreadsheet than the one where it was stored.
If you want this to work for multiple spreadsheets, use getScriptProperties() or getUserProperties() instead, which can persist on multiple documents (only if you're using the same script).
.setPropertyStore(PropertiesService.getScriptProperties())