I am very new to coding so I appreciate the community keeping this in mind. This script is limited to worksheet 1 and worksheet 2. When a line is edited the user email is recorded in the edited row column 41. I would also like to put a timestamp in column 42.
Bottom Line: I need for this to include a timestamp in column 42 AND exclude edits on the first (header) row.
function onEdit (e)
{
var ss = SpreadsheetApp.getActive()
var sheets = ["Worksheet","Worksheet2"]
var ws = e.range.getSheet()
if (sheets.indexOf(ws.getName()) != -1)
{
var row = e.range.getRow()
var rng = ws.getRange(row,41)
rng.setValue(e.user.getEmail())
CodePudding user response:
function onEdit (e)
{
var ss = SpreadsheetApp.getActive()
var sheets = ["Worksheet","Worksheet2"]
var ws = e.range.getSheet()
if (sheets.indexOf(ws.getName()) != -1)
{
var row = e.range.getRow()
var rng = ws.getRange(row,41)
rng.setValue(e.user.getEmail())
var timezone = "GMT-4";
var timestamp_format = "yyyy-MM-dd HH:mm:ss";
var dateCol = headers[0].indexOf('Date');// create a column if its not exits
// note: actRng = the cell being updated
var actRng = event.source.getActiveRange();
var index = actRng.getRowIndex();
if (dateCol > -1 && index > 1) { // only timestamp if 'Date' header exists, but not in the header row itself!
var cell = sheet.getRange(index, dateCol 1);
var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
cell.setValue(date);
}
var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Worksheet");
var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Worksheet2");
var valuesToCopy = sheetFrom.getRange(0, 41, sheetFrom.getLastRow(), 1).getValues();
//Paste to another sheet from first cell onwards
sheetTo.getRange(1,sheetTo.getLastColumn()-1,valuesToCopy.length,1).setValues(valuesToCopy);
}
}
This will add current time to column 42 and copy column 41 from worksheet one to worksheet2
CodePudding user response:
I was able to figure it out, thank you for the help it pointed me in the right direction. The following works:
function onEdit (e)//get the range of the edited cell
{
var ss = SpreadsheetApp.getActive()
var sheets = ["Worksheet","Copy of Worksheet"]
var ws = e.range.getSheet()
if (sheets.indexOf(ws.getName()) != -1)
// Check if the edit was made in an applicable sheet
{
var row = e.range.getRow()
if(row != 1){
//Get the row of the edited cell
var rng = ws.getRange(row,41)
//Range = Row of edited cells and Column 41
rng.setValue(e.user.getEmail())
var rng = ws.getRange(row,42)
//Range = Row of edited cells and Column 42
rng.setValue(Utilities.formatDate(new Date(),"America/New_York","yyyy-MM-dd HH:mm"))
}}}