Can't Delete Named Ranges when they are broken or with "#REF"

[makubexD]

Important: Please do not post usage questions here.
To get a quick response, please ask a question on Stack Overflow using gspread tag.
See existing questions: https://stackoverflow.com/questions/tagged/gspread

---

Describe the bug
I'm trying to delete some named ranges I already created in a sheet called "StaticDataSheet". However, I get this message

gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid requests[0].addNamedRange: Cannot add named range with name: Range1, a named range with that name already exists.', 'status': 'INVALID_ARGUMENT'}

This is because I decided to delete this sheet manually. When doing so, the named ranges are not explicitly deleted and remain listed.
Here is a sample

So each time, I'm trying to use this:

spread_sheet_main.list_named_ranges()

When I try to display the list, I only get an empty array [], and there is no option to delete named ranges with "#REF" errors

To Reproduce
Steps to reproduce the behavior:

1. Create a new sheet with a specific name to identify it, along with an extra sheet in addition to the default "Sheet1." Then, create some named ranges.
2. Verify that the named ranges were created correctly by navigating to Data → Named Ranges in the menu.
3. Delete the recently created sheet.
4. Check Data → Named Ranges again.
5. You should now see "#REF" for the previously created "named ranges".
6. In your code, try using spread_sheet_main.list_named_ranges(). You will receive an empty array. With an empty result, there is nothing available to delete.
7. In the code, assume you proceed to create these "named ranges" again.
8. When the program reaches the code for creating named ranges, it encounters an error due to the existing #REF entries.
   gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid requests[0].addNamedRange: Cannot add named range with name: Range1, a named range with that name already exists.', 'status': 'INVALID_ARGUMENT'}

Expected behavior
if we have implemented a logic to delete we should delete without any problem

Code example*

def delete_existing_named_ranges(self, spreadsheet, range_names):
    """Delete named ranges with specified names if they already exist."""
    existing_named_ranges = spreadsheet.list_named_ranges()
    update_requests = []

    for named_range in existing_named_ranges:
        if named_range['name'] in range_names:
            delete_request = {
                "deleteNamedRange": {
                    "namedRangeId": named_range['namedRangeId']
                }
            }
            update_requests.append(delete_request)
            print(f"Deleting existing named range: {named_range['name']}")

    if update_requests:
        spreadsheet.batch_update({"requests": update_requests})

For further details in implementation, I created a repo with all the logic https://github.com/makubexD/namedRangesGSBug

Screenshots

Environment info:

• Operating System [Windows]:
• Python version: python --version --> Python 3.10.8
• gspread version: Version: 5.12.4

Stack trace or other output that would be helpful

Additional context
Add any other context about the problem here.

[alifeee]

hi, thank you for the report :]

unfortunately, it seems that this is an issue with the Google API. We cannot get the API to return the invalid named ranges:

• https://issuetracker.google.com/issues/126262167?pli=1
• https://tanaikech.github.io/2022/07/06/removing-invalid-named-ranges-from-google-spreadsheet-using-google-apps-script/

The relevant functions are:

• spreadsheet.py
  • Spreadsheet.named_range(name: str)
  • Spreadsheet.list_named_ranges()
• worksheet.py
  • Worksheet.define_named_range(name: str, range_name: str)
  • Worksheet.delete_named_range(named_range_id: str)
• spreadsheet_test.py - no relevant tests
• worksheet_test.py - no relevant tests

here is some code to test the issue. It runs fine the first time, but on a 2nd run, it raises the following error due to an existing range. However, this range does not appear in calls to spreadsheet.list_named_ranges()

gspread.exceptions.APIError: APIError: [400]: Invalid requests[0].addNamedRange: Cannot add named range with name: numbers_subset, a named range with that name already exists.

import gspread

SPREADSHEET_ID = "ahwdh238f2h89h8f2h8fg2"

gc = gspread.service_account(filename="./creds.json")

# Open a sheet from a spreadsheet in one go
spreadsheet = gc.open_by_key(SPREADSHEET_ID)

print("initial named ranges")
print(spreadsheet.list_named_ranges())

try:
    worksheet = spreadsheet.worksheet("temporary sheet")
except gspread.exceptions.WorksheetNotFound as ex:
    worksheet = spreadsheet.add_worksheet("temporary sheet", 5, 5)

worksheet.append_rows([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
worksheet.define_named_range("a1:c3", "numbers")
worksheet.define_named_range("a1:a3", "numbers_subset")  # this line fails on 2nd try

print()
print("after defining named range")
print(spreadsheet.list_named_ranges())
print("numbers")
print(spreadsheet.named_range("numbers"))
print(spreadsheet.named_range("numbers_subset"))

worksheet.delete_named_range(
    [r for r in spreadsheet.list_named_ranges() if r["name"] == "numbers"][0][
        "namedRangeId"
    ]
)

print()
print("after deleting named range")
print(spreadsheet.list_named_ranges())
print(spreadsheet.named_range("numbers_subset"))

spreadsheet.del_worksheet(worksheet)

print()
print("after deleting worksheet")
print(spreadsheet.list_named_ranges())  # "numbers_subset" is not listed here

so, it seems the only solution is to manually delete invalid ranges (see the google issue tracker above)

we cannot really do much to fix this, other than attempting to make the error nicer.

if you have any other thoughts, I'd love to hear them. Otherwise, I will close this issue. Thanks again for the report :)