Automatic Updated At Fields In Google Sheets

When working on Google Sheets, especially with others, it is often quite useful to have fields that capture when a cell, column, row, or even an entire sheet has been updated. In order to do that, you need to use Google Apps Scripts. Don’t worry if you haven’t really coded before; the sample script below is quite straightforward and can be easily altered to meet your needs.

Step 1: Open the Sheets script editor

In your Google Sheets, you first have to open the script editor. It is in Tools -> Script editor. That will start a new Google Apps Script project with a single script: Code.gs. Be sure to set a project title at the top of the page.

Step 2: Create the script

Replace the contents of Code.gs with the script below. It gets the selected cell on the sheet being viewed. If that cell is in a column after B and row after 1 and it is on Sheet1, then the current date is set in the first cell of the same column.

function onEditSheet1(e) {  
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  // The edit is done in any column after B and row after 1 and the sheet name is Sheet1
  if (cell.getColumn() > 2 && cell.getRow() > 1 && sheet.getName() == "Sheet1") {
    sheet.getRange(1, cell.getColumn()).setValue(new Date()).setNumberFormat("yyyy-MM-dd");
  }
};

Step 3: Add a trigger

To execute the script on edits to your sheet, you need to attach it to a trigger. To view your project’s triggers, click the clock symbol on the script editor’s tool bar. That will open up the project’s triggers dashboard. Click the Add Trigger button at the bottom left of the page. Use the following settings:

• Function to run: onEditSheet1
• Deployment: Head
• Event source: From spreadsheet
• Event type: On edit

Click save. You will be prompted to grant permission to run the script with your Google account and informed that the app has not be verified. That is fine since you are the person that created it.

Now go back to your sheet and on Sheet1, try editing some cells. You will see that the cells at the top of the columns you edit get set to the current date.

Closing thoughts

Well now you know enough to be dangerous 🙂

Google Apps Scripts is incredibly powerful. Here, we are just doing simple cell updates but where things can get crazy is when you start making calls to 3rd party APIs. That means you can set triggers that will make calls to Webhooks. At that point, the possibilities with what you can do with these little scripts is limitless. The reference documentation for Google Apps Scripts specific to Google Sheets can be found here.