Acceder a hojas de cálculo de Google Docs mediante API desde una aplicación Java

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

package io.github.picodotdev.javagoogleapi;

...

public class Main {

    ...

    private static Credential getCredentialsApiKey() {
        return new GoogleCredential().createScoped(SCOPES);
    }

    ...

    public static void main(String... args) throws IOException, GeneralSecurityException {
        NetHttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();
        JsonFactory jsonFactory = JacksonFactory.getDefaultInstance();
        Sheets service = new Sheets.Builder(httpTransport, jsonFactory, getCredentialsApiKey()).setApplicationName(APPLICATION_NAME).build();

        ...
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

$ curl -X GET "https://sheets.googleapis.com/v4/spreadsheets/1JhBPGW4F.../values/Hoja1?key=AIzaSyDGwW..."
{
  "range": "Hoja1!A1:AA1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "a1",
      "a3"
    ],
    [
      "a2",
      "a4"
    ]
  ]
}

1
2
3
4

$ curl -X GET "https://www.googleapis.com/drive/v3/files/1YCnD37w6p.../export?key=AIzaSyDGwW...&mimeType=text/plain"
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation 
ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint
occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

package io.github.picodotdev.javagoogleapi;

...

public class Main {

    ...
    
    private static final String CREDENTIALS_FILE_PATH = "/blogbitix-119471bc8ebf.json";

    ...

    private static Credential getCredentialsServiceAccount(NetHttpTransport httpTransport, JsonFactory jsonFactory) throws IOException {
        InputStream in = Main.class.getResourceAsStream(CREDENTIALS_FILE_PATH);
        return GoogleCredential.fromStream(in, httpTransport, jsonFactory).createScoped(SCOPES);
    }

    public static void main(String... args) throws IOException, GeneralSecurityException {
        NetHttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();
        JsonFactory jsonFactory = JacksonFactory.getDefaultInstance();
        Sheets service = new Sheets.Builder(httpTransport, jsonFactory, getCredentialsServiceAccount(httpTransport, jsonFactory)).setApplicationName(APPLICATION_NAME).build();

        ...
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

package io.github.picodotdev.javagoogleapi;

...

public class Main {

    ...

    public static void main(String... args) throws IOException, GeneralSecurityException {
        ...

        Sheets service = new Sheets.Builder(httpTransport, jsonFactory, getCredentialsApiKey()).setApplicationName(APPLICATION_NAME).build();
        ValueRange response = service.spreadsheets().values().get(SPREADSHEET_ID, RANGE).setKey(API_KEY).execute();
        List<List<Object>> values = response.getValues();

        if (values == null || values.isEmpty()) {
            System.out.println("No data found.");
            return;
        }

        for (List<Object> row : values) {
            System.out.println(row.stream().map(Object::toString).collect(java.util.stream.Collectors.joining(", ")));
        }
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

$ ./gradlew run
> Task :compileJava
> Task :processResources UP-TO-DATE
> Task :classes

> Task :run
a1, a3
a2, a4

BUILD SUCCESSFUL in 1s
3 actionable tasks: 2 executed, 1 up-to-date

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

{
  "type": "service_account",
  "project_id": "blogbitix",
  "private_key_id": "119471bc8...",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDFQpNVknb3bRp9\n...\n-----END PRIVATE KEY-----\n",
  "client_email": "blogbitix@blogbitix.iam.gserviceaccount.com",
  "client_id": "110222042...",
  "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_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/blogbitix%40blogbitix.iam.gserviceaccount.com"
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

package io.github.picodotdev.javagoogleapi;

....

public class Main {

    private static final String APPLICATION_NAME = "JavaGoogleApi";
    private static final String API_KEY = "AIzaSyDBZ...";
    private static final String CREDENTIALS_FILE_PATH = "/blogbitix-119471bc8ebf.json";
    private static final String SPREADSHEET_ID = "1JhBPGW4F...";
    private static final String RANGE = "Hoja1";
    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS_READONLY);

    private static Credential getCredentialsApiKey() {
        return new GoogleCredential().createScoped(SCOPES);
    }

    private static Credential getCredentialsServiceAccount(NetHttpTransport httpTransport, JsonFactory jsonFactory) throws IOException {
        InputStream in = Main.class.getResourceAsStream(CREDENTIALS_FILE_PATH);
        return GoogleCredential.fromStream(in, httpTransport, jsonFactory).createScoped(SCOPES);
    }

    public static void main(String... args) throws IOException, GeneralSecurityException {
        NetHttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();
        JsonFactory jsonFactory = JacksonFactory.getDefaultInstance();
        Sheets service = new Sheets.Builder(httpTransport, jsonFactory, getCredentialsServiceAccount(httpTransport, jsonFactory)).setApplicationName(APPLICATION_NAME).build();
        ValueRange response = service.spreadsheets().values().get(SPREADSHEET_ID, RANGE).setKey(API_KEY).execute();
        List<List<Object>> values = response.getValues();

        if (values == null || values.isEmpty()) {
            System.out.println("No data found.");
            return;
        }

        for (List<Object> row : values) {
            System.out.println(row.stream().map(Object::toString).collect(java.util.stream.Collectors.joining(", ")));
        }
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

apply plugin: 'java'
apply plugin: 'application'

mainClassName = 'io.github.picodotdev.javagoogleapi.Main'
version = '1.0'

repositories {
    mavenCentral()
}

dependencies {
    compile 'com.google.apis:google-api-services-sheets:v4-rev553-1.25.0'
}