4

I am working on an application (Apache Drill) and looking to build a connector to Google Sheets using the Google's Java API (v4).

I have this working, however, I would like to store the refresh token and here's where I'm stuck. Here's my existing code:

  public static Credential authorize(GoogleSheetsStoragePluginConfig config) throws IOException, GeneralSecurityException {
    GoogleClientSecrets clientSecrets = config.getSecrets();
    GoogleAuthorizationCodeFlow flow;
    List<String> scopes = Collections.singletonList(SheetsScopes.SPREADSHEETS);

    flow = new GoogleAuthorizationCodeFlow.Builder
      (GoogleNetHttpTransport.newTrustedTransport(), JSON_FACTORY, clientSecrets, scopes)
        .setDataStoreFactory(config.getDataStoreFactory())
        .setAccessType("offline")
        .build();
    return new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user");
  }

  public static Sheets getSheetsService(GoogleSheetsStoragePluginConfig config) throws IOException, GeneralSecurityException {

    Credential credential = GoogleSheetsUtils.authorize(config);
    return new Sheets.Builder(
      GoogleNetHttpTransport.newTrustedTransport(), GsonFactory.getDefaultInstance(), credential)
      .setApplicationName("Drill")
      .build();
  }

What I'd like to have is something like this:

public static Sheets getSheetsService(<client secrets>, String refreshToken, String accessToken) {
   // Not sure what to do here...
}

Any help would be greatly appreciated. 
Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
cgivre
  • 513
  • 4
  • 21

2 Answers2

0

I understand that you want to set up an authentication flow for the Sheets API in Java. In that case I advise you to get familiar with the service builder. As the docs says, it needs abstract objects (an HTTP transport, a JSON factory and an HTTP requests) to initialise correctly.

However there is a simpler approach that you can use to set up the authentication flow easily. Here you have a working example ready to be used. It has all the dependencies imported and all the flow already scripted. You can use that example as a base just by copying and pasting it in your project. Please keep in mind that you may need to install some libraries or set up credentials, so please read the rest of that page for more instructions.

UPDATE

After studying your new comments I believe that these are your goals:

  • Set up an OAuth 2.0 credentials flow on the Sheets API.
  • To create a new Sheet file.
  • Using a Java environment.

If those target are accurate, then you can use the following script:

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.gson.GsonFactory;
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.FileNotFoundException;
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 SheetsAPI {
  private static final String APPLICATION_NAME = "Sheets API";
  private static final JsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
  private static final String TOKENS_DIRECTORY_PATH = "tokens";
  private static final List < String > SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS_READONLY);
  private static final String CREDENTIALS_FILE_PATH = "/credentials.json";

  private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException {
    InputStream in = SheetsQuickstart.class.getResourceAsStream(CREDENTIALS_FILE_PATH);

    if ( in == null) {
      throw new FileNotFoundException("Resource not found: " + CREDENTIALS_FILE_PATH);
    }

    GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader( in ));
    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 receiver = new LocalServerReceiver.Builder().setPort(8888).build();
    return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");
  }

  public static void main(String...args) throws IOException, GeneralSecurityException {
    final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
    Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
      .setApplicationName(APPLICATION_NAME)
      .build();
    Spreadsheet sheet = new Spreadsheet().setProperties(new SpreadsheetProperties().setTitle("My new Sheet"));
    sheet = service.spreadsheets().create(sheet).setFields("spreadsheetId").execute();
    System.out.println("Sheet ID: " + sheet.getSpreadsheetId());
  }
}

That script is based on the Sheets API Java quickstart as discussed in my original answer. I have just modified the original code to interact with the Sheet.Spreadsheets class in order to create a new Sheet (and with the class SpreadsheetProperties too to give it a filename).

Please be aware that I am assuming two things. First, you already have a Java project structure to store the script above. Secondly, you already know your OAuth 2.0 credentials. Those credentials should be stored on a file called credentials.json inside your resources folder (ideally on src/main/resources/) with the format shown below. As an alternative, you could download a ready-for-use credentials.json file from the Cloud Platform.

{
  "installed": {
    "client_id": "012345678901-abcdefghijklmnopqrstuvwxyzabcdef.apps.googleusercontent.com",
    "project_id": "abcdef-012345",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_secret": "abcdefghijklmnopqrstuvwx",
    "redirect_uris": [
      "urn:ietf:wg:oauth:2.0:oob",
      "http://localhost"
    ]
  }
}

Please test this approach and let me know if it works for your scenario. Don't hesitate to drop a comment if you need further guidance.

