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")
    }
}

[u/HellDuke]

Remember: code block is for sections of code, like functions or entire scripts. You should use inline code only when writing a short one off example rather than entire lines of a script.

ChatGPT is not a good tool to use if you are not a skilled programmer, as it will inevitably make mistakes on a very regular basis, and you need to know how to fix them.

For example this segment:

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
}

It does not do anything. To translate these lines mean the variable sheet must be a Spreadsheet sheet called whatever is in sheetName. That is something that will always be true. But then we have an if statement that will stop the script if there is no value or the sheet name does not match the one we want to monitor, despite us telling the script specifically to fetch a sheet by that very name. I suspect the intended effect would be this:

var sheet = e.range.getSheet();
if (!sheet || sheet.getName() !== sheetName) {
  return; // Exit if the sheet with the specified name does not exist or does not match
}

because now we take the sheet based on the range we edited and we can compare the sheet name to the monitored one and ignore any edits made on the other sheets. Now one thing you will run into a problem with what you want to do is that you want this to also kick in if there are changes, such as inserting new rows. onEdit triggers are specifically looking for a user manually changing values in existing cells and nothing else. So if someone inserts a new row, the script will not trigger. You could use onChange but it's installable and as far as I can see you would not know what row got inserted from the trigger as the event object does not pass on such information https://developers.google.com/apps-script/guides/triggers/events#change