How to determine the workspace ID & Name and folder name if I only have the sheetID?

NJDevil

Hi,

I am using the Smartsheet API for Python. I can determine the workspace names and IDs. I can determine the Sheet name and ID. However, I need to know which workspace (ID & Name) and under which folder of that workspace a specific sheet (per sheetID) resides.

How do I do that using python, please?

Thank you.

Lee Joramo

There is no way to get the parent folder name directly from a sheet. You have to get the workspace, and then request the object for the workspace's folders and sheets in the workspace. Then you can recurse this object to build the path to the sheet. This will get you part way there:

import smartsheet

smartsheet_access_token = "YOUR_ACCESS_TOKEN"
sheet_id = "YOUR_SHEET_ID"

smart = smartsheet.Smartsheet(smartsheet_access_token)

def find_item_path(folderDict, item_id, path=None):
    item_id = int(item_id)
    if path is None:
        path = []
    found = False
    # Check sheets in the workspace
    if "sheets" in folderDict:
        for sheet in folderDict['sheets']:
            if sheet['id'] == item_id:
                path = path + [[folderDict['id'], folderDict['name']], [sheet['id'], sheet['name']]]
                found = True
    if not found and "folders" in folderDict:
        for folder in folderDict['folders']:
            pathNextLevel = find_item_path(folder, item_id, path)
            if path != pathNextLevel:
                return pathNextLevel
    return path

sheet = smart.Sheets.get_sheet(sheet_id)
sheet_dict = sheet.to_dict()
workspace_id = sheet_dict["workspace"]["id"]
workspaces = smart.Workspaces.get_workspace(
  workspace_id=workspace_id,
  load_all=True
)
workspaces_dict = workspaces.to_dict()

item_path = find_item_path(workspaces_dict, sheet_id)
item_path = [[sheet_dict["workspace"]["id"], sheet_dict["workspace"]["name"]]] + item_path

print(item_path)

This will return a list of ["ID", "Name"] pairs:

[[3400509850982345, 'My Workspace'], [9086131540979674, 'Sub-Folder A1'], [3500989088668639, 'Data Sheet B2']`

I haven't done this before in Python, so this is not fully tested. It will take more coding to make it work wtih more than one level of folders.

NJDevil

Too bad this is not easier. Some findings with your code:

1) If there is no subfolder then it lists the workspace twice.

2) If the target sheet is nested in a few subfolders, only the last folder is listed, but not the entire path.

I am somewhat new to python and I'm sure it will take mew hours to get this working.

Any chance you can resolve the two items above?

Once working, my next steps would be:

1) Create a list of all sheets in the workspace.

2) Loop through the sheet list to create the list of sheets with full path in order to best be able to identify each sheet.

3) Loop through the sheet list and create a list of shares for each sheet.

3) Loop through all workspaces and do #1 #2, and #3 for all workspaces in order to create a full list.

Goal: To be able to audit the access list for every workspace and worksheet. I have the workspace access list completed. Now trying to get the sheet list working (as outlined with the reasoning above).

THANK YOU for your help!

NJDevil

Lee Joramo

I don't have the time right now. Maybe late in the week

NJDevil

That would be super helpful. Thank you, Lee!

OptimusPrime

Hello can you supply a piece of code in C# to do this too?

NJDevil

Sorry, I do not code in C#.