Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Appearance settings
Discussion options

I'm using batchUpdateByDataFilter with a gridRange to update a sheet.
I can get the request to work, but I've noticed that I cannot match the shape of my gridRange to the shape of my values, unless the shape of my values is square (equal length for rows and columns).

I don't know if this is expected behavior or not, but the fact that I'm able to pass a gridRange with independent lengths for width and height seems to suggest that its not. Is this a bug or expected?

Example request body

    values = [
        ['X', 'X', 'X'],
    ]
    body = {
        "valueInputOption": 'USER_ENTERED',
        "data": [
            {"dataFilter": {
                'gridRange': {
                    'sheetId': id_sheet,
                    'startRowIndex': 0,
                    'endRowIndex': 1,
                    'startColumnIndex': 0,
                    'endColumnIndex': 3,
                },
            }, "values": values}
        ]
    }

results in a 400 with this response

Invalid dataFilterValueRange[0]: dataFilterValueRange.dataFilter matched range Sheet2!$A$1:$C$1 whose rows cannot fit the data described in data.values[0]

For this example I have to increment the endRowIndex up to 3 for the request to succeed.
Are bounded gridRanges required to be square?

I can see in the documentation, the only examples are for square, half-bounded, or fully unbounded ranges.
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#gridrange

UPDATE
Testing a little bit more and I've found that I can not issue an update when passing a greater than 26 rows, suggesting that I am bounded to an equivalent column width, even when I issue the command with and unbounded range.
Returned 400

Invalid dataFilterValueRange[0]: dataFilterValueRange.dataFilter matched range Sheet2!$A$1:$Z$1000 whose dimension_unspecified cannot fit the data described in data.values.

v2.62.0 / Python 3.10 on Windows 10

You must be logged in to vote

I've got the same problem with versions 2.128.0 and 2.147.0, and by trial and error, I have found that it is fixed by specifying the majorDimension of the DataFilterValueRange. So in your case that would be:

    values = [
        ['X', 'X', 'X'],
    ]
    body = {
        "valueInputOption": 'USER_ENTERED',
        "data": [{
            "dataFilter": {
                'gridRange': {
                    'sheetId': id_sheet,
                    'startRowIndex': 0,
                    'endRowIndex': 1,
                    'startColumnIndex': 0,
                    'endColumnIndex': 3,
                },
            }, 
            "majorDimension": 'ROWS',  # <-- THIS
            "values": 

Replies: 1 comment

Comment options

I've got the same problem with versions 2.128.0 and 2.147.0, and by trial and error, I have found that it is fixed by specifying the majorDimension of the DataFilterValueRange. So in your case that would be:

    values = [
        ['X', 'X', 'X'],
    ]
    body = {
        "valueInputOption": 'USER_ENTERED',
        "data": [{
            "dataFilter": {
                'gridRange': {
                    'sheetId': id_sheet,
                    'startRowIndex': 0,
                    'endRowIndex': 1,
                    'startColumnIndex': 0,
                    'endColumnIndex': 3,
                },
            }, 
            "majorDimension": 'ROWS',  # <-- THIS
            "values": values
        }]
    }
You must be logged in to vote
0 replies
Answer selected by nraffuse
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Category
🙏
Q&A
Labels
None yet
2 participants
Morty Proxy This is a proxified and sanitized view of the page, visit original site.