Occasionally you may be asked to report on all the external visitors or editors to files shared out from your domain. This worked example is broken into two parts. The first part will show you how to find the files that were edited by external users or read by external users.
Note: This is different to finding the files shared as read ‘read’ or ‘edit’ to external users. Just because your users allow someone to edit a file by sharing it with them, does not mean the external party edited that file.
The second part shows you how to find the files shared out, then extract the reader and editor history for each file, then eliminate all the readers and editors from your domain, and produce a report that contains the external files that have specific external readers or editors and the times of their actions.
These examples contain useful Reg. Ex. selection and Google ‘Filtering’ examples.
Note: If a file is made ‘Public’ or ‘Public with Link’ then Google do not return names or times for visits from the public.
Find all files that were edited by external users or read by external users
To find all the files edited by people not on your domain you go to the GAT Drive audit and from the ‘General Search’ selection selected ‘Edited by’.
To find all editors NOT from your domain we are going to need to use a regular expression. The expression used is
Replace just the ‘your-domain’ (and ‘com’ words if required) with your own domain name. DO NOT change any other character. Be sure to tick ‘Reg. Exp.’ (If you need another ‘.’ in your domain name, remember to escape it with a ‘\’, so you get \. )
Then click on ‘Search Documents’ to find all the files that were edited by non-local users. Finally select ‘Out’ from the returned results because you are only interested in files shared out, that external people have edited.
Repeat for ‘Visited by’.
Find all files shared out, then extract the reader and editor history for each file
In the Drive Audit, select all files shared ‘Out’
Next select ‘Export Visits to Spreadsheet”
If you have thousands of files this may take a long time.
When finished you will see a screen like this.
Select ‘Open Actions …’ (link in blue).
You will now see the spreadsheet in Google Docs.
Select Column ‘E’ and insert a new column to the right.
In the cell F2 paste this formula (replacing YourDomain with your domain name)…
Copy that cell (select the cell, then CTRL C, make sure it has blue broken line box around the cell), select the entire column (Click on ‘F’), paste the cell (CTRL V).
Column F should now be filled with TRUE or FALSE
TRUE is the editor or visitor was from YourDomain.
FALSE if the user is from any other domain.
Next, select the filter icon.
Move to Column F and from the first row select the filter choices
Turn off ‘True’, click ‘OK’
You should now be left with every editor and every visitor and the date and time of their action to every external shared file from your domain, excluding users from YourDomain.