0

I am currently attempting to connect to Google Sheets API via OAuth2 with JWT. I am using Service Account Key, so Salesforce can pull the data from Google Sheets without the requirement for manual authorisation every time it sends out a query.

I am at the point where I set up the Service Account Key and I am successfully sending a request to it to obtain the access_code.

Then I am attempting to query the API, googleSheetsCalloutClass:

    public with sharing class gSheets {

  /******  GET ACCESS KEY  ******/

    public static String getAccessToken(){

        Http h = new Http();
        HttpRequest req = new HttpRequest();
        HttpResponse res = new HttpResponse();
        req.setEndpoint('https://accounts.google.com/o/oauth2/token');
        req.setMethod('POST');

        req.setHeader('ContentType','application/x-www-form-urlencoded');

        String header = '{"alg":"RS256","typ":"JWT"}';
        String header_encoded = EncodingUtil.base64Encode(blob.valueof(header));

        String claim_set = '{"iss":"loginloginlogin@sheettosalesforce.iam.gserviceaccount.com"';
        claim_set += ',"scope":"https://www.googleapis.com/auth/spreadsheets"';
        claim_set += ',"aud":"https://accounts.google.com/o/oauth2/token"';
        claim_set += ',"exp":"' + datetime.now().addHours(1).getTime()/1000;
        claim_set += '","iat":"' + datetime.now().getTime()/1000 + '"}';

        String claim_set_encoded = EncodingUtil.base64Encode(blob.valueof(claim_set));

        String signature_encoded = header_encoded + '.' + claim_set_encoded;

        String key = 'KEY IS HERE';

        blob private_key = EncodingUtil.base64Decode(key);
        signature_encoded = signature_encoded.replaceAll('=','');
        String signature_encoded_url = EncodingUtil.urlEncode(signature_encoded,'UTF-8');
        blob signature_blob =   blob.valueof(signature_encoded_url);

        String signature_blob_string = EncodingUtil.base64Encode(Crypto.sign('RSA-SHA256', signature_blob, private_key));

        String JWT = signature_encoded + '.' + signature_blob_string;

        JWT = JWT.replaceAll('=','');

        String grant_string= 'urn:ietf:params:oauth:grant-type:jwt-bearer';
        req.setBody('grant_type=' + EncodingUtil.urlEncode(grant_string, 'UTF-8') + '&assertion=' + EncodingUtil.urlEncode(JWT, 'UTF-8'));
        res = h.send(req);
        String response_debug = res.getBody() +' '+ res.getStatusCode();
        System.debug('Response =' + response_debug );
        if(res.getStatusCode() == 200) {
            JSONParser parser = JSON.createParser(res.getBody());
            while (parser.nextToken() != null) {
                if ((parser.getCurrentToken() == JSONToken.FIELD_NAME) && (parser.getText() == 'access_token')) {
                    // Move to the value.
                    parser.nextToken();
                    // Return the access_token

                    System.debug('parser get text is '+parser.getText());
                    return parser.getText();
                }
            }
        }
        return 'error';
    }

    /****** API CALLOUT *******/
    public static HttpResponse googlesSheetCallout (){
      string accessToken = getAccessToken();


      httpRequest req = new httpRequest();
      req.setEndpoint('https://sheets.googleapis.com/v4/spreadsheets/1QsUxoouUp-l2Awe2QGFFDtBmLX0fz3cd9kZDRyg5CDI/values/A:AD?access_token='+accessToken);
      req.setMethod('GET');

      Http h = new Http();
      HttpResponse res = h.send(req);
      System.debug ('res is ' +res);
      return res;

    }


}

When I run the function this is what the log returns:

|CALLOUT_RESPONSE|[71]|System.HttpResponse[Status=Forbidden, StatusCode=403]
|USER_DEBUG|[72]|DEBUG|res is System.HttpResponse[Status=Forbidden, StatusCode=403]
4
  • is your access_token valid? Jun 15, 2017 at 11:32
  • I believe so. How would you verify its validity ? Jun 15, 2017 at 11:36
  • what are you getting as a body of response ? it should be like { "access_token" : "1/8xbJqaOZXSUZbHLl5EOtu1pxz3fmmetKx9W8CV4t79M", "token_type" : "Bearer", "expires_in" : 3600 } Jun 15, 2017 at 11:41
  • that's precisely what I am getting: Response ={ "access_token" : "TOKEN WAS HERE", "expires_in" : 3600, "token_type" : "Bearer" } 200 Jun 15, 2017 at 11:47

2 Answers 2

1

I solved it, and the issue was not the code itself.

The problem was sharing my sheet. To allow read/edit access to your sheet from the service account it must be shared with the Service Account ID email address, the same way it's shared with any other user. If this isn't done the script will produce 403 error.

More info here.

0

I've solved exactly the same task recently. That is what I was doing for A1:V100 ranges:

HttpRequest req = new HttpRequest();
req.setMethod('GET');
req.setEndpoint('https://sheets.googleapis.com/v4/spreadsheets/'+fileId+'/values/A1:V100?access_token='+accessToken);
Http h = new Http();
HttpResponse res = h.send(req);

Don't forget to enable Google Sheep API from google console page

7
  • Thank you Alexander. I changed the script to look exactly the same as yours but it still isn't working... The Google Sheets API is enabled in the console. Also, what's interesting, when looking at API activity in the console Google detects that API requests are being made. Jun 15, 2017 at 12:09
  • on that activity what is a status code of request? Jun 15, 2017 at 12:33
  • [72]|System.HttpResponse[Status=Forbidden, StatusCode=403] Jun 15, 2017 at 12:36
  • on that activity what is a status code of request in google console? Jun 15, 2017 at 12:48
  • Just says 403 in the console Jun 15, 2017 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.