How and/or when does a CellLink Object 'status' get updated to 'BROKEN'?

Across our project portfolio, each project has a main project sheet. This sheet has rows added & deleted as tasks are added or canceled from a project.

Individual departments have department specific tracking sheets which mirrors the task list in the in main project sheet and certain key values are linked to the main project sheet via cell links.

My question arises when a task/row is deleted from the main project sheet. I understand that the cell link will persist, but based on the API documentation, I expect the status to change to 'BROKEN'. However, when I check the status of cell links for which I know the source row to have been deleted, the status remains 'OK' even if the source row was deleted months ago.

CellLink Object

columnId

number

Column Id of the linked cell.

rowId

number

Row Id of the linked cell.

sheetId

number

Sheet Id of the sheet that the linked cell belongs to.

sheetName

string

Sheet name of the linked cell.

status

string

  • BLOCKED One of several other values indicating unusual error conditions.
  • BROKEN The row or sheet linked to was deleted.
  • CIRCULAR One of several other values indicating unusual error conditions.
  • DISABLED One of several other values indicating unusual error conditions.
  • INACCESSIBLE The sheet linked to cannot be viewed by this user.
  • INVALID One of several other values indicating unusual error conditions.
  • NOT_SHARED One of several other values indicating unusual error conditions.
  • OK The link is in a good state.

Is my understanding of the CellLink status attribute incorrect? How often is the CellLink status updated?

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @Clay.Hall

    As I tested, when I deleted rows 2 and 3 with cell-link-out to other sheets, the cell-link-in value on the other sheet (tracking sheet: department A) was updated to "None" immediately.

    When I removed the cell-link-out to other sheets, not the rows themselves, the cell-link-in values were updated to "None" as well.

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @Clay.Hall

    As I tested, when I deleted rows 2 and 3 with cell-link-out to other sheets, the cell-link-in value on the other sheet (tracking sheet: department A) was updated to "None" immediately.

    When I removed the cell-link-out to other sheets, not the rows themselves, the cell-link-in values were updated to "None" as well.

  • @jmyzk_cloudsmart_jp Thank you for confirming the cell link behavior. I was able to recreate your results with dummy sheets. Previously I had followed this same procedure, but I had added dummy rows to the production sheets.

    This led me to check if our production, destination sheet is within limits:

    Total Rows: 4880
    Total Columns: 80
    Total Cells: 390400
    Incoming Cell Links (Active): 23829

    While my destination sheet appears to fall within the sheet limits, I am now questioning the 500 link limit for a source sheet:

    "You can create links of up to 500 cells from the same source sheet at a time and can have a total of 500,000 inbound links in a destination sheet."

    Can anyone provide clarity to this limit? Does it mean that for a given destination sheet, a single source sheet can only have 500 outbound cell links? Or does it mean a source sheet can only have a total of 500 outbound cell links at a given time.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    @Clay.Hall

    Yes, you can create 500 cell links at a time. To create more than 500 cell links, you need to repeat the 500 cell link creations.

    The demo sheet below has 2000 cell-link-outs created in the 500-cell batch.

    https://app.smartsheet.com/b/publish?EQBCT=9bc61be798ef42ad87c10d87b51c930a

    I got this error message when I tried to create 1000 cell links at a time.

    The destination sheet has 2000 cell-links-ins.

    https://app.smartsheet.com/b/publish?EQBCT=ff49fd58f49a42918ed1b949a45e8add

  • @jmyzk_cloudsmart_jp

    Thanks again for being so quick to respond. It's been really helpful to keep me from heading down the wrong path.

    After you clarified the 500 cell link creation limit, I started to explore the inbound and outbound cell links and reference limit:

    Sheet Component

    Maximum

    Rows

    20,000

    Columns

    400

    Cells

    500,000

    Characters in a cell

    4,000

    Inbound and outbound cell links or references

    500,000

    Attempting to count all of the cell links & references, it didn't seem like I would be at issue with the 500,000 reference maximum:

    I was able to reduce the number of "Used in formula on: " references to below 100,000. I re-tested the processes of deleting a task in the source sheet and the cell links in the destination sheet disappeared as expected.

    Is it possible to access the "Used in formula on : " elements or Sheet Reference Manager via the API so that these limits can be monitored?