Java | How to find a value (row and column number)

Question

I am using the Java API (V4) to read and edit the data from a google sheet. So far, I am able to read and edit the data based on the row and column number (index).

• What I am want to do is to localise a data by its specific value ( I want to get the row and the column number of a specific cell using its value).

So far this is a part of the working code I used to edit the data:

  // Copy the format from A1:C1 and paste it into A2:C5, so the data in 
// each column has the same background.             
requests.add(new Request()
                        .setCopyPaste(new CopyPasteRequest()
                                .setSource(new GridRange()
                                        .setSheetId(0)
                                        .setStartRowIndex(0)
                                        .setEndRowIndex(1)
                                        .setStartColumnIndex(0)
                                        .setEndColumnIndex(3))
                                .setDestination(new GridRange()
                                        .setSheetId(0)
                                        .setStartRowIndex(1)
                                        .setEndRowIndex(6)
                                        .setStartColumnIndex(0)
                                        .setEndColumnIndex(3))
                                .setPasteType("PASTE_FORMAT")));

                BatchUpdateSpreadsheetRequest batchUpdateRequest = new BatchUpdateSpreadsheetRequest()
                        .setRequests(requests);

Can anyone please help me? Thank you in advance.

Answer 1

Find text is not yet available for Google Sheet API. Please star this issue tracker FR to get notified on any updates.

A workaround based on this SO answer, "You can get the data for the range you are searching on, and then iterate over it looking for a match. " Here is his code snippet:

/**
* Finds a value within a given range. 
* @param value The value to find.
* @param range The range to search in.
* @return A range pointing to the first cell containing the value, 
* or null if not found.
*/
function find(value, range) {
var data = range.getValues();
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j++) {
if (data[i][j] == value) {
return range.getCell(i + 1, j + 1);
}
}
}
return null;
}

NOTE: Code sample is in google-apps-script

Like in this code snippet, you will need to set the range first, then check if value will match inside the range.

Answer 2

According to the documentation, you can find the value using : FindReplaceRequest() and .getFind();

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