User Defined Variables Within Collaborative Spreadsheet

Introduction #

“Apply custom filters” to query the metadata within GAT+ is a common task when auditing your Google Workspace domain. Many times you will have to query an auditing section by using search operators and search operands to retrieve the metadata.

To make query your metadata more flexible and collaborative we have a feature that allows you to pull in custom variables from a Google Spreadsheet when defining your filter query.

What are the benefits?

• You can share a spreadsheet with contributors who can fill it in
• The Spreadsheet can contain a list of variables such as (Emails, Phone Numbers, Serial Numbers, and anything else)

The custom variables can be pulled in any auditing section of GAT+.

Getting Started with Custom Variables #

While signed in to your Google Workspace admin account, the first step is to create a Google Spreadsheet in your Google Drive.

1. Give your newly created Google Spreadsheet a name.
2. Take a note of the File ID of your Spreadsheet.
3. Share your spreadsheet to our email address (gat-app-sa@generalaudittool.com as a Viewer or Editor).

   

4. Share this spreadsheet with the rest of your collaborative team so they can begin to contribute.

Now within the GAT+ tool, navigate to Configuration > General > Settings tab.

Enter the spreadsheet ID in the field “Sheet ID with Variables” and click Save.

Within the Google spreadsheet, you can now begin building your custom variables.

Note: Every time you make a change please wait 2 – 5 minutes before your changes are loaded into GAT+.

Custom Variable Update within Admin Log #

Whenever you update the Google Spreadsheet containing your list of Custom Variables wait a few minutes so that GAT+ can recognize the changes.

You can see the log within the GAT+ > Configuration > Admin Log menu.

Invoking Custom Variables within a Custom Filter #

Using a Custom Variable in Filtering Metadata #

In any auditing section of GAT+, apply a custom filter. And invoke your custom variables from the Spreadsheet.

Using the format %variable_name% make sure to use percentage symbols on either side.

Using a Custom Variable in Removing Permission #

You can invoke Custom Variables when using the Remove Permission menu.

This is especially helpful if you have to manually enter many values such as full email addresses, partial domain names, or Public or Public with link sharing permissions to remove them.

Pulling information from a Google Spreadsheet is much easier to maintain and use.

How to Use #

Within the Drive audit menu, in the Files tab, select files/folders with the checkbox then select the Files Operation then Remove Permissions.

The Remove Permission menu fields can handle variables that are comma-separated.

Example: *@gmail.com, public with link, public,*@yahoo.com,*@ExternalDomain.com

Format Within Spreadsheet #

In the Google Spreadsheet, I had a variable list like:

EXT_EMAILS

user1@abc.com,user2@xyz.com, etc. Use a comma to separate each value. So to invoke this during a filter query, I must use the Search operator in (comma separated values).

If you wish to use a Regex or a partial list of values, you must use the Search operator matches (partial string or reg.ex.).

Within the spreadsheets, these list values would be something like domain1.com|domain2.xyz|bob@companyz.com, etc. with each value separated with a vertical bar ( | ) in logical OR.

Valid Search Operators within GAT+ for Custom Variables #

Since all of the values in the Google Spreadsheet are strings (any sequence of characters). There are only a few search operators within the custom filter menu that would be compatible.

• in (comma separated values)
• not in (comma separated values)
• matches (partial string or reg.ex.)
• doesn’t match (partial string or reg.ex.)

Note:

In the current version, you can only use string values within your variable lists.