Jacques-Guzel Heron
  • 2,480
  • 1
  • 7
  • 16
  • Thanks for responding, but unfortunately, that doesn't really answer my question. My code already does what's in that sample. What I am looking to accomplish is get the GoogleSheets service with a stored `refreshtoken`. – cgivre Jan 18 '22 at 00:16
  • I appreciate your details. Sadly, I don't understand what you mean by *get the GoogleSheets service*, could you please expand that? I assume that you already have a working Sheets API service similar to the docs example. – Jacques-Guzel Heron Jan 18 '22 at 09:10
  • Jacques, Thanks for responding! Let me add a little clarification. Firstly, the big picture: I'm developing a connector for Apache Drill which will allow a user to connect Drill to Google Sheets and query (and join) their data using SQL. As currently implemented, the user has to provide the details from Google so that Drill can obtain the `Sheets` object which is used to fetch the data. What happens now is that Drill stores the configuration infomration (securely) and gives the user a link to open a browser window, which then returns the `access_token` and `refresh_token`. – cgivre Jan 18 '22 at 14:20
  • We are also building some UI components that can obtain the `access_token` and `refresh_token` from Google (independently from Drill). The issue is that once we've obtained them, there is no apparent way to use shem to create the `Sheets` object. So, getting back to my sample code, what I'd like to do is create a second method (`getSheetsService`) to ontain the Sheets service whereby I can pass it the aforementioned tokens and it will create the `Sheets` object. Does that make sense? – cgivre Jan 18 '22 at 14:30
  • @cgivre I have just updated my answer to match that scenario. Check it and let me know how it goes. – Jacques-Guzel Heron Jan 26 '22 at 14:40
  • thanks for your response. You're close but not quite. Basically, I'm looking for a function that will return an authorized Sheets object, given a Credentials object and a refresh token. The key thing is that I want to provide the refresh token. Does that make sense? – cgivre Jan 26 '22 at 18:12
0

The Google api java client library is designed to handle all this for you. By default FileDataStoreFactory stores all of the credetinals with in a file on your machine in DATA_STORE_DIR.

dataStoreFactory = new FileDataStoreFactory(DATA_STORE_DIR);

If you dont want to store it in a file then you just need to create your own implementation of AbstractDataStoreFactory which will accept your refresh token.

Full FileDataStoreFactory sample

/**
   * Initializes an authorized sheets service object.
   *
   * @return The sheets service object.
   * @throws IOException
   * @throws GeneralSecurityException
   */
  private static Sheets initializeAnalyticsReporting() throws GeneralSecurityException, IOException {

    httpTransport = GoogleNetHttpTransport.newTrustedTransport();
    dataStoreFactory = new FileDataStoreFactory(DATA_STORE_DIR);

    // Load client secrets.
    GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY,
        new InputStreamReader(HelloSheets.class
            .getResourceAsStream(CLIENT_SECRET_JSON_RESOURCE)));

    // Set up authorization code flow for all authorization scopes.
    GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow
        .Builder(httpTransport, JSON_FACTORY, clientSecrets,
            SheetsScopes.all()).setDataStoreFactory(dataStoreFactory)
        .build();

    // Authorize.
    Credential credential = new AuthorizationCodeInstalledApp(flow,
        new LocalServerReceiver()).authorize("user");
    // Construct the sheets service object.
    return new Sheets.Builder(httpTransport, JSON_FACTORY, credential)
        .setApplicationName(APPLICATION_NAME).build();
  }
Linda Lawton - DaImTo
  • 106,405
  • 32
  • 180
  • 449
  • Thanks for the response, but I'm still not clear as to how this answers my question. Again, let's say that I had a valid `refresh_token`, `client_id`, and `client_secret`. Where do I put that? I don't think the `GoogleClientSecrets` object will hold that. – cgivre Jan 25 '22 at 19:59
  • My point is your refresh token is already being stored in DATA_STORE_DIR with the name of "user". The question is where did you want to store it exactly. – Linda Lawton - DaImTo Jan 26 '22 at 07:46
  • CLIENT_SECRET_JSON_RESOURCE denotes the location of your credetinals.json file, this is where the client id and client secrets should be stored. As you are using the default datastore FileDataStoreFactory the refresh token is stored in a file in DATA_STORE_DIR with the name of "user". These are two separate objects one holding your client credentials client id and client secrete. The second holding the user credentials, access token, refresh token. So you could place your refresh token in that file as long is in the proper format used by fileDatastoreFactory. – Linda Lawton - DaImTo Jan 26 '22 at 07:46
  • There is no default data store which would let you just send the refresh token. But you can make your own by making an implementation of AbstractDataStoreFactory for your purpose. You might want to checkout [MemoryDataStoreFactory.java](https://github.com/googleapis/google-http-java-client/blob/main/google-http-client/src/main/java/com/google/api/client/util/store/MemoryDataStoreFactory.java) this might help as well. – Linda Lawton - DaImTo Jan 26 '22 at 07:46