API Issue with list_sheets

Bobert

I script in python. I need to explain some things about how we use Smartsheet. We have a master sheet that has all of our current orders on it. We have around 200 projects, and each project has its own project sheet. When an order is placed via update request, a daily script picks up the info and creates the order on the master sheet.

To iterate through the project sheets in our scripts, we use list_sheets. list_sheets doesn't seem to list all of the sheets the service account we use for scripts is shared on. I have verified in the website GUI that the account is shared as an admin on the two sheets that won't list. We ran some test scripts and determined that the sheets don't list for two other accounts that are also shared on the sheets as admins. We were able to save the sheet as new, and it did list it. This is a good work around, however I really need to understand why the sheets are not being listed.

I recently saw the announcement that the API is changing and list_sheets is changing slightly. I ran a script that uses the old method, and new method to see if there is a difference in the listed sheets. I'm including the script below for context. Both methods returned the same list. There wasn't a difference between the two lists, so I am fairly certain I can rule out the recent changes to the list_sheets call.

I would really appreciate it anyone had an idea of why this is happening.

import smartsheet

api_token = 'xxxxxxxxxxxxxxxxxxxxxxxx'

smartsheet_client = smartsheet.Smartsheet(api_token)

def new_sheet_list_creation():
try:
response = smartsheet_client.Sheets.list_sheets(page = 1, page_size = 10000)

    if response.data:

        print('List of project sheets:')

        new_sheet_list = []

        for sheet in response.data:

            new_sheet_list.append(sheet.name)

            print(sheet.name)

    else:

        print('No sheets found in the account.')


    return new_sheet_list


except Exception as e:

    print(f'Error: {e}')

def old_sheet_list_creation():
try:
response = smartsheet_client.Sheets.list_sheets(include_all=True)

    if response.data:

        print('List of project sheets:')

        old_sheet_list = []

        for sheet in response.data:

            old_sheet_list.append(sheet.name)

            print(sheet.name)

    else:

        print('No sheets found in the account.')


    return old_sheet_list


except Exception as e:

    print(f'Error: {e}')

def main():
old_sheet_list = old_sheet_list_creation()
new_sheet_list = new_sheet_list_creation()

old_set = set(old_sheet_list)

new_set = set(new_sheet_list)


diff = old_set.symmetric_difference(new_set)

unique_values = list(diff)


print('Sheets that are in the not in the old list, but in the new.:')

print(unique_values)

if name == 'main':
main()

jmyzk_cloudsmart_jp

Hi @Bobert

Do you have more than 10000 sheets?

If so, even with page_size=10000, the API will only return that many sheets in a single call.
So, if the account has more than 10,000 sheets, the extras will not appear unless you page through:

My account currently has 5592 sheets. And as I tested, the Single Page method missed two sheets.

# --- Method 1: include_all=True ---

print("=== Method 1: include_all=True ===")

resp_all = smartsheet_client.Sheets.list_sheets(include_all=True)

list_all = [(sheet.name, sheet.id) for sheet in resp_all.data]

print(f"Sheets retrieved: {len(list_all)}")




# --- Method 2: Single page, page_size=5590 ---

print("\n=== Method 2: Single Page (page_size=5590) ===")

resp_single = smartsheet_client.Sheets.list_sheets(page=1, page_size=5590)

list_single = [(sheet.name, sheet.id) for sheet in resp_single.data]

print(f"Sheets retrieved: {len(list_single)}")



# --- Method 3: Manual Paging with total_pages logic ---

print("\n=== Method 3: Manual Paging (page_size=5590) ===")

first_page = smartsheet_client.Sheets.list_sheets(page=1, page_size=5590)

total_pages = first_page.total_pages

all_sheets = list(first_page.data)


for page in range(2, total_pages + 1):

    resp_page = smartsheet_client.Sheets.list_sheets(page=page, page_size=5590)

    all_sheets.extend(resp_page.data)


list_paged = [(sheet.name, sheet.id) for sheet in all_sheets]

print(f"Sheets retrieved: {len(list_paged)}")




# --- Compare results ---

def compare_sets(set_a, set_b, label_a, label_b):

    missing_a = set_a - set_b

    missing_b = set_b - set_a


    if missing_a:

        print(f"\nSheets in {label_a} but not in {label_b}: {len(missing_a)}")

        for name, sid in sorted(missing_a):

            print(f"  {name} ({sid})")

    if missing_b:

        print(f"\nSheets in {label_b} but not in {label_a}: {len(missing_b)}")

        for name, sid in sorted(missing_b):

            print(f"  {name} ({sid})")

    if not missing_a and not missing_b:

        print(f"\nNo difference between {label_a} and {label_b}.")


compare_sets(set(list_all), set(list_single), "include_all", "single page")

compare_sets(set(list_all), set(list_paged), "include_all", "paged loop")

compare_sets(set(list_single), set(list_paged), "single page", "paged loop")

=== Method 1: include_all=True ===
Sheets retrieved: 5592

=== Method 2: Single Page (page_size=5590) ===
Sheets retrieved: 5590

=== Method 3: Manual Paging (page_size=5590) ===
Sheets retrieved: 5592

Sheets in include_all but not in single page: 2
Three types of contact information (especially nicknames) (5918989318311812)
BGL Subscription fee (1346721816727428)

No difference between include_all and paged loop.

Sheets in paged loop but not in single page: 2
Three types of contact information (especially nicknames) (5918989318311812)
BGL subscription fee (1346721816727428)