Accessing Google Sheets API via OAuth2 with JWT

Question

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":"[email protected]"';
        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]

Answer 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.

Answer 2

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