get_all_worksheet_values feature request

[JPGarCar]

Is your feature request related to a problem? Please describe.
API limits is the problem. When wanting to read values from all or most of the worksheets in a google sheets I have to get the values of each worksheet one at a time, that is one API call per worksheet. This can make the program reach the API limit very quickly if enough users access the program.

Describe the solution you'd like
a get_all_worksheet_values() function that uses the batchGet endpoint to retrieve multiple worksheet data in one API call.
If a list of worksheet names are given, then use those, else grab all the worksheet names and retrieve all their values. I know the latter would involve making two API calls, but it would be worth on most cases.

I know the library has a values_batch_get() method. What I am requesting is an abstraction to this method.

I would prefer the function to return a dict of all the worksheet values, mapped by the worksheet name/title.

Describe alternatives you've considered
Only alternatives are calling for each worksheet data at a time, or just using values_batch_get().

Additional context
I am happy to tackle this feature, would just like to get some input from the devs on if the approach is correct and get any feedback or improvements.

[JPGarCar]

I was able to make a demo, it works!

        def get_all_data(self, skip_worksheet_titles: list[str] = None):
        """ Grabs all the data from all the worksheets in one API call. Skips any worksheets that were named in the
        skip_worksheet_title parm.
        :returns Dict of worksheet data with worksheet title as key
        """

        if skip_worksheet_titles is None:
            skip_worksheet_titles = []

        def colnum_to_string(n: int) -> str:
            """  Converts an int to a sheet column string index.
            Example: 0 -> A, 27 -> AA
            """
            string = ""
            while n > 0:
                n, remainder = divmod(n - 1, 26)
                string = chr(65 + remainder) + string
            return string

        def extract_title_from_range(range_string: str) -> str:
            """ Will extract the sheet title from a range.
            Example: "'Volunteer Portal'!A1:Z1005" -> "Volunteer Portal"
            """
            first_appearance = range_string.index("'")
            return range_string[first_appearance + 1:range_string.index("'", first_appearance + 1)]

        ranges = []

        for worksheet in self.google_sheet.worksheets():
            if worksheet.title not in skip_worksheet_titles:
                ranges.append('{}!A1:{}'.format(worksheet.title, colnum_to_string(worksheet.col_count)))

        values = self.google_sheet.values_batch_get(
            ranges=ranges
        )

        return_data = {}

        for values in values['valueRanges']:
            value_range = ValueRange.from_json(values)
            return_data[extract_title_from_range(value_range.range)] = fill_gaps(value_range)

        return return_data

which returns a dict, the worksheet values keyed by the worksheet title.

Everything is done in two API calls:

1. Grab all worksheet metadata
2. Grab all the worksheets' data using values_batch_get()

To make it production worthy I would add the function to take the Query Parameter parm from values_batch_get().

Thoughts @lavigne958?

[lavigne958]

Hi @JPGarCar

Thank you for this proposal.

It looks great. I would only do some minor changes (the way you compute a column number to a column name (1->A) but the major part of you code can be put into gspread.

If you like you can open the PR then I put my comments then we merge it, or you want send a patch (i can guide you) if you like so when I merge it you appear in the contributors list.

[JPGarCar]

I'll make a PR! Thanks @lavigne958

[lavigne958]

before you start here are 2 comments that can already take:

1. no need to create the function colnum_to_string we already have a function for it, here
   just use this one and strip give it a row number of 1 and remove it from the output.
2. for the function extract_title_from_range please use regexp, I can help with that if needed.

the rest is fine but I would need to take a deeper look and try it first to validate it.
Once your PR is open we'll see about the tests as well (I might write them myself).

[swimninja247]

Hi! Was this ever completed? If not I can put up a PR following the earlier replies' guidance.

[lavigne958]

nope it has not been done, plese go ahead and start a PR we'll start working from there.

We do need to work on the format of the returned values though 🤔

either you open the PR and we'll work from there and proceed to any changes if necessary or you can pos there a comment of the returned format, the new functions you'll create etc and we can discuss it first, either way if fine to me 😉

thank you for your help