0

So, I have 2 Spreadsheet (Its "Spreadsheet" Not "Sheet) -> Test1 and Test2 I want to copy data from Test1 to Test2 when column 7 on Test1 = "Completed"

But all i can do is copy data to Sheet1 to Sheet2 in Test1 (same spreadsheet). Please Help me!

This is my code

function onEdit(event) {
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if(s.getName() == "Main" && r.getColumn() == 7 && r.getValue() == "Completed") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
  }
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Prasis Prabu
  • 1
  • 1
  • 1
    This is such a simple problem I would be doing you a disservice to just give you the answer. Do the research. Figure it out and you will be much better off in the future. As a hint I would recommend that you learn more about the event object . With efficient use of the event object you can probably accomplish this in about 4 lines. – Cooper Jun 26 '21 at 12:46
  • i had search the answer for 1 week, please help me – Prasis Prabu Jun 26 '21 at 13:13
  • Like @Cooper is saying. The anwser is in line 2. Do not get the active spreadsheet.. Maybe something with openBy..... https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyidid – RemcoE33 Jun 26 '21 at 13:19
  • It Doesn't work ... I use var ss= SpreadsheetApp.openById ("ABC"); and nothing happened – Prasis Prabu Jun 26 '21 at 13:35
  • You don't have to open the active spreadsheet just use event.source. Programmers typically put e there simply because e is only one letter and it's easy to type. The point is that whatever you put there it will be fill with the event object. – Cooper Jun 26 '21 at 13:41
  • So, How is the solution? Please i'm so stressfull find the way, what should I change to my code? or you have any code better? – Prasis Prabu Jun 27 '21 at 08:48

1 Answers1

0

onEdit() is a simple trigger. It can not open another spreadsheet:

Because simple triggers fire automatically, without asking the user for authorization, they are subject to several restrictions:

  • They can modify the file they are bound to, but cannot access other files because that would require authorization.

https://developers.google.com/apps-script/guides/triggers

You need to figure out another way to do it.

Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • Do you have any code better, please help what should i change with my code – Prasis Prabu Jun 27 '21 at 08:49
  • Instead of `onEdit()` you can run a script via Custom menu https://developers.google.com/apps-script/guides/menus But I don't know if it suits you and what the script should do exactly. To take all rows that contain 'Completed' in 7th column from 'Main' sheet of current spreadsheet and copy them to another spreadsheet (to the sheet that has name 'Completed'?)? – Yuri Khristich Jun 27 '21 at 09:00