Watch and manage sheets with agents

In the Sheets steps, you can filter rows by column values. The names of the columns come from the first row. The first row must meet the following requirements:

• The column names must start in cell A1
• Each name must be one cell, not merged cells
• Each column name must be unique and can’t be empty

The When a sheet changes starter watches for changes based on 2 sets of conditions:

1. Find rows to watch for changes—The agent starts only for changes to rows that match the conditions you set. Matching is done on the latest value in the column.
2. Select columns to watch—The agent starts only when a change happens in the selected columns. If a change happens in another column, it doesn’t start the agent, even if the change happens in a row that matches your first set of conditions. You can watch multiple columns.

You can set several column conditions for each set, just click Add column and choose if the row has to match all the conditions in the set (AND) or only one of them (OR).

The agent starts only if the change matches both sets of conditions. If many rows change at once, like when someone pastes new values into several rows, those changes are grouped into one run.

To use this starter, decide what changes you want to watch for. For example, your team might have a project tracker that has 4 columns: "Project name", "Assignee", "Priority", and "Status". Here are a few examples:

• Start when one of your projects changes to "High" priority
  • For the rows to watch, set the column to "Assignee" and the value to your name.
  • For the columns to watch, set the column to "Priority" and the value to "High." If you want to run the agent for any change in priority, set the value to "Any".
  • Note: Because the starter is based on the latest values in the column, it won’t start if the assignee is changed to someone besides you at the same time as the priority changes.
• Start when you’re assigned a project
  • For the rows to watch, set the column to "Assignee" and the value to "Any" or your name. Because the starter is based on the latest value in the column, it will start for a row that just changed to your name.
  • For the columns to watch, set the column to "Assignee" and the value to your name.

You can reference details about the change to the sheet in later steps by adding variables. These variables include:

• Details about the spreadsheet and sheet
• Who made the change
• How many rows that matched the conditions changed
• Data from the rows that changed and matched the conditions

Data from the rows that matched your starting conditions is available by column. Continuing the example from before, you could insert the variable Latest values "Project name" to get the name of the project in the row that started the agent. If the sheet had a bulk change to several rows, then you get a list of values, but only from the rows that match the starting conditions.

To show how this works, imagine you use this project tracking sheet:

You set up an agent to email Kim when their projects change to "High" priority, with the message: "[Latest values "Project name"] is now a high priority!"

• If Project A changes from "Low" to "High", Kim gets the message: "Project A is now a high priority!"
• If all projects change to "High", Kim gets the message: "Project A, Project D is now a high priority!" Only Projects A and D are listed because those rows have "Kim" in the "Assignee" column.

This step gets data by column from one or more rows. It’s important to set the right matching conditions so you get data only from the relevant row or rows.

You can use data from rows that matched your conditions in later steps by adding variables. These variables include:

• Details about the spreadsheet and sheet
• How many rows matched the conditions
• Data from the rows that matched the conditions

Get sheet contents has one variable per column of data, Matching values "column name". The variable contains the latest values for that column, only from the rows that matched your conditions, not all the rows. If more than one row matches, the Matching values "column name" gives you a list of values in that column from those rows.

To show how this works, imagine you use this project tracking sheet:

Here are a few examples of agents that get data from the tracker:

• Get a list of high priority projects that aren’t started yet: You set 2 column conditions for this one:
  1. Set the column to "Priority". To set the value, click the value box and select "High."
  2. Click Add column.
  3. Set the column to "Status" and select "Not started" for the value.
  4. Leave AND selected for how the conditions are combined so that the rows have to match both. If you changed it to OR, then your results would include rows that matched either condition.
  5. When you add the Matching values "Project name" variable in a later step, it gives you "Project E", the value from the "Project name" column that matches those conditions.
• Get the status of a project, using a project name variable that was extracted from an email
  1. Set the column to "Project name" and the value to the project name variable.
  2. If the Extract step says to look for the "Project A" row, then Matching values "Status" gives you "In progress".

These steps can change or empty one or more rows. It’s important to set the right filters so you change only the rows you want.

To show how this works, imagine you use this project tracking sheet:

Here are a few examples of agents that update the tracker. Finding rows to clear works the same way, except that all values are cleared from the matching row instead of being updated:

• Update the status of a project based on data extracted from a post to a Chat space
  • For Find rows to update, set the column to "Project name" and the value to the variable that represents the extracted project name.
  • For Set new values by column, set the column to "Status" and the value to the variable that represents the new status.
  • As long as each project has a unique name, the status is updated only in the row with the matching project name.
• If the status is "Done", assign to Dana to close out the project
  • For Find rows to update, set the column to "Status" and the value to "Done."
  • For Set new values by column, set the column to "Assignee" and the value to "Dana." If multiple rows are set to "Done," then all of them have "Assignee" changed to "Dana".

You can use data from the rows that changed in later steps by adding variables. These variables include:

• Details about the spreadsheet and sheet
• How many rows matched the conditions
• Data from the rows that matched the conditions

Update rows has 2 variables per column of data, Latest values "column name" and Old values "column name". The variables contain the values for that column, only from the rows that were updated, not all the rows. For example:

• The update rows step changed the status of Project A from "In progress" to "Blocked"
  • Latest values "Status" = Blocked
  • Old values "Status" = In progress
• The update rows step changed the assignee for Project B and Project D from Yuri and Kim, respectively, to Dana
  • Latest values "Assignee" = Dana, Dana
  • Old values "Assignee" = Yuri, Kim

Clear rows has one variable per column, Cleared values "column name". These are like the Old values "column name" for the Update rows step and work the same way. They can be useful if you want to keep track of the data that was cleared by the agent, in case you need to recover it.