Function works in debugger, but not in live code

Question

I am using Google Sheets to track details about cases. After entering information on the sheet, I paste the URL to the related Doc so it is very easy to open the file from the sheet.

I want to update the description of the Doc file with information from the Sheet. When I paste the URL, I want it to pull the related column information and enter it into the Doc description.

The code is below:

function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var changedCell= event.source.getActiveRange().getA1Notation();
  var column = ss.getRange(changedCell).getColumn();
  if(column == "19"){
    var cellValue =  ss.getRange(changedCell).getValue();
    var id = cellValue.replace("https://drive.google.com/a/mine.net/file/d/","")
                      .replace("https://drive.google.com/open?id=","")
                      .replace("/view?usp=sharing","")
                      .replace("&authuser=0","");
    var row = ss.getRange(changedCell).getRow();
    var desc = Utilities.formatDate(ss.getRange("B"+row).getValue(),"GMT","MM/dd/yyyy")
                   +" "+ss.getRange("C"+row).getValue()
                   +" "+ss.getRange("D"+row).getValue()
                   +" "+ss.getRange("E"+row).getValue();
    var file = DriveApp.getFileById(id);
    file.setDescription(desc);
  }
}

When I run this through debugger, it works perfectly (if I force the changedCell and column information) and I see the updated description in the related file.

However, when I try to run it from the spreadsheet, I get this error:

[14-11-23 19:40:31:061 MST] Execution failed: You do not have permission to call getFileById (line 104, file "Code") [0.006 seconds total runtime]

If I don't have permission, why will it run in debug? And, how do I grant permission? I have full access to both files.

On a related note, is there a better way to extract the id from the url?

UPDATE Here is a screen shot of were I setup the trigger and the called function enter image description here

Even after changing column to a number it still never even hits the alert.

Are you sure youn don't have another function in the same project called onEdit? It would prevent the installable onEdit to work... — Serge insas, Nov 25 '14 at 17:49
The .gs file doesn't have one. Is it possible it is getting one from somewhere else? — davids, Nov 25 '14 at 23:18
any .gs file bounded to the spreadsheet is always active... so the answer is yes. — Serge insas, Nov 25 '14 at 23:28
Is there a way for me to see if any other .gs files are bound to this sheet? — davids, Nov 25 '14 at 23:57

Serge insas · Accepted Answer · 2014-11-24T21:53:41.323

5

As explained in the documentation, simple onEdit triggers have a few restrictions in what they can do : they can't call any function that requires explicit authorization.

DriveApp.getFileById() is obviously a method that requires authorization.

Just change your function name and create an installable onEdit trigger that calls the function (script editor/menu/ressources/triggers).

As for retrieving the ID from the url you could use the split method to get it :

var id = cellValue.split('/d/')[1].split('/')[0];

EDIT

As mentioned in the comments, your condition is not correct, column is a number, not a string.

To convince yourself test the following and check the logger.

function onEditInstall(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var changedCell= event.source.getActiveRange().getA1Notation();
  var column = ss.getRange(changedCell).getColumn();
  Logger.log(typeof(column)+' '+column);// is number
  if(column == 19){
    Browser.msgBox('Luck !')
  }
}

enter image description here

edited Nov 24 '14 at 21:53

answered Nov 24 '14 at 05:57

Serge insas

45,904
7
105
131

I could never get it to run. When I view the triggers I see one with these values: Run="onEditing", Events="From spreadsheet" "On edit". However, this is never called. – davids Nov 24 '14 at 17:30
Have you checked your condition ? I think it is never true because you use "19" as a string but it should be a number... try removing the "" and check again please. Are you sure also about your new function name? I ran a test myself and it was working. – Serge insas Nov 24 '14 at 18:12
I am positive about "19". It works just fine in debugger, and gets to the code inside the if function. I pick the name from the list, so it should be correct. I am not sure what else I am missing. – davids Nov 24 '14 at 21:40
Please also read the comments on this post http://stackoverflow.com/questions/27107903/use-onedit-trigger-on-a-specific-sheet-within-google-scripts-for-google-sheets. , particularlythe part about function names. – Serge insas Nov 24 '14 at 22:27
I appreciate your efforts. Unfortunately, I can't find anything stopping it. I guess I will just have to keep doing it manually for now. – davids Nov 26 '14 at 15:09
Try a new spreadsheet and implement only my example . Once you have a basic function that works you can develop your script from there... good luck.:-) – Serge insas Nov 26 '14 at 15:34

Function works in debugger, but not in live code

1 Answers1

EDIT