Google Sheets API: How to find a row by value and update it's content

Question

I am working on an Android application that uses a Google Spreadsheet as a database. The application should GET, APPEND and UPDATE values in a spreadsheet, using the Sheets API v4. The first two functions are working fine but I have difficulties updating a specific row. I need to find a row that has a specific value in it's first column ("Batch ID") and update all the cells in this row.

This is how my spreadsheet looks like.

Right now I am getting the row to be modified like this:

ValueRange response = this.mySheetsService.spreadsheets().
                values().get(spreadsheetId, range).execute();

List<List<Object>> values = response.getValues();
String rangeToUpdate;

Log.i(TAG, "all values in range: " + values.toString());

int i = 0;
if (values != null) {
    for (List row : values) {
        i += 1;
        if (row.get(0).equals(selectedBatchID)) {
            Log.i(TAG, "IT'S A MATCH! i= " + i);
            rangeToUpdate = "A" + (i + 1) + ":E" + (i + 1); //row to be updated
        }
    }
}
/*once I have the row that needs to be updated, I construct my new ValueRange requestbody and
*execute a values().update(spreadsheetId, rangeToUpdate , requestbody) request.
*/

This is actually working fine but I think it's an ugly solution and I am sure there is a better one out there.

I have read the Sheets API documentation and I got familiar with notions such as batchUpdateByDataFilter, DataFilterValueRange or DeveloperMetadata and I sense that I should use these features for what I'm trying to achieve but I couldn't put them together and I couldn't find any examples.

Can someone show me or help me understand how to use these Sheets V4 features?

Thank you.

No, I ended up using my "ugly" solution as it actually fits my needs. — ferenckovacsx, May 03 '18 at 10:59

Rusty Core · Answer 1 · 2018-03-22T22:12:39.650

I have exactly the same issue, and it seems that so far (March 2018) Sheets v4 API does not allow to search by value, returning cell address. The solution I found somewhere here on StackOverflow is to use a formula. The formula can be created in an arbitrary sheet each time you want to find the address by value, then you erase the formula. If you do not want to delete the formula every time, you many prefer to create in a safer place, like a hidden worksheet.

Create hidden worksheet LOOKUP_SHEET (spreadsheetId is your spreadsheet ID):

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate

{
 "requests": [
  {
   "addSheet": {
    "properties": {
     "hidden": true,
     "title": "LOOKUP_SHEET"
    }
   }
  }
 ]
}

Create a formula in the A1 cell of the hidden worksheet that searches for "Search value" in MySheet1 sheet, and get back the row:

PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/LOOKUP_SHEET!A1?includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE&valueInputOption=USER_ENTERED&fields=updatedData

{
 "range": "LOOKUP_SHEET!A1",
 "values": [
  [
   "=MATCH("Search value", MySheet1!A:A, 0)"
  ]
 ]
}

The response will look like this:

{
 "updatedData": {
  "range": "LOOKUP_SHEET!A1",
  "majorDimension": "ROWS",
  "values": [
   [
    3
   ]
  ]
 }
}

By default, major dimension is ROWS. MATCH() returns relative row within column A, if no row IDs are provided then this position is effectively absolute. Or, you may want to use a more reliable call like =ROW(INDIRECT(ADDRESS(MATCH("Search value",A:A,0),1))). If the sheet has spaces in it, enclose it in single quotes. If you are searching for number, make sure you do not enclose it in quotes.

Is the PUT call atomic? If it gets called a second time with a different lookup before the first call returns, is there a chance the response from the first call will reflect the second call's search? — user1114, Jan 18 '20 at 18:40
It is not atomic and raise conditions happen. The best way to account for it is to use append instead, and then use the response to clear the cell. — Leandro Zubrezki, Mar 05 '21 at 12:39

score 2 · Answer 2 · edited Jul 22 '20 at 00:09

I had the same requirement.

First: Create a function that gets the index of targeted object from the sheet, like:

private int getRowIndex(TheObject obj, ValueRange response) {
    List<List<Object>> values = response.getValues();
    int rowIndex = -1;

    int i = 0;
    if (values != null) {
        for (List row : values) {
            i += 1;
            if (row.get(1).equals(obj.getBatchId())) {
                System.out.println("There is a match! i= " + i);
                rowIndex = i;
            }
        }
    }

    return rowIndex;
}

