Sheet access report change - please contact you rep to fix-missing owner column

Susan D

Hello

We use the shared access reports for security, audits, transferring files and hipaa.

The column for owner is no longer there. Smartsheets says it was changed and is by design.

Please contact you rep and complain

Genevieve P.

Hi @Susan D

Yes, the Owner column information has been removed since it was impacting the Sheet Access Report from generating due to the time it was taking to process this data. However this information is still provided in the Report:

Use the Shared To Permission column to identify the Owner

Cheers,

Genevieve

Susan D

we have over 50,000 sheets and we started doing audits to make sure staff do not share sheets outside the organization. If we found a sheets shared outside the company we would contact the owner. Any idea how to accomplish this with out the owner field?

Genevieve P.

Hey @Susan D

Once you've identified the sheet that was shared incorrectly, you can set a filter by the "Owner" value in the "Shared to Permission" column. Then you can find the same sheet name (or use the "Key" if your sheet names are not unique) to see the email in the "Shared To" column.

If you expect to need to reference the owners frequently, I would suggest creating a new workbook tab with the filtered data. That way you can search for the Sheet Name / Key in the second tab knowing that the rows are already filtered to show the owner. This will also enable you to sort by Admins as well, since they can help remove sharing permissions if the owner is no longer available.

Cheers,

Genevieve

Susan D

I have about 63060 rows.

I did this in access and filtered out emails. How can a do this in a database?

doing this manually by the eye is not an option.

Genevieve P.

Hi @Susan D

That sounds like what I would do:

1. Create a filter from the downloaded CSV file showing all Admins and Owners - add that data to a second tab so you can reference it
2. Change the filter to see the emails that aren't approved (I assume by domain?)
3. Cross-compare these tabs to find the information you need: the Owner and Admins of the related sheets from the first tab.

In my computer, I would use Command + C to copy the Key cell, then Command + F to find that same Key in the next tab. You could also Sort by the Sheet Name to keep the Admins and Owner together so you can copy all of the emails to contact.

As a side note - if you're on an Enterprise plan, it may be easier to set up an Approved Domain Sharing List across the organization instead of manually checking the Sheet Access Report. Here's more information:

https://help.smartsheet.com/articles/855284-security-controls-enterprise-only

Cheers,

Genevieve

Brooke Y

We have over 200,000 sheets, reports, dashboards, etc. in our environment. Manually looking for and editing anything this large in excel takes time. To quickly populate the Owner I made an IF statement in the blank Owner column.

=IF(H1="Owner",F1,"") - this is if the Shared to Permission Column(H1) is Owner, then return the Shared To(G1) email, if it's not owner return blank. Hit Enter, then, click the bottom right corner of the cell to quickly fill out the sheet.

(Remove the formula and keep the values by doing a copy and paste values on the Owner cell if you like.)

I then filtered the Shared To to Owner and copied the remaining cells to a new blank sheet (you could also do on a new tab.) On the original sheet I inserted a column for Sheet Owner and ran an XLookup based of the Key to pull in the owner for all sheets. Hit Enter, then, click the bottom right corner of the cell to quickly fill out the sheet. Again, you can remove the formula and keep the values by doing a copy and paste values on the new Owner cell if you like.

=XLOOKUP(A1,[Book3]Sheet1!$A:$A,[Book3]Sheet1!$E:$E,"UNKNOWN",0) - This is looking up the value in cell A1 (Key), matching it against the Key column (select entire column) in the new sheet, returning the Sheet Owner value (select entire column) of the new sheet, returning "uknown" if unknown, and only pulling in exact match. There should not be any "unknown", but I keep this to easily filter out errors.

While this is a workaround, having the Owner back in the report would be extremely helpful.

SDG&E CI Team: Kelly Dyer

We use the Sheet Access report all the time for transferring licenses, recognizing who owns what when they go to remove a license and for information about who shares things with whom. I don't mind waiting to get a complete file with the owner information included. The work around is just not feasible for ease in accessing this information. PLEASE put the information back into the Sheet Access Report!

Kariv

Joining in asking Smartsheet to please add the Owner information back into the Sheet Access report. We need this key information without having to do a cumbersome workaround.