Sheet access and published items reports

Sheet access reports are useful when you need to perform routine audits on who can access your assets and on what level. The reports provide date and time for when each user accessed an asset last. Published items reports help you see which assets were published and by whom.

To support large-scale deployments of these reports, Smartsheet is planning to introduce resource and subject API links rather than linking to the asset or expanding all the details of the user/group. Direct links and expanded details can create performance issues; using API links allows for the efficient creation and consumption of the report. When these changes become generally available, you may notice certain fields within your reports will be updated. See FAQ below for details on the specific fields that will be changed.

 

Once you generate your reports, you can use custom ruby scripts to convert the reports into a readable asset.

 

Generate a sheet access report

  1. Log in to Admin Center.
  2. Select the Menu icon to expand the left sidebar, and then select User Management. 
  3. Select More Actions > Download Sheet Access Report.
  4. Check your email. The report goes to the email associated with your Admin Center login. 

Download a sample sheet access report

What's in the report? 

The sheet access report contains the following columns: 

  • Resource API URL (API URL to asset)
  • Subject API URL (API URL to user/group)
  • Type (Asset Type such as Sheet)
  • Resource ID (Asset ID)
  • Subject type (user/group)
  • Subject ID (User or Group ID)
  • Shared to Email address (for subject type: user)
  • Permission Set Name (for example, read_only)
  • Last Modified Date/Time (UTC)
Sheet access report elements

 

The Shared to email address will still be available in the original, expanded format.

 

Generate a Published Items report 

  1. Log in to Admin Center.
  2. Select the Menu icon to expand the left sidebar, and then select User Management. 
  3. Select More Actions > Download Published Item Report.
  4. Check your email. The report goes to the email associated with your Admin Center login.

Download a sample Published Items report

What’s in the report?

The published items report contains the following columns:

  1. Asset ID
  2. API URL for the item [for Asset Name]
  3. API URL for the Publishing User
  4. Published format
  5. Published Item Type
  6. Publishing user Email Id
  7. Access Control
  8. Modified Timestamp
Published items report elements

 

The publishing user email address will still be available in the original, expanded format.

 

Create a script to convert the API references

After you create the sheet access or published items reports, you can create a ruby script to fill in usernames and resources. The script requires two inputs: 

  • Path to the downloaded sheet access report  
  • Path to the new report (`ruby sar.rb ~/Downloads/asset_report.csv new_report.csv`)  

You must have ruby installed to run this script (https://www.ruby-lang.org/en/)

FAQs

What's changed between the previous and current versions of the Sheet Access report?

  • The main difference is the introduction of an API-based access, providing raw, non-expanded data and the use of Resource URLs for detailed information retrieval.
  • We have removed the Asset Name, Workspace Name, and Owner Name fields from the sheet access report to improve the speed and performance of report generation 
  • We have enabled administrators to access this information via API URLs, and we have added the following fields:
    1. Direct URL to shared asset (Resource API URL)
    2. Direct URL leading to the User or Group the asset is shared with (Subject API URL)
    3. Subject type (user/group)
    4. Subject ID (User or Group ID)

What's changed in the Published Item report?

Asset Name and Owner Name columns are removed. New columns providing the API URL for item details and Owner Email ID are added.

Are the new report formats applicable to both org level and specific user-level report generation?

Yes, the updates will apply to both types of report generation.

How can I consume the data in these new reports now that they will be in the form of API URLs?

We’ve provided a sample ruby script for reference for you to create custom scripts to programmatically consume the desired data from these reports. However, if you lack the technical expertise and access to tools required to create your custom scripts, enroll the appropriate resources in your company (like IT) to do this for you.

Will there be an option to export the report in JSON format?

No.

What is the syntax for the Resource API URL and Subject API URL?

The syntax for the Resource API URLs is as follows:

  • For a sheet: https://api.smartsheet.com/2.0/sheets/{{Resource ID}}
  • For a report: https://api.smartsheet.com/2.0/reports/{{Resource ID}}
  • For a dashboard: https://api.smartsheet.com/2.0/dashboards/{{Resource ID}}

The syntax for the Subject API URLs is: https://api.smartsheet.com/2.0/users/{{Subject ID}} or https://api.smartsheet.com/2.0/groups/{{Subject ID}} based on if the Subject Type is user or group.

How can I locate the Resource IDs and Subject IDs?

To find the {{Resource ID}} for sheets, reports, or dashboards before the Sheet Access report is updated, you can navigate within Smartsheet to the specific asset, select File > Properties, and locate the asset ID listed there. The new Sheet Access report also contains a Resource ID column.

As for User IDs, these aren't readily displayed in the Smartsheet UI since email addresses serve as the primary identifier. To obtain User IDs, you need to utilize the Get User API. Here’s a quick guide on how you might retrieve User IDs:

  1. Make an API call to the Users endpoint: https://api.smartsheet.com/2.0/users
  2. Parse the response to extract User IDs associated with the current user’s email address.

For more information, review the Smartsheet Developer Portal.

Was this article helpful?
YesNo