User Defined Variables Within Collaborative Spreadsheet

User-Defined Variables Within Collaborative Spreadsheet #

“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 your metadata query 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+.

Spreadsheet settings #

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 aname.

   

2. Take a note of the File ID of your Spreadsheet.

   

3. Share your spreadsheet with 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.

GAT+ Settings #

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

In the Sheet ID with variables field, enter the URL of the Google spreadsheet we created above

Click on the Save button

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 Operatio,n 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 regex).

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.

Variable 1	value1,value2,value3
Variable 2	value1|value2|value3

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.

FAQ #

1. How long does it take for changes in my Google Spreadsheet to appear in GAT+?

A. After you update a variable or add a new value to your spreadsheet, there is a synchronization delay of 2 to 5 minutes. You can verify that the update was successful by checking the Admin Log under the Configuration menu in GAT+.

2. What is the correct syntax for using a variable in a filter?

A. To invoke a variable, you must wrap the variable name (as defined in your spreadsheet) with percentage symbols.

• Format: %your_variable_name%
• Example: If your variable is named EXT_EMAILS, you would enter %EXT_EMAILS% into the filter field.