API suddently started throwing Error: Access not granted or expired. Service

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.

CodePudding user response：

Issue:

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.

Solution:

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())