Update sheet

How to add timestamp when a row is updated

To indicate the last change time when the row is udpated, you can insert a timestamp to the last column of the row:

1. Select an update trigger on the sheet.
2. Select Update Row action
3. Enter the column letter in column field and {now()} in value field

{now()} will produce the current timestamp in the format of YYYY-MM-DD hh:mm:ss.

You can use {today()} or any variable to populate any data in any column.

How to track who has updated the row

When the sheet is being shared and multiple users can edit it, it may be useful to track who has made the update.

Similar to the timestamp tracking, you can add the user info to a column by using varialbes.

• {editing_user}: the user name
• {editing_user_email}: the user email.

How to sort a sheet without changing the header row

Steps:

1. Choose any trigger on the sheet
2. Add a Sort Sheet action
3. Specify the header row number in Head Row field
4. Enter the column letter in Sorting Column field and choose the sorting order.

How to sort a range

Steps:

1. Choose any trigger on the sheet
2. Add a Sort Sheet action
3. Specify the range in Range field using A1 notation.
4. Enter the column letter in Sorting Column field and choose the sorting order.

How to update multiple columns

Let’s say you want to duplicate the values of A,B,C,D to D,E,F,G. Obviously, you can enter the columns one by one in Update Row action. But you don’t have to do this.

Instead, you could just specify multiple columns in column field, eg, D-G or D,E,F,G, and {column_A-D} in the value field. The values will be automatically allocated based on the order.

How to insert a google sheet formula

To insert google sheet formula to a cell, use Update Row action the same way you insert text.

For example you could use =today() to insert the formula of today.

To add the values of two columns: ={column_A} + {column_B}

You can also specify the row number: =sum(A{row_number}:A)

How to update a lookup row of another sheet

There are chances that you want to find a row on another sheet based on a unique ID. You can do this via Lookup Row action.

Assuming the unique ID is in column A of both sheets:

1. Add Lookup Row action with A in column field and {column_A} in value field
2. Add Update Row action, select Lookup row in Target Row field and specify the columns to be updated

Lookup Row action will find the first row that matches the condition, ie, with the same unique ID.

How to update any cell of a sheet

You can use Update Sheet action to update an arbitrary cell of a sheet:

1. Use any trigger that suits your needs.
2. Select Update Sheet action and enter sheet name
3. Enter A2 in the range name field and {column_A} in the value field

How to update any cell of a different spreadsheet

You can specify the spreadsheet URL or ID in the target sheet field of Update Sheet action.

1. Use any trigger that suits your needs.
2. Select Update Sheet action
3. Specify sheet name field with the spreadsheet URL or ID and “#” prior to the sheet name. For example, YOUR_SPREADSHEET_URL#sheet name.
4. Specify the ranges and values you want to update