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

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.

Answers

  • Lee Joramo
    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
    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
    Lee Joramo ✭✭✭✭✭✭

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

  • NJDevil
    NJDevil ✭✭

    That would be super helpful. Thank you, Lee!