Sheet Access Report Size Limitations

Scott Peters

Hello Community Friends - Does anyone have a creative solution for managing the Sheet Access Report? Ours has exceeded the max row limitations of both Excel and Google Sheets. We need the ability to look up who is Sheet Owner and/or Admin of various sheets that we might not have access to, but we can no longer load all of the data. The Sheet Access Report itself cannot be run with any filters.

We tried loading it into a sheet using DataShuttle with filters to exclude the other Shared To Permission levels, but the file itself is missing any rows beyond an Excel's CSV limitation (1,048,576). We also tried loading it into DataTable, but there isn't a unique key in the file. There's a Key field, but that's the asset ID. A true unique key in our use case would be the asset Key+Shared To (email address).

My next line of thinking is to explore ideas with Bridge and the API, but that's more than our usual level of savvy. Any ideas or Bridge pointers? Thanks.

Brian_Richardson

For Bridge you'd need the Event paid endpoint to get the various access events etc. You could, of course, then use Bridge or another API based tool to read the events in batches and post them/filter them as needed.

For a count of sheets by person, you could use Bridge to do a workflow like this:

1. Call API to do a List Users call, pageSize=10,000, which will give you 10k users and some details for each. The details include sheetCount, the number of sheets that the person owns (? - not sure if also admins?). There's also an option to filter this result for sheets only modified since a certain date.
2. Pass the results to Javascript to part together a series of 500 row objects
3. Add Row to put that data in a sheet.

For a more detailed view of the sheets and the access level of the user, and potentially other data, you can also use Bridge but in a more robust way:

1. Have a sheet for each possible owner and define those sheet IDs and owner names in a reference sheet
2. Bridge would run and read the reference sheet, then pass each row to a child workflow (FOR-EACH loop essentially)
3. Child workflow would do a List Sheets call with an Assume-User header that has the user's email. That will return a list of sheets where they have access and includes the accessLevel object which tells you their access level. Note that you get an "abbreviated" sheet object with just some basics (name, link, access, created/modified dates, id). You could do a further call on each object to get the full sheet object if you wanted to get anything else, like latest conversation, data, all the sharing in place, etc.
4. Take the results and pass to a Javascript module to piece together a set of rows with the data from all the objects
5. Add or Update Rows into that user's "access sheet" to give you the list of sheets and what access level they have for each.

Here's an example of some Jscript code to put together chunks of 500 row objects that can then be updated into Smartsheet:

//Build Map of Target Sheet Columns. This JS module parameter is an array of columns from the Get Sheet module, fetching the target sheet.
const targetColumnMap = new Map();

targetColumns.map(tcolumn => targetColumnMap.set(tcolumn.title, tcolumn.id))
const sourceColumnMap = new Map()

sourceColumns.map(scolumn => sourceColumnMap.set(scolumn.title, scolumn.virtualId))
const rows = []; //Empty array to store cells objects later

for (row of sourceData) {

//Duplicate cells objects as many times as you need to based on # of target columns you want to update

  
const resultObject = {

cells: [{

    "columnId": targetColumnMap.get('Scenario'),

    "value": row.cells[sourceColumnMap.get('Scenario')].value || null

  },

  {

    "columnId": targetColumnMap.get('Key'),

    "value": row.cells[sourceColumnMap.get('Key')].value || null

  },

  {

    "columnId": targetColumnMap.get('Parent'),

    "value": row.cells[sourceColumnMap.get('Parent')].value || null

  },

  {

    "columnId": targetColumnMap.get('Description'),

    "value": row.cells[sourceColumnMap.get('Description')].value || null

  }

],

"toBottom": true

}

rows.push(resultObject)

}
// The chunkArray function splits any array into small arrays of 500 chunks.

// This is to allow all Smartsheet API calls to succeed as there is a limit of 500 rows per request.

const chunkArray = (array) => {

const arrays = [];
let i,

j,

temparray,

chunk = 500; // Sets the value to 500 items per array.
for (i = 0, j = array.length; i < j; i += chunk) {

temparray = array.slice(i, i + chunk);

arrays.push(temparray);

}
return arrays;

}
// CHANGE CONSOLE.LOG TO RETURN chunkArray(rows)

return chunkArray(rows)

Scott Peters

@Brian_Richardson - This is a great start, really appreciate it. We have over 30k sheets and growing, but I envision how to use Bridge to filter/ignore assets where Owner= one of our Control Center Leads. But for the rest… I had not thought to use List Users as the first step. Once I have that, how would we know we are gathering the 'full' inventory sheets owned by each user? Again, really appreciate the ideas.

Brian_Richardson

You'd have to double check, but my understanding is that the List Users gives you a count of owned sheets as {{data.sheetCount}}.

It's not clear from the documentation whether that count is only owned items (my assumption) or owned and administered items. I think you'd need to do a couple of calls and just check to see what you're getting.

https://smartsheet.redoc.ly/tag/users#operation/list-users