0

I am able to get the Row and Column data from the Smartsheet API JSON response but in it there are no metadata such as _OUTLINELEVEL_ or _ROWNUM_.

When requesting the data from Smartsheet API, I also sent additional params such as:

params = {'include': 'objectValue,objectProperties,format,formula,columnType,options'}

and sent it with the request. But I still do not get any metadata. Specifically, I am trying to get the _OUTLINELEVEL_ column that is present in the Smartsheet.

If I view the Smartsheet online then I can see those columns.

Could someone please help me get this data? Thank you!

floss
  • 2,603
  • 2
  • 20
  • 37
  • Is the `_OUTLINELEVEL_` column a system-generated column in Smartsheet? Or is it a column that a user has manually created in the sheet (using a formula to populate it)? – Kim Brandl Apr 26 '23 at 15:41
  • And specifically what API operation are you using -- `Get Sheet` ? – Kim Brandl Apr 26 '23 at 15:49
  • @KimBrandl columns such as `_OUTLINELEVEL_` are system generated based on parent/child relationships – floss Apr 26 '23 at 19:42
  • Interesting. I've never seen the `_OUTLINELEVEL_` column in a sheet, and don't see it as an option for Column Type when I add a new column to a sheet via UI. You say you can see this column in your sheet -- do you know how this column came to exist there? I can't seem to find any Smartsheet documentation that describes such a column type in Smartsheet. If you look at the value of any (populated) cell in that column -- is it a plain text value or a formula? If you look at the cell history of any (populated) cell in that column -- do you see a history entry for when it was first populated? – Kim Brandl Apr 26 '23 at 19:54
  • @KimBrandl Would you know if we can get the parent child level relationship data in the API call and how are they represented? In Smartsheet, there is a parent row and also a child row. Is this something we can get identified from the API? – floss Apr 28 '23 at 15:14
  • I've updated my answer to add a section that explains how you can use data within a `Get Sheet` API response to determine row hierarchy (parent-child-sibling) relationships in a sheet. If this successfully addresses your question, please mark the answer as accepted, so that others will be more likely to benefit from this info in the future. Thanks! – Kim Brandl Apr 28 '23 at 21:43

1 Answers1

1

The API does support getting the row number for rows within a sheet. For example, the following snippet uses the Smartsheet Python SDK to get a sheet, then iterates through the rows in the response to print out the value of the row_number property for each row.

# Specify access token
os.environ['SMARTSHEET_ACCESS_TOKEN'] = 'ADD_YOUR_TOKEN_VALUE_HERE'

# Initialize client. f
# Uses the API token in the environment variable SMARTSHEET_ACCESS_TOKEN.
smartsheet_client = smartsheet.Smartsheet()

sheetId = 3932034054809476

# get the sheet
sheet = smartsheet_client.Sheets.get_sheet(sheetId) 

# iterate through the rows array and print row number of each row
for row in sheet.rows:
    print('rowNumber= ' + str(row.row_number))

As far as I know, it's not possible to get the outline level of a row via the Smartsheet API -- unless a user has manually added such a column to the sheet and populated it using a formula -- for example, as described in this Smartsheet Community post: https://community.smartsheet.com/discussion/77827/work-breakdown-structure-wbs-column-formula-configuration#latest.

If this is the case -- i.e., a column has been (manually) created in the sheet and populated with an outline level value -- then you'd obtain that cell value for a row in the same way as you'd obtain the cell value of any other cell within the sheet -- i.e., by using the Get Sheet operation or the Get Row operation and then evaluating the value of that cell within the row object(s) of the API response. For example, the following code snippet uses the Smartsheet Python SDK to get a sheet and then iterates through the rows of the sheet, and for each row prints the value of the cell I'm interested in (i.e., in this example, the value in the column that has column id = 5228827298293636).

# Specify access token
os.environ['SMARTSHEET_ACCESS_TOKEN'] = 'ADD_YOUR_TOKEN_VALUE_HERE'

# Initialize client. f
# Uses the API token in the environment variable SMARTSHEET_ACCESS_TOKEN.
smartsheet_client = smartsheet.Smartsheet()

sheetId = 3932034054809476

# get the sheet
sheet = smartsheet_client.Sheets.get_sheet(sheetId) 

# iterate through the rows 
for row in sheet.rows:

    # iterate through the cells for the row and print value of specific cell
    for cell in row.cells:
        if cell.column_id == 5228827298293636:
            print('value of cell= ' + cell.value)

UPDATE: how to determine row hierarchy via API

