Search code examples
google-sheetsauto-populate

I want to auto populate a cell with date when adjacent cell is populated or changed in Google Sheets

I'm specifically working with Google sheets app on either my tablet or cellphone (though I can access an actual computer and update it through that as well, I mainly work within the apps) and I need to be able to auto-populate the cell in column C with the current date whenever there is data entered or changed. I'm reading all the other answered questions and they're based on excel and I'm not sure it applies to Google Sheets. I'm not very familiar with code or formulas so if you can give me the exact code I need to use and tell me where out needs to be entered I can follow those instructions but just giving me a bunch of code jargon well only serve to confuse me now.

Here's what I'm working on. I have a sheet where all data in Column A is the name of a person, column B is their current weight, and column C is the date they entered their current weight whether it be a new entry or changing an existing entry. I hope this helps.

Solution

  • make a copy of this sheet: https://docs.google.com/spreadsheets/d/

    what you are asking is done by a script which runs whenever you enter something in column B

    0

    • go to Tools

    • select Script editor

      0

    • copy paste this script:

    function onEdit(event) { 
  var sheet = event.source.getActiveSheet();

  // note: actRng = the cell being updated
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();

  if (cindex == 2) { // 1 == Column A, 2 == Column B, 3 == Column C, etc.
   var dateCol = sheet.getLastColumn();  
   var lastCell = sheet.getRange(index,dateCol);
   var date = Utilities.formatDate(new Date(), "EST", "MMM-dd-yyyy");
   lastCell.setValue("'" + date);
  }
}

    • save the project under some name

    • return to your sheet (you can close script window/tab)