Script question for a Google spreadsheet

ArtereisArtereis Registered User regular
I have a script in a spreadsheet right now that does the following as an example:

Look at E2:P2 and using the onEdit() function place the current date in Q2. Right now it tracks any edit, including deletions. Is there something I could add to the code to only make it update if the value entered into E2:P2 is a or b?
function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  if( sheet.getName() == "TESTING - Tracking" ) {
    var r = sheet.getActiveCell();
    var i = [5,6,7,8,9,10,11,12,13,14,15,16];
    if(~i.indexOf(r.getColumn())) {
      var row = r.getRow();
      var time = new Date();
      time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
      SpreadsheetApp.getActiveSheet().getRange('Q' + row.toString()).setValue(time);
    }
  }
}

Hope the code isn't too sloppy. I was looking for code samples and had to splice a few examples together in addition to my minimal Javascript knowledge.

Posts

  • AiouaAioua Ora Occidens Ora OptimaRegistered User regular
    edited November 2015
    Artereis wrote: »
    I have a script in a spreadsheet right now that does the following as an example:

    Look at E2:P2 and using the onEdit() function place the current date in Q2. Right now it tracks any edit, including deletions. Is there something I could add to the code to only make it update if the value entered into E2:P2 is a or b?
    function onEdit() {
      var sheet = SpreadsheetApp.getActiveSheet();
      if( sheet.getName() == "TESTING - Tracking" ) {
        var r = sheet.getActiveCell();
        var i = [5,6,7,8,9,10,11,12,13,14,15,16];
        if(~i.indexOf(r.getColumn())) {
          var row = r.getRow();
          var time = new Date();
          time = Utilities.formatDate(time, "GMT-08:00", "MM/dd/yy");
          SpreadsheetApp.getActiveSheet().getRange('Q' + row.toString()).setValue(time);
        }
      }
    }
    

    Hope the code isn't too sloppy. I was looking for code samples and had to splice a few examples together in addition to my minimal Javascript knowledge.

    you'll want to nest in another if statement, yah?
    if (r.value == "a" || r.value == "b")
    {
    
    }
    

    EDIT: I don't actually know javascript or coding for google sheets, fyi consider that strictly pseudocode, but I think you'd want to stick it in right before your if(~i.indexOf(r.getColumn()))

    Aioua on
    life's a game that you're bound to lose / like using a hammer to pound in screws
    fuck up once and you break your thumb / if you're happy at all then you're god damn dumb
    that's right we're on a fucked up cruise / God is dead but at least we have booze
    bad things happen, no one knows why / the sun burns out and everyone dies
  • ArtereisArtereis Registered User regular
    edited November 2015
    That's kind of what I was assuming, but I'm really unfamiliar with the SpreadsheetApp commands, or if OnEdit would actually let me get that granular.

    *edit* It looks like I was just searching on the wrong terms. I had been trying to figure out how to get the cell value from the wrong chapters of the reference guide. A getValue() if statement seems to have solved the problem.

    This can be closed. Thanks for looking at it.

    Artereis on
    Aioua
Sign In or Register to comment.