Google Sheets moving rows between separate spreadsheets not working but between sheets is working

Question

I have a short function that I made using the answers to other questions here but it is not working perfectly and I don't understand why!

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = e.source.getActiveSheet();
  var r = e.source.getActiveRange();

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

}

The idea is to check for "Verified" and if it is, then move it to a "Verified Expenses" sheet. I prefer this to be a totally separate spreadsheet so my data entry sheet is quick to load. I also tried openByID in case you are wondering.

This is the change I made to try and get it to work:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = e.source.getActiveSheet();
  var r = e.source.getActiveRange();
  var t = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1R6YQAjV5G8JVzr2ecw6IZQFzC8J5KbzLImXOb6KGDIw/edit#gid=0");

  if(s.getName() == "Input Expenses" && r.getColumn() == 14 && r.getValue() == "Verified") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = t.getSheetByName("Verified Expenses");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }

}

I feel like it is something very simple that I overlooked because I'm staring at it too long.

--edit--

This is the execution transcript when I run it as a manual function rather than onEdit()

[15-05-27 12:28:50:192 EDT] Starting execution
[15-05-27 12:28:50:209 EDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[15-05-27 12:28:50:244 EDT] Spreadsheet.getActiveSheet() [0.034 seconds]
[15-05-27 12:28:50:245 EDT] Spreadsheet.getActiveRange() [0 seconds]
[15-05-27 12:28:50:308 EDT] SpreadsheetApp.openByUrl([https://docs.google.com/spreadsheets/d/1R6YQAjV5G8JVzr2ecw6IZQFzC8J5KbzLImXOb6KGDIw/edit#gid=0]) [0.062 seconds]
[15-05-27 12:28:50:308 EDT] Sheet.getName() [0 seconds]
[15-05-27 12:28:50:308 EDT] Range.getColumn() [0 seconds]
[15-05-27 12:28:50:309 EDT] Sheet.getName() [0 seconds]
[15-05-27 12:28:50:309 EDT] Execution succeeded [0.1 seconds total runtime]

You need to use Logger.log() statements, E.g. Logger.log('row: ' + row); and/or use the debugger to figure out what the code is doing. What is the value of s.getName() etc. Check the Execution Transcript under the View menu to see if the code completed. If it didn't, what was the error and what line did it fail on? — Alan Wells
click on "Resources/current project's triggers" and add the onEdit function there as an onEdit trigger. Also run the function manually once so you get the permission pop up where you can give the function the permission to change your spreadsheets. — SpiderPig
Thanks Jonathon I just checked. Would have hit myself if it was that simple. Unfortunately I am checking the right column. Might want to add in something that searches that by name. A little safer in case I edit the spreadsheet. Going to try debugging but I remember having some issue due to the fact that the script doesnt want to run unless I actually edit the sheet so the script always terminates early when I run manually. — Catu
@SpiderPig isn't my function already triggering itself on edit? It works if I am working in only one spreadsheet, without setting a trigger in resources, but it fails between two separate spreadsheets. I will try regardless but seems to be a separate issue. You may be right about the permission but I can't run it manually without getting "Cannot read property "source" from undefined." The undefined is my event! If I don't actually edit the spreadsheet then there is no event! arrrrg. — Catu

Rafael Merlin Rafael Merlin · Accepted Answer · 2015-05-27T18:02:43

I believe I was able to solve this in a simulation environment.

First of all, the onEdit method apparently doesn't work when you access other SpreadSheets as it is supposed to be for simple scripts only. So you'll need to create a function with another name and run it as a trigger as SpiderPig pointed out in the comments of your question.

Besides that, I believe that the moveTo function is supposed to work only inside the same SpreadSheet, so you'll have to use another approach to do that, I've used the appendRow one.

A very helpful place to get information about everything you need on Google App Scripts is this one: https://developers.google.com/apps-script/

Here's the script I used to make it work:

function Test(e) {
  //Oficial
  var s = e.source.getActiveSheet();
  var r = e.range;
  //Test
  //var s =  SpreadsheetApp.getActiveSheet(); //Gets active Sheet.
  //var r = s.getRange(2, 1, 1, 2); //Gets first line after header.

  var targetSheet = SpreadsheetApp.openById("15V0AvU84OBmN0nQweNyYczh5UfOTX77eECzu_1Um6Ug").getSheetByName("Verified Expenses");

  //To View Logs, enter no View, Logs after the execution of the script
  Logger.log(s.getName()); 
  Logger.log(r.getColumn());
  Logger.log(r.getValue());

  if(s.getName() == "Input Expenses" && r.getColumn() == 1 && r.getValue() == "Verified") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var rangeToAdd = s.getRange(row, 1, 1, numColumns).getValues(); //Creates an Array[][], being the first dimension the rows and the second one the columns. 
    var rangeToAddFiltered = rangeToAdd[0]; //Get the first row of the array (as we will only check one by one).
    targetSheet.appendRow(rangeToAddFiltered); //Append the Row to the new SpreadSheet (moveTo appears to work only inside the same SpreadSheet).
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.deleteRow(row); //Profit!
  }
}

Don't forget to add the Test(e) on the onEdit trigger, in Resources, Current project's triggers.

triggers

I hope it helps you.

Update:

If this still doesn't help you, maybe you're having some permission issues (the first time you run a script a window pops up asking permission for the script to change the spreadsheets). Maybe a alternative would be to force an fake event in a fixed range just to be able to debug the function to see what is happening. I've added some commented lines in the above script that, if uncommented, can be used to run the script entirely through the script interface for testing/debugging purposes.

Google Sheets moving rows between separate spreadsheets not working but between sheets is working

1 Answers