See List of People Your Domain Shares Documents With

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

=ArrayFormula(unique(trim(transpose(split(concatenate(A3:A441&”,”),”,”)))))

In Cell F2 add the Instruction

=ArrayFormula(sum(iferror(sign(find($E2,$A$3:$A$441)))))

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.

For any ideas, requests or help contact ideas@generalaudittool.com or support@generalaudittool.com

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

Comments

comments