com.google.api.services.sheets.v4.Sheets Java Examples

public static void createSheet(List<Object> headers) throws IOException, GeneralSecurityException{
	if(onlyOnce)
		return;
	onlyOnce = true;	
	Sheets service = getService();
	String sheetID = getGitRepositoryState().commitId;
	List<Request> requests = new ArrayList<>(); 
	AddSheetRequest addSheet = new AddSheetRequest();
	addSheet.setProperties(new SheetProperties().setTitle(sheetID));
	requests.add(new Request().setAddSheet(addSheet));
	BatchUpdateSpreadsheetRequest requestBody = new BatchUpdateSpreadsheetRequest();
	requestBody.setRequests(requests);
	service.spreadsheets().batchUpdate(SPREADSHEET_ID, requestBody).execute();
	
	ArrayList<List<Object>> rows = Lists.newArrayList();
	rows.add(headers);
	ValueRange body = new ValueRange().setValues(Arrays.asList(headers));
	service.spreadsheets().values().append(SPREADSHEET_ID, sheetID, body).setValueInputOption("USER_ENTERED")
			.execute();
}
 

/**
 * Create Google sheets client factory with given root URL and server certificate.
 */
public static GoogleSheetsClientFactory createClientFactory(String rootUrl, String serverCertificate, boolean validateCertificates) throws GeneralSecurityException, IOException {
    NetHttpTransport.Builder transport = new NetHttpTransport.Builder();

    if (validateCertificates && ObjectHelper.isNotEmpty(serverCertificate)) {
        byte [] decoded = Base64.getDecoder().decode(serverCertificate.replaceAll(BEGIN_CERT, "")
                .replaceAll(END_CERT, ""));
        transport.trustCertificatesFromStream(new ByteArrayInputStream(decoded));
    } else {
        transport.doNotValidateCertificate();
    }

    return new BatchGoogleSheetsClientFactory(transport.build()) {
        @Override
        protected void configure(Sheets.Builder clientBuilder) {
            clientBuilder.setRootUrl(rootUrl);
        }
    };
}
 

public AppendValuesResponse appendValues(String spreadsheetId, String range,
                                         String valueInputOption, List<List<Object>> _values)
        throws IOException {
    Sheets service = this.service;
    // [START sheets_append_values]
    List<List<Object>> values = Arrays.asList(
            Arrays.asList(
                    // Cell values ...
            )
            // Additional rows ...
    );
    // [START_EXCLUDE silent]
    values = _values;
    // [END_EXCLUDE]
    ValueRange body = new ValueRange()
            .setValues(values);
    AppendValuesResponse result =
            service.spreadsheets().values().append(spreadsheetId, range, body)
                    .setValueInputOption(valueInputOption)
                    .execute();
    System.out.printf("%d cells appended.", result.getUpdates().getUpdatedCells());
    // [END sheets_append_values]
    return result;
}
 

public UpdateValuesResponse updateValues(String spreadsheetId, String range,
                                         String valueInputOption, List<List<Object>> _values)
        throws IOException {
    Sheets service = this.service;
    // [START sheets_update_values]
    List<List<Object>> values = Arrays.asList(
            Arrays.asList(
                    // Cell values ...
            )
            // Additional rows ...
    );
    // [START_EXCLUDE silent]
    values = _values;
    // [END_EXCLUDE]
    ValueRange body = new ValueRange()
            .setValues(values);
    UpdateValuesResponse result =
            service.spreadsheets().values().update(spreadsheetId, range, body)
                    .setValueInputOption(valueInputOption)
                    .execute();
    System.out.printf("%d cells updated.", result.getUpdatedCells());
    // [END sheets_update_values]
    return result;
}
 

public BatchGetValuesResponse batchGetValues(String spreadsheetId, List<String> _ranges)
        throws IOException {
    Sheets service = this.service;
    // [START sheets_batch_get_values]
    List<String> ranges = Arrays.asList(
            //Range names ...
    );
    // [START_EXCLUDE silent]
    ranges = _ranges;
    // [END_EXCLUDE]
    BatchGetValuesResponse result = service.spreadsheets().values().batchGet(spreadsheetId)
            .setRanges(ranges).execute();
    System.out.printf("%d ranges retrieved.", result.getValueRanges().size());
    // [END sheets_batch_get_values]
    return result;
}
 