You can use information within the rows collection of a Get Sheet response to programmatically identify the row hierarchy (i.e., parent-child-sibling relationships) within a sheet.

For example, consider the following sheet:

smartsheet sheet

Please note -- I've manually added (and populated) the last three columns in this sheet to show the data (ID values) that'll be returned for row.id, row.parent_id, and row.sibling_id in the API response. Further, I've color coded the various ID values to make it easy to see which IDs show up where. As this example shows, the parent_id attribute and the sibling_id attribute for each row in the response indicate where the row appears in the sheet relative to other rows in the sheet. Here's the logic for how these attributes get populated:

parent_id:

  • If the parent_id attribute is present for a row, its value is the ID of the parent row.
  • If the parent_id attribute is not present for a row, this indicates it's a top-level (i.e., non-indented) row in the sheet.

sibling_id:

  • If the sibling_id attribute is present for a row, its value is the ID of the row that immediately precedes it at the same level of hierarchy (in that location) within the sheet.
  • If the sibling_id attribute is not present for a row, this indicates that the row is the first row present in that hierarchy (at that location) of the sheet -- i.e., it's either the first top-level (non-indented) row in the sheet or the first child row in that location within the sheet.

Here's the Get Sheet API response for the sheet that's shown in the screenshot above.

{
    "id": 3932034054809476,
    ...
    "columns": [
        ...
    ],
    "rows": [
        {
            "id": 6933706290423684,
            "rowNumber": 1,
            "expanded": true,
            "createdAt": "2022-08-23T20:13:19Z",
            "modifiedAt": "2023-04-28T21:25:45Z",
            "cells": [
                {
                    "columnId": 6101753539127172,
                    "value": 1.0,
                    "displayValue": "1"
                },
                {
                    "columnId": 5228827298293636,
                    "value": "white",
                    "displayValue": "white"
                },
                {
                    "columnId": 5678576703590276,
                    "value": 6.933706290423684E15,
                    "displayValue": "6933706290423684"
                },
                {
                    "columnId": 2055191968829316
                },
                {
                    "columnId": 6558791596199812
                }
            ]
        },
        {
            "id": 5556441535434628,
            "rowNumber": 2,
            "parentId": 6933706290423684,
            "expanded": true,
            "createdAt": "2023-04-26T19:49:40Z",
            "modifiedAt": "2023-04-28T21:25:45Z",
            "cells": [
                {
                    "columnId": 6101753539127172,
                    "value": "1a",
                    "displayValue": "1a"
                },
                {
                    "columnId": 5228827298293636,
                    "value": "child1a",
                    "displayValue": "child1a"
                },
                {
                    "columnId": 5678576703590276,
                    "value": 5.556441535434628E15,
                    "displayValue": "5556441535434628"
                },
                {
                    "columnId": 2055191968829316,
                    "value": 6.933706290423684E15,
                    "displayValue": "6933706290423684"
                },
                {
                    "columnId": 6558791596199812
                }
            ]
        },
        {
            "id": 3304641721749380,
            "rowNumber": 3,
            "parentId": 6933706290423684,
            "siblingId": 5556441535434628,
            "expanded": true,
            "createdAt": "2023-04-26T19:49:40Z",
            "modifiedAt": "2023-04-28T21:25:45Z",
            "cells": [
                {
                    "columnId": 6101753539127172,
                    "value": "1b",
                    "displayValue": "1b"
                },
                {
                    "columnId": 5228827298293636,
                    "value": "child1b",
                    "displayValue": "child1b"
                },
                {
                    "columnId": 5678576703590276,
                    "value": 3.30464172174938E15,
                    "displayValue": "3304641721749380"
                },
                {
                    "columnId": 2055191968829316,
                    "value": 6.933706290423684E15,
                    "displayValue": "6933706290423684"
                },
                {
                    "columnId": 6558791596199812,
                    "value": 5.556441535434628E15,
                    "displayValue": "5556441535434628"
                }
            ]
        },
        {
            "id": 4992620981079940,
            "rowNumber": 4,
            "parentId": 6933706290423684,
            "siblingId": 3304641721749380,
            "expanded": true,
            "createdAt": "2023-04-28T21:15:57Z",
            "modifiedAt": "2023-04-28T21:25:45Z",
            "cells": [
                {
                    "columnId": 6101753539127172,
                    "value": "1c",
                    "displayValue": "1c"
                },
                {
                    "columnId": 5228827298293636
                },
                {
                    "columnId": 5678576703590276,
                    "value": 4.99262098107994E15,
                    "displayValue": "4992620981079940"
                },
                {
                    "columnId": 2055191968829316,
                    "value": 6.933706290423684E15,
                    "displayValue": "6933706290423684"
                },
                {
                    "columnId": 6558791596199812,
                    "value": 3.30464172174938E15,
                    "displayValue": "3304641721749380"
                }
            ]
        },
        {
            "id": 3480469833469828,
            "rowNumber": 5,
            "parentId": 4992620981079940,
            "expanded": true,
            "createdAt": "2023-04-28T21:12:11Z",
            "modifiedAt": "2023-04-28T21:25:45Z",
            "cells": [
                {
                    "columnId": 6101753539127172,
                    "value": "1c-1",
                    "displayValue": "1c-1"
                },
                {
                    "columnId": 5228827298293636,
                    "value": "child 1c-1",
                    "displayValue": "child 1c-1"
                },
                {
                    "columnId": 5678576703590276,
                    "value": 3.480469833469828E15,
                    "displayValue": "3480469833469828"
                },
                {
                    "columnId": 2055191968829316,
                    "value": 4.99262098107994E15,
                    "displayValue": "4992620981079940"
                },
                {
                    "columnId": 6558791596199812
                }
            ]
        },
        {
            "id": 770536852088708,
            "rowNumber": 6,
            "siblingId": 6933706290423684,
            "expanded": true,
            "createdAt": "2022-07-11T13:32:02Z",
            "modifiedAt": "2023-04-28T21:25:45Z",
            "cells": [
                {
                    "columnId": 6101753539127172,
                    "value": 2.0,
                    "displayValue": "2"
                },
                {
                    "columnId": 5228827298293636,
                    "value": "green",
                    "displayValue": "green"
                },
                {
                    "columnId": 5678576703590276,
                    "value": 7.70536852088708E14,
                    "displayValue": "770536852088708"
                },
                {
                    "columnId": 2055191968829316
                },
                {
                    "columnId": 6558791596199812,
                    "value": 6.933706290423684E15,
                    "displayValue": "6933706290423684"
                }
            ]
        },
        {
            "id": 7046650170566532,
            "rowNumber": 7,
            "siblingId": 770536852088708,
            "expanded": true,
            "createdAt": "2022-10-02T13:49:03Z",
            "modifiedAt": "2023-04-28T21:25:45Z",
            "cells": [
                {
                    "columnId": 6101753539127172,
                    "value": 3.0,
                    "displayValue": "3"
                },
                {
                    "columnId": 5228827298293636,
                    "value": "white",
                    "displayValue": "white"
                },
                {
                    "columnId": 5678576703590276,
                    "value": 7.046650170566532E15,
                    "displayValue": "7046650170566532"
                },
                {
                    "columnId": 2055191968829316
                },
                {
                    "columnId": 6558791596199812,
                    "value": 7.70536852088708E14,
                    "displayValue": "770536852088708"
                }
            ]
        },
        {
            "id": 6903384426145668,
            "rowNumber": 8,
            "siblingId": 7046650170566532,
            "expanded": true,
            "createdAt": "2023-02-23T17:30:36Z",
            "modifiedAt": "2023-04-28T21:25:45Z",
            "cells": [
                {
                    "columnId": 6101753539127172,
                    "value": 4.0,
                    "displayValue": "4"
                },
                {
                    "columnId": 5228827298293636,
                    "value": "red",
                    "displayValue": "red"
                },
                {
                    "columnId": 5678576703590276,
                    "value": 6.903384426145668E15,
                    "displayValue": "6903384426145668"
                },
                {
                    "columnId": 2055191968829316
                },
                {
                    "columnId": 6558791596199812,
                    "value": 7.046650170566532E15,
                    "displayValue": "7046650170566532"
                }
            ]
        },
        {
            "id": 7984069460840324,
            "rowNumber": 9,
            "parentId": 6903384426145668,
            "expanded": true,
            "createdAt": "2023-04-28T21:12:11Z",
            "modifiedAt": "2023-04-28T21:25:45Z",
            "cells": [
                {
                    "columnId": 6101753539127172,
                    "value": "4a",
                    "displayValue": "4a"
                },
                {
                    "columnId": 5228827298293636,
                    "value": "child 4a",
                    "displayValue": "child 4a"
                },
                {
                    "columnId": 5678576703590276
                },
                {
                    "columnId": 2055191968829316,
                    "value": 6.903384426145668E15,
                    "displayValue": "6903384426145668"
                },
                {
                    "columnId": 6558791596199812
                }
            ]
        }
    ]
}
Kim Brandl
  • 13,125
  • 2
  • 16
  • 21