onEdit() trigger is unable to create a new Spreadsheet

0
votes

I am trying to create a custom function which copies the values of one Sheet and creates another Spreadsheet with those. The Code all in all works just fine, but I need to acces it from mobile. Thats why im using the onEdit(e) trigger. Using this, the script stops in the moment it should create the new sheet. I checked this with a status box and every time it crashes at "Creating Sheet". Is there any other way, so that I can acces my script on mobile or to fix that weird issue.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getActiveRange();

  if(s.getName() == "FlugbuchExport" && r.getColumn() == 4 && r.getRow() == 4 && r.getValue() == true){


    var infoCell = s.getRange(6,3,1,3);
    infoCell.setValue("");
    infoCell.setValue("Process started");



    var dataSheet = ss.getSheetByName("Flugbuch-GmbH"); 
    var windenkladde = ss.getSheetByName("Windenkladde");
    var dateRange = windenkladde.getRange(20,4);
    var date = dateRange.getValue();
    var dateString = Utilities.formatDate(date, "UTC", "dd.MM.yyyy");
    var downloadLinks = ss.getSheetByName("Downloadlinks");


    infoCell.setValue("Neue Tabelle wird erstellt");
    var ssNewName = "LFO " + "Flugbuch - GmbH - " + dateString;
    infoCell.setValue("Creating sheet");
    var ssNew = SpreadsheetApp.create(ssNewName, dataSheet.getLastRow(), dataSheet.getLastColumn());
    var ssNewId = ssNew.getId();



    infoCell.setValue("Data got copied");
    dataSheet.copyTo(SpreadsheetApp.openById(ssNewId));



    var ssNewSheetDelete = ssNew.getSheets()[0];
    ssNew.deleteSheet(ssNewSheetDelete);
    downloadLinks.appendRow([date,ssNew.getUrl()]);
    infoCell.setValue("Process finished");
    r.setValue(false);
  }

}
1
google-apps-scriptgoogle-sheetstriggers

1 Answers

0
votes

You are using a simple onEdit trigger

  • Simple trigger have restrictions, such as:

    They cannot access services that require authorization.

  • If you go from the script editor on View - > Executions, you will see that the execution failed with a message like

Exception: You do not have permission to call SpreadsheetApp.create. Required permissions: https://www.googleapis.com/auth/spreadsheets

Solution

  • Replace the simple onEdit trigger through an installable one
  • For this, rename your function to something different than onEdit
  • Go on Edit > Current project's triggers
  • Click on Add Trigger
  • Select your funciton under Choose which function to run
  • Specify Select event type On edit