CODEWITHSUNDEEP
spreadsheet
Mark Hengstenberg
Mark Hengstenberg

Reputation: 3

Time stamp a cell when multiple columns are updated

I have been checking out multiple codes on trying to update my Google Spreadsheet but have been unsuccessful when trying to do this with multiple cells. On my spreadsheet I have multiple tabs and when I update a row in column 2,3 or 4, I would like it to enter the date in column 5.

Thank you for your help.

enter image description here

Upvotes: 0

Views: 402

Answers (1)

asiby
asiby

Reputation: 3389

Step 1.

In the Google Spreadsheet, click on "Script editor..." under the "Tools" menu.

Step 2.

Remove any sample script that might be in there and paste the following ...

// Sets the targetColumn on the edited row to the current date if the 
// edited column in within columnBounds.
// Note: This will only handle single cell editing.

// Columns that need to be monitored for changes. Use CAPITAL letters.
var monitoredColumns = ['B', 'C', 'D'];
// Colum that will receive the date.
var targetColumn = 'E'
// To avoid adding the date in the title row, we need to consider the starting row.
var startingRow = 4

// onEdit() is a reserved function name that will be called every time the sheet will be edited.
function onEdit(e) {
  var range = e.range;

  // Row of the edited cell.
  var row = range.getRow();

  // Column of the edited cell.
  var col = String.fromCharCode(64 + range.getColumn());

  if (row < startingRow) {
    // None of the monitored rows have been edited.
    return;
  }

  if (monitoredColumns.indexOf(col) < 0) {
    // Column B, C or D (2, 3 or 4) was not modified.
    // Do not proceed any further.
    return;
  }

  // Current spreadsheet.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Date cell.
  var dateCell = sheet.getRange(targetColumn + range.getRow());
  // Set it to the current date.
  dateCell.setValue(new Date());
}

Step 3

Adjust the values of monitoredColumns, targetColumn and startingRow

Step 4

Start entering some content in the cells.

Upvotes: 1

Related Questions