0

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
TSR
  • 17,242
  • 27
  • 93
  • 197

2 Answers2

3

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.

Community
  • 1
  • 1
Mr.Rebot
  • 6,703
  • 2
  • 16
  • 91
0

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

String cellReq = (new FindReplaceRequest().setFind("samuel")).getFind();
  • 1
    @user6889641 can you give a brief example as I checked the response does not contain the string found. It returns only the number if replacements were done if mentioned in the request. As per https://developers.google.com/sheets/api/guides/batchupdate example, it works only for replacing a string. I want the whole ROW to be fetched if the string is found. – Harshit Nov 07 '18 at 06:38