public Sheets getSheetsService(String appName,
                               String clientSecretJSON) {
    try {
        HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        Credential credential = authorize(clientSecretJSON);
        return new Sheets.Builder(HTTP_TRANSPORT,
                                  JSON_FACTORY,
                                  credential)
                .setApplicationName(appName)
                .build();
    } catch (Exception e) {
        throw new IllegalArgumentException(e);
    }
}
 

public Sheets buildService(GoogleCredential credential) throws IOException,
    GeneralSecurityException {
  return new Sheets.Builder(
      GoogleNetHttpTransport.newTrustedTransport(),
      JacksonFactory.getDefaultInstance(),
      credential)
      .setApplicationName("Sheets API Snippets")
      .build();
}
 

public BatchUpdateValuesResponse batchUpdateValues(String spreadsheetId, String range,
                                                   String valueInputOption,
                                                   List<List<Object>> _values)
        throws IOException {
    Sheets service = this.service;
    // [START sheets_batch_update_values]
    List<List<Object>> values = Arrays.asList(
            Arrays.asList(
                    // Cell values ...
            )
            // Additional rows ...
    );
    // [START_EXCLUDE silent]
    values = _values;
    // [END_EXCLUDE]
    List<ValueRange> data = new ArrayList<>();
    data.add(new ValueRange()
            .setRange(range)
            .setValues(values));
    // Additional ranges to update ...

    BatchUpdateValuesRequest body = new BatchUpdateValuesRequest()
            .setValueInputOption(valueInputOption)
            .setData(data);
    BatchUpdateValuesResponse result =
            service.spreadsheets().values().batchUpdate(spreadsheetId, body).execute();
    System.out.printf("%d cells updated.", result.getTotalUpdatedCells());
    // [END sheets_batch_update_values]
    return result;
}
 

public ValueRange getValues(String spreadsheetId, String range) throws IOException {
    Sheets service = this.service;
    // [START sheets_get_values]
    ValueRange result = service.spreadsheets().values().get(spreadsheetId, range).execute();
    int numRows = result.getValues() != null ? result.getValues().size() : 0;
    System.out.printf("%d rows retrieved.", numRows);
    // [END sheets_get_values]
    return result;
}
 

public BatchUpdateSpreadsheetResponse batchUpdate(String spreadsheetId, String title,
                                                  String find, String replacement)
        throws IOException {
    Sheets service = this.service;
    // [START sheets_batch_update]
    List<Request> requests = new ArrayList<>();
    // Change the spreadsheet's title.
    requests.add(new Request()
            .setUpdateSpreadsheetProperties(new UpdateSpreadsheetPropertiesRequest()
                    .setProperties(new SpreadsheetProperties()
                            .setTitle(title))
                    .setFields("title")));
    // Find and replace text.
    requests.add(new Request()
            .setFindReplace(new FindReplaceRequest()
                    .setFind(find)
                    .setReplacement(replacement)
                    .setAllSheets(true)));
    // Add additional requests (operations) ...

    BatchUpdateSpreadsheetRequest body =
            new BatchUpdateSpreadsheetRequest().setRequests(requests);
    BatchUpdateSpreadsheetResponse response =
            service.spreadsheets().batchUpdate(spreadsheetId, body).execute();
    FindReplaceResponse findReplaceResponse = response.getReplies().get(1).getFindReplace();
    System.out.printf("%d replacements made.", findReplaceResponse.getOccurrencesChanged());
    // [END sheets_batch_update]
    return response;
}
 

public String create(String title) throws IOException {
    Sheets service = this.service;
    // [START sheets_create]
    Spreadsheet spreadsheet = new Spreadsheet()
            .setProperties(new SpreadsheetProperties()
                    .setTitle(title));
    spreadsheet = service.spreadsheets().create(spreadsheet)
            .setFields("spreadsheetId")
            .execute();
    System.out.println("Spreadsheet ID: " + spreadsheet.getSpreadsheetId());
    // [END sheets_create]
    return spreadsheet.getSpreadsheetId();
}
 

public Sheets buildSheetsService(GoogleCredential credential)
        throws IOException, GeneralSecurityException {
    return new Sheets.Builder(
            GoogleNetHttpTransport.newTrustedTransport(),
            JacksonFactory.getDefaultInstance(),
            credential)
            .setApplicationName("Slides API Snippets")
            .build();
}
 

