Home > Mobile >  Exclude First Row from Edit Script
Exclude First Row from Edit Script

Time:03-14

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

}}}
  • Related