Google Sheets API Sort column by date

Question

I want to, by using python googleapiclient.discovery, sort a google sheet by a column of dates. I assume there may be a way to do this via batchUpdate, given this, but I can't quite figure out what the syntax would be here.

In gs, the sort I want to do looks like:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
  var range = sheet.getRange(3, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
  range.sort(1)
}

I did see that there is a way using googleapiclient (.scripts()) to call a gs script like above but I want to avoid turning this into a Cloud Project. I am also aware that a script like above could be set on an on edit trigger but ideally I do not want to not trigger this on every change, only when another external job runs.

Tanaike · Accepted Answer · 2020-08-31 22:32:24Z

Google Sheets API Sort column by date Google Sheets API Sort column by date https://stackoverflow.com/users/9004050/gustavmauler

I believe your goal and situation as follows.

You want to convert the Google Apps Script in your question to the python script.
At python script, you want to use googleapis for python.
You have already been able to get and put values for Google Spreadsheet using Sheets API.

In this case, the method of "spreadsheets.batchUpdate" of Sheets API is used.

Sample script:

Before you use this script, please set the Spreadsheet ID and sheet ID.

spreadsheet_id = '###'  # Please set the spreadsheet ID.
sheet_id = '###'  # Please set the sheet ID.

service = build('sheets', 'v4', credentials=creds)
requests = {
    "requests": [
        {
            "sortRange": {
                "range": {
                    "sheetId": sheet_id,
                    "startRowIndex": 2,
                    "startColumnIndex": 0
                },
                "sortSpecs": [
                    {
                        "dataSourceColumnReference": {
                            "name": "A"
                        },
                        "sortOrder": "ASCENDING"
                    }
                ]
            }
        }
    ]
}
res = service.spreadsheets().batchUpdate(body=requests, spreadsheetId=spreadsheet_id).execute()
print(res)

When above script is run, the same result of your Google Apps Script can be obtained.
In this sample, range object means sheet.getRange(3, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).
- In the case of getRange(3, 1, sheet.getLastRow() - 1, sheet.getLastColumn()), for example, when the data range is "A1:B10", getRange(3, 1, sheet.getLastRow() - 1, sheet.getLastColumn()) is "A3:B11". So I used above range.

Note:

About the authorization script, please check the Quickstart. Ref- This sample script supposes that you have already been able to use Sheets API.

Collectives™ on Stack Overflow

Google Sheets API Sort column by date

1 Answer 1

Sample script:

Note:

References:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Note:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related