@Provides
static Sheets provideSheets(
    @JsonCredential GoogleCredentialsBundle credentialsBundle,
    @Config("projectId") String projectId) {
  return new Sheets.Builder(
          credentialsBundle.getHttpTransport(),
          credentialsBundle.getJsonFactory(),
          credentialsBundle.getHttpRequestInitializer())
      .setApplicationName(projectId)
      .build();
}
 

public static void computeMetrics() throws IOException, GeneralSecurityException {
	Sheets service = getService();
	String sheetID = getGitRepositoryState().commitId;
	ArrayList<List<Object>> rows = Lists.newArrayList();
	ArrayList<Object> content = Lists.newArrayList();
	content.add(getGitRepositoryState().buildHost);
	content.add(getGitRepositoryState().buildTime);
	content.add(getGitRepositoryState().branch);
	content.add(sheetID);
	content.add("=GEOMITTEL('"+sheetID+"'!J2:J1004)");
	rows.add(content);
	ValueRange body = new ValueRange().setValues(rows);
	service.spreadsheets().values().append(SPREADSHEET_ID, "history", body).setValueInputOption("USER_ENTERED")
			.execute();
}
 

/**
 * Prints the names and majors of students in a sample spreadsheet:
 * https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
 * @throws GeneralSecurityException 
 * @throws IOException 
 */
public static void write(List<Object> data) throws IOException, GeneralSecurityException  {
	Sheets service = getService();
	String sheetID = getGitRepositoryState().commitId;
	ArrayList<List<Object>> rows = Lists.newArrayList();
	rows.add(data);
	ValueRange body = new ValueRange().setValues(rows);
	service.spreadsheets().values().append(SPREADSHEET_ID, sheetID, body).setValueInputOption("USER_ENTERED")
			.execute();
}
 

public static String fetchHeaderRow(String spreadsheetId, String range, String headerRow, Map<String, Object> properties) {
    RangeCoordinate rangeCoordinate = RangeCoordinate.fromRange(range);
    StringBuilder rangeBuilder = new StringBuilder();

    if (range.contains("!")) {
        rangeBuilder.append(range, 0, range.indexOf('!') + 1);
    }

    rangeBuilder.append(CellCoordinate.getColumnName(rangeCoordinate.getColumnStartIndex()))
                .append(headerRow)
                .append(':')
                .append(CellCoordinate.getColumnName(rangeCoordinate.getColumnEndIndex()))
                .append(headerRow);

    final String rootUrl = ConnectorOptions.extractOption(properties, "rootUrl", Sheets.DEFAULT_ROOT_URL);

    final boolean validateCertificates = ConnectorOptions.extractOptionAndMap(
        properties, "validateCertificates", Boolean::valueOf, false);

    final String serverCertificate = ConnectorOptions.extractOption(properties, "serverCertificate", "");

    try {
        final GoogleSheetsClientFactory clientFactory = GoogleSheetsConnectorHelper.createClientFactory(rootUrl, serverCertificate, validateCertificates);
        Sheets client = GoogleSheetsConnectorHelper.makeClient(clientFactory, properties);

        ValueRange valueRange = client.spreadsheets().values().get(spreadsheetId, rangeBuilder.toString()).execute();
        if (ObjectHelper.isNotEmpty(valueRange.getValues())) {
            StringJoiner joiner = new StringJoiner(",");
            valueRange.getValues().get(0).stream().map(Object::toString).forEach(joiner::add);
            return joiner.toString();
        }
    } catch (IOException | GeneralSecurityException e) {
        LOG.warn(String.format("Failed to fetch header row %s from spreadsheet %s", rangeBuilder.toString(), spreadsheetId), e);
    }

    return rangeCoordinate.getColumnNames();
}
 

/**
 * Create client from given client factory using properties or default values.
 */
public static Sheets makeClient(GoogleSheetsClientFactory clientFactory, Map<String, Object> properties) {
    final String clientId = ConnectorOptions.extractOption(properties, "clientId");
    final String clientSecret = ConnectorOptions.extractOption(properties, "clientSecret");
    final String applicationName = ConnectorOptions.extractOption(properties, "applicationName");

    return clientFactory.makeClient(clientId, clientSecret, applicationName,
                            ConnectorOptions.extractOption(properties, "refreshToken", ""),
                            ConnectorOptions.extractOption(properties, "accessToken", ""));
}
 

