javagoogle-sheetsbatch-updates

Google sheets API Batch Update


Could someone please post an example of using Google's batchUpdateValuesRequest to set multiple value on a google sheet?

I am just trying to understand how to change values in a google sheet.

I can successfully authenticate (and read data from a sheet) but am having trouble formatting the update request and its data.

Thanks!


Solution

  • https://www.programcreek.com/java-api-examples/?api=com.google.api.services.sheets.v4.Sheets

    Examples of batch updates can be seen on the link above.

    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;
    }