---
title: User Defined Variables Within Collaborative Spreadsheet
description: "### 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…"
url: "https://gatlabs.com/knowledge/tech-tips/user-defined-variables-within-collaborative-spreadsheet/"
updated: "2026-01-29"
category: "Drive Audit & Management"
tags:
  - GAT+
---

# 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.
   ![](https://gatlabs.com/knowledge/wp-content/uploads/2022/02/2022-02-11-11_21_58-Films-TV.png)
2. Take a note of the File ID of your Spreadsheet.
   ![](https://gatlabs.com/knowledge/wp-content/uploads/2022/02/2022-02-11-11_39_33-Collaborative-Spreadsheet-with-User-Defined-Variables-Google-Sheets.png)
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

![](https://gatlabs.com/knowledge/wp-content/uploads/2022/02/2022-02-11-11_56_33-Films-TV.png)

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

![](https://gatlabs.com/knowledge/wp-content/uploads/2022/02/2022-02-11-12_35_20-Mail.png)

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.

![](https://gatlabs.com/knowledge/wp-content/uploads/2022/02/2022-03-08-10_03_08-Calculator.png)

### 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.**

![](https://gatlabs.com/knowledge/wp-content/uploads/2022/02/2022-02-11-12_29_02-.png)

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

![](https://gatlabs.com/knowledge/wp-content/uploads/2022/02/2022-02-22-13_27_52-GAT-1.png)

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

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

![](https://gatlabs.com/knowledge/wp-content/uploads/2022/02/2022-03-08-10_08_53-Calculator.png)

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

![](https://gatlabs.com/knowledge/wp-content/uploads/2022/02/2022-02-11-12_40_51-Mail.png)

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