-2

I'm having an issue with this code and hope you could help me. I'm trying to run a script that builds a Data Validation when a cell is edited. I can tell it reads all the information but when I try to use it, apparently is not working.

Here is the code:

function onEdit(){

  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1zmAy1mwmN7uGBKW7fHGRO4s7z_F-5rijqEf7boBUbE4/edit#gid=0");
  var ss2 = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ztucMytQqL0_6AGrp5Ocza_zS2TIQ5LjhFrF5e8St0k/edit#gid=1691525205");
  var ext= ss2.getSheetByName("Externo");
  var int= ss2.getSheetByName("Interno");
  var nomint = int.getRange("A2:A100").getValues();
  var nomext = ext.getRange("A2:A100").getValues();
  var hoja = ss.getSheetByName("Sistema");
    var nomemi=hoja.getRange("C12");
    var nomrec=hoja.getRange("H12");  

  var celda = hoja.getActiveCell().getA1Notation();
  if(celda="C10"){
    var r = hoja.getRange("C10").getValue();
    if(r = "Interno")
    {
      var emi = nomemi;
    var regla = SpreadsheetApp.newDataValidation().requireValueInList(nomint).build();
  emi.setDataValidation(regla);
  }
    if(r = "Externo")
    {
    var emi = nomemi;
    var regla = SpreadsheetApp.newDataValidation().requireValueInList(nomext).build();
  emi.setDataValidation(regla);
  }
  }
  if(celda="H10"){
    var h = hoja.getRange("H10").getValue();
    if(h = "Interno")
    {
    var emi = nomrec;
    var regla = SpreadsheetApp.newDataValidation().requireValueInList(nomint).build();
  emi.setDataValidation(regla);
  }
    if(h = "Externo")
    {
    var emi = nomrec;
    var regla = SpreadsheetApp.newDataValidation().requireValueInList(nomext).build();
  emi.setDataValidation(regla);
  }
  }

Utilities.sleep(2000);
  n;
}
  • In what way is it not working? Is there an error message? No response? Browser locks up? What? – ale Sep 13 '17 at 14:28
  • it has created the Data validation but it is supposed to create it again every time I change the value in the cell C10 or H10 for the other Data Validation and its not working – Michael Cruz Sep 13 '17 at 14:37
1

I had the same issue on a code working for 1 year.

Seems Google added recently new restrictions to simple triggers and it isn't possible to open other files on the server side.

So, to sum up, onEdit is NOT compatible with .openById(id) nor .openByUrl(url) anymore.

In order to solve it you should use SpreadsheetApp.getActive().

In your case, I'd suggest to =IMPORTRANGES() so you can have everything in the running code SpreadSheet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.