Second: Create the update method by passing the targeted object having your desired value "batch id" and others new values for the rest of fields.

public void updateObject(Object obj) throws IOException, GeneralSecurityException {    
    sheetsService = getSheetsService();
    ValueRange response = sheetsService.spreadsheets().
            values().get(SPREADSHEET_ID, "Sheet1").execute();
    
    int rowIndex = this.getRowIndex(obj, response);
    
    if (rowIndex != -1) {
        List<ValueRange> oList = new ArrayList<>();
        oList.add(new ValueRange().setRange("B" + rowIndex).setValues(Arrays.asList(
                Arrays.<Object>asList(obj.getSomeProprty()))));
    
        oList.add(new ValueRange().setRange("C" + rowIndex).setValues(Arrays.asList(
                Arrays.<Object>asList(obj.getOtherProprty()))));
    
        //... same for others properties of obj
    
        BatchUpdateValuesRequest body = new BatchUpdateValuesRequest().setValueInputOption("USER_ENTERED").setData(oList);
        BatchUpdateValuesResponse batchResponse;
        batchResponse sheetsService.spreadsheets().values().batchUpdate(SPREADSHEET_ID, body).execute();
    } else {
        System.out.println("the obj dont exist in the sheet!");
    }
}

Finally: In your app you have to pass the tageted object to the update method:

TheObject obj = new Object();
obj.setBatchId = "some value";

Fill the obj with others values if you want.

Then call the method:

objectChanger.updateObject(obj);

how to delete that row found in rowIndex ? – Noor Hossain Dec 15 '20 at 09:37 — Noor Hossain, Dec 15 '20 at 09:37

score 0 · Answer 3 · answered Feb 03 '21 at 19:06

In the spreadsheets API we have the concept of developer metadata, that allow us to store information not visible to the end user that we can later on retrieve and use. In this case the best approach is to assign the Batch ID as a metadata for a particular row. I will add the code based on the Javascript SDK.

const response = await sheets.spreadsheets.developerMetadata.search({
  auth: jwtClient,
  spreadsheetId,
  requestBody: {
    dataFilters: [
      {
        developerMetadataLookup: {
          locationType: 'ROW',
          metadataKey: 'batchId',
          metadataValue: '$BATCH_ID'
        }
      }
    ]
  }
});
if (response.matchedDeveloperMetadata) {
  // There is a row with that id already present.
  const { endIndex } = response.matchedDeveloperMetadata[0].developerMetadata.location.dimensionRange;
  // Use endIndex to create the range to update the values range: `SheetName!A${endIndex}`,
  await sheets.spreadsheets.values.update(
    {
      auth: jwtClient,
      spreadsheetId,
      range: `SheetName!A${endIndex}`,
      valueInputOption: 'USER_ENTERED',
      requestBody: {
        majorDimension: 'ROWS',
        values: [[]]
      },
    },
    {}
  );
} else {
  // Append the value and create the metadata.
  const appendResponse = await sheets.spreadsheets.values.append(
    {
      auth: jwtClient,
      spreadsheetId,
      range: 'SheetName!A1',
      valueInputOption: 'USER_ENTERED',
      requestBody: {
        majorDimension: 'ROWS',
        values: [[]]
      },
    },
    {}
  );
  if (appendResponse.data.updates?.updatedRange) {
    const updatedRange = appendResponse.data.updates?.updatedRange;
    const [, range] = updatedRange.split('!');
    const indexes = convertSheetNotation(range);
    await sheets.spreadsheets.batchUpdate({ auth: jwtClient, spreadsheetId, requestBody: {
      requests: [
        {
          createDeveloperMetadata: {
            developerMetadata: {
              location: {
                dimensionRange: {
                  sheetId: 0,
                  startIndex: indexes[0],
                  endIndex: indexes[0] + 1,
                  dimension: "ROWS"
                }
              },
              metadataKey: 'batchId',
              metadataValue: '$BATCH_ID',
              visibility: "DOCUMENT"
            }
          }
        }
      ]
    }});
  }
}

We need to be careful of race conditions as we may end up with duplicated rows, let me know if that helps :)

Google Sheets API: How to find a row by value and update it's content

3 Answers3

Linked