Tech Tip – List of people your domain shares documents with – – depreciated
Part of the power of GAT 4.0 (and greater) is the rich CSV reports it can produce. These can leave you with a large number of options for rich post-processing of reports. In this Tech Tip example, we are going to show you how to find your organisation’s Top External Viewers and Editors of domain documents.
First run the GAT and under Docs select ‘Show Advanced Search’
Simply select ‘Search Documents’
Next select the ‘Out’
And finally select ‘Export to CSV’*
*Please Note: The maximum size of any Google spreadsheet is 400,000 cells. Each row of our CSV has 29 cells, therefore if you have more than 13,793 records we can not generate a CSV.
We suggest adjusting the date window to an appropriate size.
This last step will create a file in your Google Docs account called
Docs shared out not deleted
Open this spreadsheet and in the bottom left of the sheet, select ‘+’ to add a new sheet
Select the new sheet, ‘Sheet2’. On the new sheet in cell A2 add the instruction
=query(‘Sheet 1’!F:I; “select H,I,F where F >= 0 order by F desc”)
This will pull out all the external people who can view or edit your documents.
Find the last row of data and note the number – in this example the last row is 441
To identify all the external Editors in cell E2 add the instruction
In the example below you must change the cell number A441 to the cell number of your last row
In Cell F2 add the Instruction
Again, adjust the number 441 to the number of your last row of data.
You must copy this formula down the column until you reach the row with the last external Editor
Finally in Cell H2 add the instruction
=query(‘Sheet3’!E:F; “select E,F where F > 0 order by F desc”)
This will sort in order of scale all those external people who can edit documents shared out by your domain.
You will now have identified the organisation’s ‘top external editors’ for the timeframe you selected and for documents owned by your domain.
Once you have the data isolated you can then Graph the data
You can repeat the step, changing the appropriate cell letters and values to see a list of the top external viewers.
If you have done something cool or useful using our General Audit Tool or its CSV output we would love to know – share with us and share with the world.
Note of thanks: Some of the clever formulae used in this tech tip were provided by Yogi Anand