Translate JSON request to Java Google sheet API V4

Question

In google Sheet API guide,

I read that a find request (find the cell with value 'X') can be formatted as follows in JSON:

{
  "find": string,
  "replacement": string,
  "matchCase": boolean,
  "matchEntireCell": boolean,
  "searchByRegex": boolean,
  "includeFormulas": boolean,

  // Union field scope can be only one of the following:
  "range": {
    object(GridRange)
  },
  "sheetId": number,
  "allSheets": boolean,
  // End of list of possible types for union field scope.
}

requests can be made in the form of this URL https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId .......

But I'm not able to translate that to JAVA code

The closest I've found was this code in one of the forums:

    requests.add(new Request().setFindReplace(new FindReplaceRequest().setFind(entry.getKey())
            .setMatchEntireCell(true)
            .setMatchCase(true)
            .setReplacement(entry.getValue())
            .setRange(new GridRange()
                    .setSheetId(0)
                    .setStartRowIndex(row)
                    .setEndRowIndex(row + 1))));
}



String cellReq = (new FindReplaceRequest().setFind("samuel")).getFind();

which lack a response that I expect from the API, letting me know which cell in the sheet has the value I'm trying to find.

thanks

You can just send a standard http request with the api key and json string to the url and then get an answer. What has a JAVA to do with that? You want to know how to send http request with JAVA? — Peter Majko
Thanks Peter for the answer, I just want to be consistent, and continue to use Google's libraries (Google API client, Oauth client, and the API service sheet) this takes care of Authentication and http requests and many other functions. and I see that google has a FindReplaceRequest() , but not documented enough for me to follow. — Tlink
But your question is vague. I don't really understand what is the issue. You cannot "translate JSON to JAVA". JSON is data interchange language, same as XML. JAVA is a programming language. You can use JAVA to send JSON structured data via some API to get the answer. Also I do not understand what you are trying to achieve - to me it seems, that you just want an ADDRESS of cell in google sheet with the value you are searching for. Is that correct? — Peter Majko
For JAVA Google Sheets API: developers.google.com/api-client-library/java/apis/sheets/v4 | For JAVA documentation on Google sheets API: developers.google.com/resources/api-libraries/documentation/… — Peter Majko
You're right, I didn't choose the best title. Here is a part of a JSON request to google sheets api { "range":" Sheet1!a2:a6 " "values": [ [ " Book " ] [ " Cat " ] [ " House " ] [ " Tree " ] ] } . This request basically defines a range of cells in Google sheet to be updated, and the values to be inserted into those cells. Yes I can create a client, but I'm using google's libraries, so the above JSON will be included in the code as ..... — Tlink

Sam Berlin Sam Berlin · Accepted Answer · 2017-11-16T12:47:36

It looks like you only partially copied the example from the Guide on this page: https://developers.google.com/sheets/api/guides/batchupdate#example

You need to place the FindReplaceRequest inside a BatchUpdateSpreadsheetRequest (in the requests field) and send it in a Spreadsheets.BatchUpdate call.

The full example is:

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());

Translate JSON request to Java Google sheet API V4

1 Answers