CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Andrew Karp
Andrew Karp

Reputation: 25

How to use onEdit trigger to produce timestamp in a dynamic cell using vlookup?

I'm trying to build a glorified to-do list for posting to various job boards. I have a sheet with all website properties, including a unique ID for each property, as well as columns for when the most recent post was submitted (where the timestamp will land). The to-do list is a tab called Posting Tasks, and includes the same unique ID, as well as a checkbox to mark that post as complete.

Database tab

I'm using the onEdit trigger to produce a timestamp on the Database sheet (column K) when cells in Column A of Posting Tasks sheet are 'true'. However, the timestamps are fixed to the row of the corresponding trigger. I'd like them to be relative based on a matching (vlookup) of the unique ID (column C in the trigger sheet, and column B in the Database sheet), since the Posting Tasks sheet changes daily.

Posting tasks

I'm using the below script, but don't know how to change the e.range.rowStart to be relative and lookup corresponding values. Any ideas?

function onEdit(e) {

    var sheetsToWatch= ['Posting Tasks', 'Database'
                      ],
        columnToWatch = 1,
        columnToStamp = 11;           

    if (e.range.columnStart !== columnToWatch
      || sheetsToWatch.indexOf( e.source.getActiveSheet().getName() ) === -1 
      || !e.value)
        return;
    e.source.getSheetByName('Database')
        .getRange(e.range.rowStart, columnToStamp)
        .setValue(new Date());

}

Upvotes: 0

Views: 161

Answers (1)

Cooper
Cooper

Reputation: 64082

Try this:

function onEdit(e) {
  e.source.toast('flag1');
  var sh=e.range.getSheet();
  var name=sh.getName();
  if(name=='Posting Tasks' && e.range.columnStart==1 && e.value) {
    e.source.toast('flag2');
    var id=e.range.offset(0,2).getValue();
    var tsh=e.source.getSheetByName('Database');
    var idA=tsh.getRange(3,2,tsh.getLastRow()-2,1).getValues().map(function(r){return r[0]});
    var row=idA.indexOf(id)+3;
    var tsh=e.source.getSheetByName('Database').getRange(row,11).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy HH:mm:ss"));
  }
}

Andrew Karp Modification:

var sh = e.source.getActiveSheet(); 
if (sh.getName() !== 'Posting Tasks' || e.range.columnStart !== 1 || e.range.rowStart < 3 || e.value !== 'TRUE') return; 
e.range.offset(0, 0).clearContent() } }

Upvotes: 1

Related Questions