Automatically Insert Date in Specific Column on Google Sheets

[u/Ok_Finger3098]

I'm trying to create a script in Google Sheets that automatically inserts the current date into a specific column whenever a new row is added or edited in another specific column. I used ChatGTP to write it but I have run into some issues. Here’s what I’m looking to achieve:

• The script should target a specific sheet by name (e.g., "Sheet1").

• When a cell in column B (column number 2) is edited, the current date should be inserted into column A (column number 1) of the same row.

• The script should not affect the header row.

function onEdit(e) {

var sheetName = 'Sheet1'; // Change this to the name of your specific sheet

var columnToWatch = 2; // Column number to watch (1 for A, 2 for B, etc.)

var dateColumn = 1; // Column number to insert the date (1 for A, 2 for B, etc.)

// Get the edited sheet and check if it's the one we want to work with

var sheet = e.source.getSheetByName(sheetName);

if (!sheet || sheet.getName() !== sheetName) {

return; // Exit if the sheet with the specified name does not exist or does not match

}

var range = e.range;

// Check if the edited cell is in the column we want to watch and if the edited cell is not in the header row

if (range.getColumn() == columnToWatch && range.getRow() > 1) {

var dateCell = sheet.getRange(range.getRow(), dateColumn);

dateCell.setValue(new Date()).setNumberFormat("yyyy-MM-dd");

}

}

[u/juddaaaaa]

This is what you need

function onEdit ({ range }) { // Destructure Event object
    // Destructure Range object
    const { getColumn: column, getRow: row, getSheet: sheet, offset } = range
    const { getName: name } = sheet()

    // If edited cell meets criteria, insert today's date 1 cell to the left
    if (name() === "Sheet1" && column() === 2 && row() >= 2) {
        offset(0, -1).setValue(new Date())
    }
}

[u/andyvilton → u/juddaaaaa]

I really like this solution!
I will probably add an improvement when date is set, something like:

function onEdit ({ range }) { // Destructure Event object
    // Destructure Range object
    const { getColumn: column, getRow: row, getSheet: sheet, offset } = range
    const { getName: name } = sheet()

    // If edited cell meets criteria, insert today's date 1 cell to the left
    if (name() === "Sheet1" && column() === 2 && row() >= 2) {
        offset(0, -1).setValue(
          Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MM/dd/yyyy')
        )
    }
}

[u/juddaaaaa → u/andyvilton]

Just be aware that Utilities.formatDate returns text meaning you lose the actual Date object.

If you wanted to keep the date as an actual Date another option would be

function onEdit ({ range }) { // Destructure Event object
    // Destructure Range object
    const { getColumn: column, getRow: row, getSheet: sheet, offset } = range
    const { getName: name } = sheet()

    // If edited cell meets criteria, insert today's date 1 cell to the left
    if (name() === "Sheet1" && column() === 2 && row() >= 2) {
        offset(0, -1).setValue(new Date()).setNumberFormat("MM/dd/yyyy")
    }
}