Home > database >  How to specify cell to update in Google Apps Script
How to specify cell to update in Google Apps Script

Time:07-09

I am writing a script to add a timestamp in another cell whenever a cell within the specified range is updated in Google Sheets. The timestamp should go in column 8 while the editable range in the spreadsheet is from columns 1 through 7. The code I pasted doesn't work because the offset() action will select a cell based on its position relative to the top-left cell in my range - hence, depending on the cell that is updated, the timestamp will go in a different column that its intended column. How do I specify only column 8 to be updated regardless of which cell was edited in my range?

function onEdit(e) {
AddTimestampToMetricsList(e) 
}

function AddTimestampToMetricsList(e) {
const rangeModified = e.range

  console.log(rangeModified.getA1Notation())
  console.log(rangeModified.getSheet().getSheetName())
  console.log(rangeModified.getColumn()) 
  console.log(rangeModified.getRow())  

  if(rangeModified.getColumn() > 7) return
  if(rangeModified.getSheet().getSheetName() !== "Metrics List") return
  if(rangeModified.getRow() < 2) return

  rangeModified.offset(0,8).setValue(new Date())

}

CodePudding user response:

Fix the column to 8

rangeModified.getSheet().getRange(rangeModified.getRow(),8).setValue(new Date())

CodePudding user response:

Edit columns 1 through 7 and timestamp column 8

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "Enter your sheet name" && e.range,columnStart < 8 && e.range.rowStart > Enter header row && e.value) {
    sh.getRange(e.range.rowStart,8).setValue(new Date())
  }
}
  • Related