Python Google Spreadsheet API sorting not effective

Question

I use the google example https://developers.google.com/sheets/api/samples/charts (just switching lines 3 and 4 for the purpose of the test) to test the Google Spreadshhet API v4 with Python v3.7.8:

creation of a column chart
sorting the column 'A'

The data are stored in a Spreadsheet in a google Drive where I create the column chart.

The column chart is created but the the column 'A' is not sorted (ascending). Hereafter the code:

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

...

body = {'requests': [
    {'addChart': 
     {'chart': 
      {'spec': 
       {'title': 'Model Q1 Sales', 
        'titleTextPosition': {'horizontalAlignment': 'CENTER'}, 
        'basicChart': 
        {'domains': [
            {'domain': 
             {'sourceRange': 
              {'sources': [
                  {'sheetId': 909072886, 
                   'startRowIndex': 0, 
                   'endRowIndex': 7, 
                   'startColumnIndex': 0, 
                   'endColumnIndex': 1}]}}}], 
         'series': [
             {'series': 
              {'sourceRange': 
               {'sources': [
                   {'sheetId': 909072886, 
                    'startRowIndex': 0, 
                    'endRowIndex': 7, 
                    'startColumnIndex': 1, 
                    'endColumnIndex': 2}]}}, 
              'targetAxis': 'LEFT_AXIS'}, 
             {'series': 
              {'sourceRange': 
               {'sources': [
                   {'sheetId': 909072886, 
                    'startRowIndex': 0, 
                    'endRowIndex': 7, 
                    'startColumnIndex': 2, 
                    'endColumnIndex': 3}]}}, 
              'targetAxis': 'LEFT_AXIS'},   
             {'series': 
              {'sourceRange': 
               {'sources': [
                   {'sheetId': 909072886, 
                    'startRowIndex': 0, 
                    'endRowIndex': 7, 
                    'startColumnIndex': 3, 
                    'endColumnIndex': 4}]}}, 
              'targetAxis': 'LEFT_AXIS'}], 
         'legendPosition': 'BOTTOM_LEGEND', 
         'axis': [
             {'position': 'BOTTOM_AXIS', 
              'title': 'Model Numbers', 
              'titleTextPosition': {'horizontalAlignment': 'CENTER'}}, 
             {'position': 'LEFT_AXIS', 
              'title': 'Sales', 
              'titleTextPosition': {'horizontalAlignment': 'CENTER'}}], 
         'headerCount': 1, 
         'chartType': 'COLUMN',  
         'stackedType': 'STACKED'}, 
        'sortSpecs': [
            {'dimensionIndex': 0, 
             'sortOrder': 'ASCENDING'}]}, 
       'position': 
       {'overlayPosition': 
        {'anchorCell': 
         {'sheetId': 909072886, 
          'rowIndex': 7, 
          'columnIndex': 6}
        }
       }
      }
     }
    }
]
}

response = service.spreadsheets().batchUpdate(spreadsheetId=file_id, body=body).execute()

I should get the following:

But I get this: no effect of the sort part.

Remark: I also tried without success for the sortSpecs

'dataSourceColumnReference': {'name': 'A'}

in place of

'dimensionIndex': 0

Don't hesitate to support me! Thank you in advance.

Iamblichus Iamblichus · Accepted Answer · 2020-10-12T11:41:53

Issue:

When adding a Chart, sortSpecs is only supported for data coming from external data sources. From the API Reference:

sortSpecs[]: The order to sort the chart data by. Only a single sort spec is supported. Only supported for data source charts.

Solution:

You should sort the data itself. You can do this programmatically, by adding a sortRange request to your batchUpdate:

body = {
    'requests': [
        {
            'sortRange': {
                'range': {
                    'sheetId': 909072886,
                    'startRowIndex': 1,
                    'endRowIndex': 7,
                    'startColumnIndex': 0,
                    'endColumnIndex': 5
                },
                'sortSpecs': [
                    {
                        'sortOrder': 'ASCENDING',
                        'dimensionIndex': 0
                    }
                ]
            }
        },
        {
            'addChart': {
                # Add chart request body
            }
        }
    ]
}

Python Google Spreadsheet API sorting not effective

1 Answers

Issue:

Solution:

Reference: