2

I have an onEdit function in a spreadsheet that I need to copy over to another spreadsheet where the other spreadsheet is an identical copy, with the same sheet name. It was working perfectly the last time i checked but that was a couple of days ago and it now has just stopped.

The Code:

function onEdit(e){
//  Logger.log("working so far 1");
//  mainfile();
  Logger.log("working so far 4");
//  var ss=SpreadsheetApp.openById(mainssid);
  var ss=SpreadsheetApp.openById("Sheet ID");
  var sh=ss.getSheetByName(e.range.getSheet().getName());
  var rg=sh.getRange(e.range.rowStart,e.range.columnStart);
  rg.setValue(e.value);
} 


function mainfile(){
  Logger.log("working so far 2");
  var SSID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var folder = DriveApp.getFileById(SSID).getParents().next().getName();
  var files = DriveApp.getFoldersByName(folder).next().getFiles();
  var array = [];
  while (files.hasNext()) {
    var file = files.next();
    array.unshift(file.getName());
  }
  array.sort();
  var mainss = array[0];
  var mainssid = DriveApp.getFilesByName(mainss).next().getId();
  Logger.log(mainssid);
  Logger.log("working so far 3");
}

What I would the mainfile function just gets me the id of a specific file and the commented out sections were just where I was trying to implement that into the onEdit function. So the expected result is for me to make an edit on one spreadsheet and for the same change to happen on the other spreadsheet, and the log would say Woriking so far for numbers 2,3,4 but nothing appears.

When I run the mainfile function it works perfectly. I am also aware that this might be a repost of here but seen as they didn't actually get an answer and it just fixed itself I thougt it might not qualify.

It isn't a replica of the other post because I'm not trying to send an email. I have looked at the simple triggers guide and I can't figure out what is wrong with this code as it doesnt ask for permission to run the function normally so i don't think I need autharisation to run it, I know that it can modify other files because it used to work and I have had it work today, it doesnt run for longer than 30 seconds and I haven't exceeded the quota. none of the others seem to apply. Please can you explain to me what I'm doing wrong because I don't understand.

I've also replaced the sheet ID with sheet ID. all credits for onEdit() code go t Cooper.

Thanks in advance, sorry for the rant.

Oliver Nicholls
  • 176
  • 2
  • 15
  • so I did a little test after leaving for about 10 mins it worked again but when I commented out line 6 and uncommented the others it stopped working so maybe its something to do with it having to take an amount of time to activate after a change but its been a while and its still not working yet – Oliver Nicholls Mar 19 '19 at 19:28
  • You're using invalid functions in your simple trigger. Review the rules: https://developers.google.com/apps-script/guides/triggers/#restrictions If you need to access services that require user authorization, use an installed trigger (and don't use the name `onEdit`). You can view all the issues with your function execution by checking your Stackdriver Logs – tehhowch Mar 19 '19 at 19:34
  • @tehhowch can you suggest to me an alternate way to do this then because I can't think of any? – Oliver Nicholls Mar 19 '19 at 19:38
  • Possible duplicate of [onEdit simple trigger never seems to be triggered](https://stackoverflow.com/questions/22333232/onedit-simple-trigger-never-seems-to-be-triggered) – tehhowch Mar 19 '19 at 19:41
  • Read the link to the documentation that I gave. It lists the restrictions. You can review every single Apps Script method and the associated OAuth scopes that are required for it in the official documentation. – tehhowch Mar 19 '19 at 19:59

1 Answers1

0

Simple Triggers can't do things that require authorization.

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

The script must be bound to a Google Sheets, Slides, Docs, or Forms file, or else be an add-on that extends one of those applications.
They do not run if a file is opened in read-only (view or comment) mode.
Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.
They cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization, but a simple trigger can translate a phrase with the Language service, which is anonymous.
They can modify the file they are bound to, but cannot access other files because that would require authorization.
They may or may not be able to determine the identity of the current user, depending on a complex set of security restrictions.
They cannot run for longer than 30 seconds.
In certain circumstances, editor add-ons run their onOpen(e) and onEdit(e) simple triggers in a no-authorization mode that presents some additional complications. For more information, see the guide to the add-on authorization lifecycle.
Simple triggers are subject to Apps Script trigger quota limits.
These restrictions do not apply to doGet(e) or doPost(e).
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • can you get the ID of a File without permissions then? – Oliver Nicholls Mar 19 '19 at 19:52
  • 2
    You don't need to ask this kind of question. Just go test it for yourself. – Cooper Mar 19 '19 at 19:54
  • I don't know what requires permissions and what doesn't, hell I don't really know what permissions are, please could you give me a starting point to look into because im quite lost right now – Oliver Nicholls Mar 19 '19 at 19:56
  • this one? https://developers.google.com/apps-script/guides/services/authorization because if so yes and its not very specific about commands that require auth and ones that don't. also the mainfile function didn't prompt a window to ask for autharization to run when i ran it on its own so I don't get why the onEdit would break because of it – Oliver Nicholls Mar 19 '19 at 20:06
  • You can always look in the execution transcript and see why your script failed. – Cooper Mar 19 '19 at 20:10
  • This is the [Google Apps Script documentation.](https://developers.google.com/apps-script/) – Cooper Mar 19 '19 at 20:12
  • so I went back 5to have a look because I liked your idea about the transcript but I didn't really understand that so I had a look at the log again and it seems to be completing the whole onedit, just not copying to the other sheet – Oliver Nicholls Mar 19 '19 at 20:18
  • 1
    I think you need to be using installable onEdit() trigger and don't name the function onEdit(). – Cooper Mar 19 '19 at 20:23
  • im going to be honest with you Cooper I feel like giving pointers is a more useful teaching tecnique than doing it for them as nice as that is, and then punishing them by refusing to help them in future is quite harsh, I'm new here so maybe that's just how it works but i literally had no idea what to do and a pointer, something more specific than the entire official document might have been encouraging rather than discouraging. I hope you can understand my point of view# – Oliver Nicholls Mar 19 '19 at 20:26
  • onEdit() triggers can be tough to debug. The execution transcript is helpful and I often use the Spreadsheet.toast() method and finally Logger.log() is valuable. – Cooper Mar 19 '19 at 21:10