CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Delyana Boyadzhieva
Delyana Boyadzhieva

Reputation: 121

Google script that triggers copy of a range to another spreadsheet - NOT WORKING for me

I have an issue with Google Apps Script that should trigger the copy of a range to another Google spreadsheet. The strange thing here is that the script is perfectly working if started through the Script Editor. But it's not working through the trigger (onEdit).

The script itself is much longer and everything related to data processing within one and the same spreadsheet is triggered successfully. Only the copy of a specific range to another spreadsheet is not working. But as I mentioned, it's working if I run it through the Script Editor.

Please see below a simple representation of my script:

   function onEdit(e){

   var sourceFile1 = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = sourceFile1.getActiveSheet();
   var selectedRange = sheet.getRange(44,1, 1, 29);
   var sourceData = selectedRange.getValues();       
   var destinationFile = SpreadsheetApp.openById("    ");
   var destinationSheet = destinationFile.getSheetByName("Orders");
   var lastRow = destinationSheet.getLastRow();
   lastRow = lastRow + 1;
   destinationSheet.getRange(lastRow, 1, 1, 29).setValues(sourceData);
   }

Upvotes: 1

Views: 126

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • In your script, when you run onEdit() with the script editor, it works.
  • But when the function is run as the OnEdit trigger, it doesn't work.
  • In this situation, you are owner of Spreadsheet and Script.

If my understanding is correct, how about this answer?

I think that in your script, when the OnEdit event is run, an error related to the authorization occurs at var destinationFile = SpreadsheetApp.openById(" ");. When you want to confirm this, please check the execution transcript of script editor after the function is run as the OnEdit trigger.

So as a solution, how about installing the function as the installable Triggers?

Note:

  • In your case, because the error of authorization occurs, you can install the function name onEdit() as the installable Triggers. But I recommend to modify the function name from onEdit() to other. For example, it is like installed_onEdit().
    • Because the function of function name of onEdit() is run as a simple trigger, when onEdit() is installed as the installable Triggers, onEdit() run 2 times as the simple trigger and the installable Triggers.

References:

If I misunderstood your question and this was not the solution of your issue, I apologize.

Upvotes: 2

Related Questions