Google Sheets two onEdits, second onEdit conflicts with first

Question

I'm working in Google sheets trying to set up two onEdit scripts, one to work right after the other. I'm not really a coder but can at least somewhat read coding and for the most part, figured out what and where to change things to make scripts work for my spreadsheet. I've looked all over StackOverflow and Google's help forum but nothing exactly has helped with me with the issue I'm facing.

Here is what I'm trying to do. Column 12 (L) is a status column with the options; In progress, No Resolution, Resolved.

When anything gets flipped to Resolved, I'd like it to move to the bottom of the spreadsheet and then that bottom row gets highlighted.

Here are the two scripts I'm working with, on their own they do what I want but together the second script runs first highlighting the row, and then it moves to the bottom. Also for some reason when the second script is active there is a 50-50 that when the entry is moved to the bottom is moves up one row.

First script:

function onEdit(e){
 // assumes source data in sheet named Problems
 // target sheet of move to named Problems, Problems
 // test column with yes/no is col 12 or L
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if(s.getName() == "Problems" && r.getColumn() == 12 && r.getValue() == 
"Resolved") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Problems");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
 }
}

Second script:

function onEdit2(e) {
if (e) {
    var ss = e.source.getActiveSheet();
    var r = e.source.getActiveRange(); 
    if (r.getRow() != 1 && ss.getName() == "Problems") {

        status = ss.getRange(r.getRow(), 12).getValue();

        rowRange = ss.getRange(r.getRow(),1,1, 12);

        if (status == 'Resolved') {
            rowRange.setBackgroundColor("#99ccff");
        }
        }else if (status == '') { 
            rowRange.setFontColor("#000000");
        }   
    }
}

I've set the onEdit2 to a trigger but that hasn't worked :(

Any help is appreciated and please let me know if any more information is needed.

Sorry, i don't understand why you are using two functions ? If you need the two steps successively, the simplest solution is to use only one function, no ? NB : `}` after the first `setBackgroundColor` could be incorrect... — Tristan, Mar 12 '19 at 14:30
Not exactly sure how to set this up under one function. I'm extremely new to coding, so I found two codes online that separately worked and am now trying to get them to work together. How would I go about putting them into one function? When I tried to do something like that before just by coping and pasting the second script into the first, only the first ran. Took a look at that extra } after setBackgroundColor , it pointed back to the { at the end of: if (r.getRow() != 1 && ss.getName() == "Problems") { — Jww243, Mar 12 '19 at 14:57
if you need to run one function after another... call it from the first function. or use a driver function if you have more complicated logic. `function onEdit(e) { if (/* some condition */) { doThing1(e); } if (/* some condition */) { doThing2(e); } ... }` — tehhowch, Mar 12 '19 at 16:36

ra89fi · Accepted Answer · 2019-03-12T15:31:42.057

1

Try this function. It'll move the Resolved row to bottom then highlight it.

function onEdit(event) {
  var sheet = event.source.getActiveSheet();
  var range = event.source.getActiveRange();
  // move
  if (sheet.getName() == 'Problems' && range.getColumn() == 12 && range.getValue() == 'Resolved') {
    var row = range.getRow();
    var numColumns = sheet.getLastColumn();
    var tRange = sheet.getRange(sheet.getLastRow() + 1, 1, 1, numColumns);
    tRange.setValues(sheet.getRange(row, 1, 1, numColumns).getValues());
    tRange.setBackgroundColor('#99ccff');
    sheet.deleteRow(row);
  }
}

edited Mar 12 '19 at 15:31

answered Mar 12 '19 at 14:52

ra89fi

1,217
1
8
9

Almost!!! I will try to explain this the best I can. So this is what happen: 1: It moved the Resolved down (lets say to line 10) 2. it deleted the empty row that was left behind 3. The entry is now on line 9 but the highlight applied to line 10 which is now empty due to to row deletion – Jww243 Mar 12 '19 at 15:00
1

I just had an idea and I moved your sheet.deleteRow(row); to under the background color code and it worked!!! Thank you thank you! I've been staring at this coding for a day and a half lol. – Jww243 Mar 12 '19 at 15:07

score 0 · Answer 2 · answered Mar 12 '19 at 14:51

The onEdit(e) function is a reserved function by Google AppScript just like the other trigger functions (onOpen(), onChange, ...). Calling it onEdit2(e) will not work as it is not recognized as a trigger function.

Simply change the name to onEdit() so that Google AppScript can trigger it.

Let me know if that works for you

Google Sheets two onEdits, second onEdit conflicts with first

2 Answers2