Google Sheets Moving Row to Another Sheet

Question

New to Google Sheets and this language in general. Have only played around with VBA in an Access Database we have, but really hitting a wall. Finding lots of similar posts, but still not able to work around my issue.

Off the bat discovered I can't have multiple onEdit triggers, realized I needed to change the onEdit triggers to their own functions, then have one onEdit trigger that calls both of them. Stuff has been working great.

Now I'm trying to do the same with another function I found that will move a row from one sheet to another based on certain criteria. On it's own as an onEdit trigger it does exactly what I want it to do, that code is:

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  
  if(s.getName() == "Active_POs" && r.getColumn() == 23 && r.getValue() == 1) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Need_Entered_QB");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
}
}

When I run that as the onEdit trigger it works great. When I try to change it to it's own function, and then call it through my main onEdit, nothing works. My main onEdit looks like this:

function onEdit(e){
  applyFormat(e);
  adddate();
}

Both of those are called just fine, I'm assuming I got lucky that when I found those as onEdit() triggers I just changed the 'onEdit' name to something unique and could still call it fine. If I try to change the above to 'moverow()' or something similar and call it with my other stuff, nothing. I'm sure this is obvious as to why it's happening, but I'm not well versed enough to understand what I'm doing, other than knowing I'm doing something wrong.

In my mind I should be able to get something like this functioning once I learn what my issue is:

function onEdit(e){
  applyFormat(e);
  adddate();
  moverow();
}

But what exactly is my issue? How can I change the functioning code to move a row just fine to it's own function that I can call in my onEdit trigger?

ziganotschka ziganotschka · Accepted Answer · 2020-07-09T08:52:47

If your new function moverow() includes the content of your old onEdit(event) function it means that it relies on the presence of an event object.
Otherwise calls like event.source.getActiveSheet() won't work
It means that you have to pass to moverow() an event object, so:

function onEdit(e){
  applyFormat(e);
  adddate();
  moverow(e);
}

function moverow(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();  
  if(s.getName() == "Active_POs" && r.getColumn() == 23 && r.getValue() == 1) 
   {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Need_Entered_QB");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}
function applyFormat(event) {
  ...
}
function adddate() {
  ...
}

Note:

You have to make sure that the various funcitons called do not conflict with each other - calls to the SpreadsheetApp are asynchronous.

It's good practive to implement SpreadsheetApp.flush() between the calls of different functions.

Google Sheets Moving Row to Another Sheet

1 Answers