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 duplicate the value of the changed column to another column

You can simply use Update Row action to update the column B with the value from column A automatically:

  1. Select Column Is Updated trigger on the sheet.
  2. Select Update Row action
  3. Enter B in the column field and {column_A} in the value field

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

How to update any ranges of a different spreadsheet

You can specify the spreadsheet id in the target sheet field of Update Sheet action.

  1. Find the spreadsheet id from the URL.
  2. Enter the spreadsheet id together and “#” prior to the sheet name. For example, YOUR_SPREADSHEET_ID#sheet name.
Last modified August 31, 2022