Request specific file permissions with Google Shee

Question

I'm using the Google Sheets API to obtain sheet data for a Java project. All works as expected locally, but I'm using the verbose permissions scope https://www.googleapis.com/auth/spreadsheets which "Allows read/write access to the user's sheets and their properties.". I would prefer to not provide this app access to all of the spreadsheets in my Google Drive (have only done so locally temporarily).

Ideally, I'd like to request permission for read/write access to a file using a file's ID. Is this possible?

If not possible, I'm guessing that the https://www.googleapis.com/auth/drive.file scope which provides "Per-file access to files created or opened by the app." is the closest I can get. I haven't managed to find a way to open the file with this app. How would I go about doing that?

Or if both of the above solutions aren't ideal or possible, let me know what you'd recommend.

Thank you!

Answer 1

I know this was posted quite a while ago, but I'll give my answer to help out future developers coming across this in the future.

I think using service accounts would give you the functionality you are looking for here. Service accounts are kind of like "bot" users that users can share documents with, and then your server can login to this service account to access those documents. Instead of having to request access to a user's entire google drive or google sheets, you can have them share the documents with you manually, which I think would be more comfortable for most users.

Here is an example of how to set this up in Node.js, but the ideas should translate fairly readily to Java.

Answer 2

Scopes grant you access across an api there is no way to limit it to a single file or group of files.

Google Sheets API, v4 Scopes

• https://www.googleapis.com/auth/drive View and manage the files in your Google Drive
• https://www.googleapis.com/auth/drive.file View and manage Google Drive files and folders that you have opened or created with this app
• https://www.googleapis.com/auth/drive.readonly View the files in your Google Drive
• https://www.googleapis.com/auth/spreadsheets View and manage your spreadsheets in Google Drive
• https://www.googleapis.com/auth/spreadsheets.readonly View your Google Spreadsheets

There is no way to limit permissions to a single file. Assuming that the file you are editing was created by your application then https://www.googleapis.com/auth/drive.file should be a valid option

Sample

Java quickstart

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.ValueRange;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.security.GeneralSecurityException;
import java.util.Collections;
import java.util.List;

public class SheetsQuickstart {
    private static final String APPLICATION_NAME = "Google Sheets API Java Quickstart";
    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
    private static final String TOKENS_DIRECTORY_PATH = "tokens";

    /**
     * Global instance of the scopes required by this quickstart.
     * If modifying these scopes, delete your previously saved tokens/ folder.
     */
    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS_READONLY);
    private static final String CREDENTIALS_FILE_PATH = "/credentials.json";

    /**
     * Creates an authorized Credential object.
     * @param HTTP_TRANSPORT The network HTTP Transport.
     * @return An authorized Credential object.
     * @throws IOException If the credentials.json file cannot be found.
     */
    private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
        // Load client secrets.
        InputStream in = SheetsQuickstart.class.getResourceAsStream(CREDENTIALS_FILE_PATH);
        GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));

        // Build flow and trigger user authorization request.
        GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(
                HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
                .setDataStoreFactory(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH)))
                .setAccessType("offline")
                .build();
        LocalServerReceiver receier = new LocalServerReceiver.Builder().setPort(8888).build();
        return new AuthorizationCodeInstalledApp(flow, receier).authorize("user");
    }

    /**
     * Prints the names and majors of students in a sample spreadsheet:
     * https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
     */
    public static void main(String... args) throws IOException, GeneralSecurityException {
        // Build a new authorized API client service.
        final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        final String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
        final String range = "Class Data!A2:E";
        Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
                .setApplicationName(APPLICATION_NAME)
                .build();
        ValueRange response = service.spreadsheets().values()
                .get(spreadsheetId, range)
                .execute();
        List<List<Object>> values = response.getValues();
        if (values == null || values.isEmpty()) {
            System.out.println("No data found.");
        } else {
            System.out.println("Name, Major");
            for (List row : values) {
                // Print columns A and E, which correspond to indices 0 and 4.
                System.out.printf("%s, %s\n", row.get(0), row.get(4));
            }
        }
    }
}

Update 2020

There is a way to grant per file access.

https://www.googleapis.com/auth/drive.file Per-file access to files created or opened by the app. File authorization is granted on a per-user basis and is revoked when the user deauthorizes the app.

Answer 3

I think, this is what you're requesting for. ( https://developers.google.com/sheets/api/quickstart/java , https://www.youtube.com/watch?v=zDxTSUWaZs4 ) I am using this code to access a google sheet via ID

public class ConnectToDatabase extends AsyncTask<Object, Integer, Long> {

    private static final String APPLICATION_NAME = "Google Sheets API Java Quickstart";
    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
    private static final String TOKENS_DIRECTORY_PATH = "tokens";
    private static String SPREADSHEET_ID = INSERTYOURIDHERE;
    private static MainActivity main_Activity = null;
    /**
     * Global instance of the scopes required by this quickstart.
     * If modifying these scopes, delete your previously saved tokens/ folder.
     */
    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS_READONLY);
    private static final String CREDENTIALS_FILE_PATH = "credentials.json";


    public ConnectToDatabase(MainActivity mainActivity) {
        this.main_Activity = mainActivity;
    }

    private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {

        // Load client secrets.
        InputStream in =
                main_Activity.getAssets().open("credentials.json");
        //new FileInputStream(CREDENTIALS_FILE_PATH);
        ConnectToDatabase.class.getResourceAsStream(CREDENTIALS_FILE_PATH);
        GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));


        // Build flow and trigger user authorization request.

        GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(
                HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)
                .setDataStoreFactory(new FileDataStoreFactory(main_Activity.getDir(TOKENS_DIRECTORY_PATH, Context.MODE_APPEND)))
                .setAccessType("offline")
                .build();


        AuthorizationCodeInstalledApp ab = new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()){
            protected void onAuthorization(AuthorizationCodeRequestUrl authorizationUrl) throws IOException {
                String url = (authorizationUrl.build());
                        /*flow.newAuthorizationUrl()
                        .setScopes(flow.getScopes())
                        .setAccessType("offline")
                        .setClientId(clientSecrets.getDetails().getClientId())
                        .setRedirectUri("/oauth2-callback")
                        .toString();
*/

                Intent browserIntent = new Intent(Intent.ACTION_VIEW, Uri.parse(url));
                main_Activity.startActivity(browserIntent);
            }
        };
        Credential a = ab.authorize("user");


        return a;
    }

    /**
     * Prints the names and majors of students in a sample spreadsheet:
     * https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
     */
    public static void main(String[] args) throws IOException, GeneralSecurityException {
    // Build a new authorized API client service.
            final NetHttpTransport HTTP_TRANSPORT = new com.google.api.client.http.javanet.NetHttpTransport();
            final String range = "A1:H";
            Sheets service = null;
            try {
                service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
                        .setApplicationName(APPLICATION_NAME)
                        .build();
            } catch (IOException e) {
                e.printStackTrace();
            }
            ValueRange response = null;

            try {
                response = service.spreadsheets().values()
                        .get(SPREADSHEET_ID, range)
                        .execute();
            } catch (IOException e) {
                e.printStackTrace();
            }

            List<List<Object>> values = response.getValues();
            [...]
    }
}

You might need to modify this code snippet according to your needs a bit. I used this as a part of an app of mine.

So what will this code do? On execution, this code will connect to google with your personal API key, which I named credentials.json. (Create your own at: https://developers.google.com/+/web/api/rest/oauth ) After successfull authentication, you will be able to access a google sheet with a particular ID.