Accessing Google Sheets API via Service Account Key

Question

I am working on Google Sheets <-> Salesforce integration and developing it in Salesforce programming language - Apex on Force.com platform.

Currently I am attempting to connect to Google Sheets API. 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, using the following class:

        /****** API CALLOUT *******/
        public static HttpResponse googleSheetsCallout (){
    //the below line provides a string containing access token to google
          string accessCode = getAccessToken();
//I found this endpoint structure online, this may be why my script 
//isn't working. However, I am struggling to find the alternative.          
string endpoint = 'https://sheets.googleapis.com/v4/spreadsheets/params=[SPREADSHEET ID GOES HERE]/values/[RANGE GOES HERE]?access_token=';
          httpRequest req = new httpRequest();
          req.setEndpoint(endpoint+accessCode);
          req.setMethod('GET');
          req.setTimeout(120000);
          httpResponse res = new http().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]

I enabled Google Sheets access in the google developer console menu, and what's interesting is when loking at the console it appears that Google notices API requests being sent out (they are appearing on the activity chart).

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.