@Inject
public SheetsClient(SheetsConfig config, JsonCodec<Map<String, List<SheetsTable>>> catalogCodec)
{
    requireNonNull(config, "config is null");
    requireNonNull(catalogCodec, "catalogCodec is null");

    this.metadataSheetId = config.getMetadataSheetId();
    this.credentialsFilePath = config.getCredentialsFilePath();

    try {
        this.sheetsService = new Sheets.Builder(newTrustedTransport(), JSON_FACTORY, getCredentials()).setApplicationName(APPLICATION_NAME).build();
    }
    catch (GeneralSecurityException | IOException e) {
        throw new PrestoException(SHEETS_BAD_CREDENTIALS_ERROR, e);
    }
    long expiresAfterWriteMillis = config.getSheetsDataExpireAfterWrite().toMillis();
    long maxCacheSize = config.getSheetsDataMaxCacheSize();

    this.tableSheetMappingCache = newCacheBuilder(expiresAfterWriteMillis, maxCacheSize)
            .build(new CacheLoader<String, Optional<String>>()
            {
                @Override
                public Optional<String> load(String tableName)
                {
                    return getSheetExpressionForTable(tableName);
                }

                @Override
                public Map<String, Optional<String>> loadAll(Iterable<? extends String> tableList)
                {
                    return getAllTableSheetExpressionMapping();
                }
            });

    this.sheetDataCache = newCacheBuilder(expiresAfterWriteMillis, maxCacheSize).build(from(this::readAllValuesFromSheetExpression));
}
 

public SpreadsheetSnippets(Sheets service) {
    this.service = service;
}
 

Snippets(Slides service, Drive driveService, Sheets sheetsService) {
    this.service = service;
    this.driveService = driveService;
    this.sheetsService = sheetsService;
}
 

private static Sheets getService() throws IOException, GeneralSecurityException {
	// Build a new authorized API client service.
	final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
	return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY,  getCredentials(HTTP_TRANSPORT))
			.setApplicationName(APPLICATION_NAME).build();
}
 

public BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId) throws IOException {
    Sheets service = this.service;

    // Create two sheets for our pivot table.
    List<Request> sheetsRequests = new ArrayList<>();
    sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));
    sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest()));

    BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest()
            .setRequests(sheetsRequests);
    BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets()
            .batchUpdate(spreadsheetId, createSheetsBody).execute();
    int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties()
            .getSheetId();
    int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties()
            .getSheetId();

    // [START sheets_pivot_tables]
    PivotTable pivotTable = new PivotTable()
            .setSource(
                    new GridRange()
                            .setSheetId(sourceSheetId)
                            .setStartRowIndex(0)
                            .setStartColumnIndex(0)
                            .setEndRowIndex(20)
                            .setEndColumnIndex(7)
            )
            .setRows(Collections.singletonList(
                    new PivotGroup()
                            .setSourceColumnOffset(1)
                            .setShowTotals(true)
                            .setSortOrder("ASCENDING")
            ))
            .setColumns(Collections.singletonList(
                    new PivotGroup()
                            .setSourceColumnOffset(4)
                            .setShowTotals(true)
                            .setSortOrder("ASCENDING")
            ))
            .setValues(Collections.singletonList(
                    new PivotValue()
                            .setSummarizeFunction("COUNTA")
                            .setSourceColumnOffset(4)
            ));
    List<Request> requests = Lists.newArrayList();
    Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest()
            .setFields("*")
            .setRows(Collections.singletonList(
                    new RowData().setValues(
                            Collections.singletonList(
                                    new CellData().setPivotTable(pivotTable))
                    )
            ))
            .setStart(new GridCoordinate()
                    .setSheetId(targetSheetId)
                    .setRowIndex(0)
                    .setColumnIndex(0)

            ));

    requests.add(updateCellsRequest);
    BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest()
            .setRequests(requests);
    BatchUpdateSpreadsheetResponse result = service.spreadsheets()
            .batchUpdate(spreadsheetId, updateCellsBody).execute();
    // [END sheets_pivot_tables]
    return result;
}
 

public static Sheets getSheetsService() throws IOException, GeneralSecurityException {
    Credential credential = GoogleAuthorizeUtil.authorize();
    return new Sheets.Builder(GoogleNetHttpTransport.newTrustedTransport(), JacksonFactory.getDefaultInstance(), credential).setApplicationName(APPLICATION_NAME).build();
}