Google Sheets API sort range

Question

I have a spreadsheet that I am appending data to using Java with Google Sheets API v4. My goal, in the end, is to effectively locate a row by the value in Column A. From what I have found, unless I want to iterate through every row until I find the right value, which in my case I do not have time for, there is no way to achieve this.

My next thought was to use the API to sort the spreadsheet then use a binary search (which I do have time for) to find the data I am looking for. However, I am unable to find any documentation/examples that have the call I would need to make to sort the range.

My spreadsheet looks something like this, except has a lot more data:

Any ideas how I would go about sorting this? I am open to other ideas about how to find the row effectively.

possible duplicate of https://stackoverflow.com/questions/49161249/google-sheets-api-how-to-find-a-row-by-value-and-update-its-content , but unfortunately they still "couldn't put them together" to get anything working — racraman, Feb 26 '19 at 23:14
Yes, I had seen that question but the asker said he ended up using the solution of just iterating through every item. — Lazerpent, Feb 26 '19 at 23:16
How is waiting an unknown amount of time for Google to sort your large data, then querying repeatedly (or querying once for the whole data and binary searching it) faster than just getting the keys you need to search over, sorting, and searching yourself? Have you timed this? — tehhowch, Feb 27 '19 at 02:00
Consider using developer metadata if you need to access specific rows — tehhowch, Feb 27 '19 at 02:01
@tehhowch because I can sort when I store the data, its when I am querying it that I need the efficiency — Lazerpent, Feb 27 '19 at 14:50
There is an API request that sorts a range by your desired column(s). Look at the Sheets API documentation for updating spreadsheets (not just spreadsheet values). I still don't understand how `values.get` of just column A, `map`ing to prefix the original row index, sorting and searching locally, and calling `values.get` for the associated whole row (2 network calls) is slower or less efficient than posting to Google to sort, waiting for that to complete, then `values.get` column A, search locally, then `values.get` the desired row (3 network calls). You still download the same amount of data. — tehhowch, Feb 28 '19 at 14:27

score 0 · Accepted Answer · answered Sep 26 '20 at 17:53

You can sort a Google sheet with a batchUpdate request; send requests like

"requests": [
  "sortRange": {
      "range": { "Sheet1!A1:A" },
      "sortSpecs": [
        {
          "sortOrder": "ASCENDING",
          "dimensionIndex": 1
        }
      ]
  }
]

dimensionIndex is the 0-indexed column (1 = column B)

Start from SortRangeRequest in the docs and click each parameter object type for more details.

Google Sheets API sort range

1 Answers1