Sorts a gspread worksheet using given sort orders.

sort_worksheet.py

# Copyright (c) 2019 Kirill Gashkov

import gspread
import gspread.utils


def sort_worksheet(wks, *specs, range=None):
    """Sorts a gspread worksheet using given sort orders.

    :param wks: The worksheet to sort.
    :type wks: gspread.Worksheet
    :param specs: The sort order per column. Each sort order represented
    by a tuple where the 1st element is a column index and 2nd element is
    the order itself: 'asc' or 'des'.
    :type specs: List[Tuple[int, str]]
    :param range: The range to sort. By default sorts whole sheet excluding
    frozen rows. Range notation: 'A1:A1'.
    :type range: str

    Example::

        # Sort sheet A -> Z by column 'B'
        sort_worksheet(wks, (2, 'asc'))

        # Sort range A2:G8 basing on column 'G' A -> Z and column 'B' Z -> A
        sort_worksheet(wks, (7, 'asc'), (2, 'des'), range='A2:G8')

    """

    if range:
        start_a1, end_a1 = range.split(':')
        start_row, start_col = gspread.utils.a1_to_rowcol(start_a1)
        end_row, end_col = gspread.utils.a1_to_rowcol(end_a1)
    else:
        start_row = wks._properties['gridProperties'].get('frozenRowCount', 0) + 1
        start_col = 1
        end_row = wks.row_count
        end_col = wks.col_count
    request_range = {
        'sheetId': wks.id,
        'startRowIndex': start_row - 1,
        'endRowIndex': end_row,
        'startColumnIndex': start_col - 1,
        'endColumnIndex': end_col
    }

    request_sort_specs = list()
    for col, order in specs:
        if order == 'asc':
            request_order = 'ASCENDING'
        elif order == 'des':
            request_order = 'DESCENDING'
        else:
            raise ValueError("Either 'asc' or 'des' should be specified as sort order.")
        request_sort_spec = {
            "dimensionIndex": col - 1,
            "sortOrder": request_order
        }
        request_sort_specs.append(request_sort_spec)

    body = {
        'requests': [{
            'sortRange': {
                'range': request_range,
                'sortSpecs': request_sort_specs,
            },
        }]
    }

    response = wks.spreadsheet.batch_update(body)
    return response