a long time ago my onEdit() function was working without any problem, but suddenly stoped working. The line that spreedshet give me error is: var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName();

Exception: Service Spreadsheets failed while accessing document with id ************

I don´t know what happend

function ss() {
  
  var fila = 3
  var entradadedatos = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Entrada de Datos");
  var entradas = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ventas");
  entradas.activate() 
  var ca = 10
  var valor = SpreadsheetApp.getActive().getRange('\'Entrada de Datos\'!H6').getValue();
  var spreadsheet = SpreadsheetApp.getActive();
  var cod = entradadedatos.getRange('a4').getValue(); //Codigo
  var v = entradadedatos.getRange('f6').getValue(); // vendedor (e,b)
  var nombre = entradadedatos.getRange('d6').getValue(); // nombre cliente
  var fecha = entradadedatos.getRange('d8').getValue(); // fecha
  var pro1 = entradadedatos.getRange(ca,4,1,1).getValue(); // Producto 1
  var k1 = entradadedatos.getRange(ca,8,1,1).getValue();
  var u1 =entradadedatos.getRange(ca,6,1,1).getValue();
  var form = '=F4*H4'
  entradas.insertRowsBefore(entradas.getRange('4:4').getRow(),valor);
  entradas.getActiveRange().offset(0, 0, 1, entradas.getRange('4:4').getNumColumns());
  entradas.getRange(4,2,valor,1).setValue(nombre);
  entradas.getRange(4,3,valor,1).setValue(v);
  entradas.getRange(4,9,valor,1).setValue(fecha);
  entradas.getRange(4,7,valor,1).setValue('=F4*H4')
  entradas.getRange(4,1,valor,1).setValue(cod);
    for(index = 0;index < valor; index  ){
      entradas.getRange(4 index,4,1,1).setValue(pro1);
      entradas.getRange(4 index,5,1,1).setValue(u1);
      entradas.getRange(4 index,6,1,1).setValue(k1);
      entradas.getRange(4 index,8,1,1).setValue(buscar(pro1,1));  
      entradas.getRange(4 index,10,1,1).setValue(buscar(pro1,2));
      ca  = 2
      var pro1 = entradadedatos.getRange(ca,4,1,1).getValue();
      var k1 = entradadedatos.getRange(ca,8,1,1).getValue();
      var u1 = entradadedatos.getRange(ca,6,1,1).getValue();
  }
  Borrar();

  }
function fac(){
    var entradadedatos = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Entrada de Datos");
    var facturas = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Facturas");
    var contador = SpreadsheetApp.getActive().getRange('\'Entrada de Datos\'!F52').getValue();
    var ca = 56
    var row = facturas.getRange("A2").getValue();
    var numfac = entradadedatos.getRange('D52').getValue(); //Codigo
    var emisor = entradadedatos.getRange('D50').getValue();
    var fecha = entradadedatos.getRange('D54').getValue(); // fecha
    var pro1 = entradadedatos.getRange(ca,4,1,1).getValue(); // Producto 1
    var k1 = entradadedatos.getRange(ca,8,1,1).getValue();
    var u1 =entradadedatos.getRange(ca,6,1,1).getValue();
    var c1 =entradadedatos.getRange(ca,10,1,1).getValue();
    facturas.getRange(row,1,1,1).activate();
    facturas.getRange(row,1,contador,1).setValue(emisor);
    facturas.getRange(row,3,contador,1).setValue(numfac);
    facturas.getRange(row,2,contador,1).setValue(fecha);
    for(index = 0;index < contador; index  ){
    var rew = row index  
    facturas.getRange(rew,4,1,1).setValue(pro1);
    facturas.getRange(rew,5,1,1).setValue(u1);
    facturas.getRange(rew,6,1,1).setValue(k1);
    facturas.getRange(rew,7,1,1).setValue(c1);
    facturas.getRange(rew,8,1,1).setFormula("=f" row "*g" rew)  
    
    ca  = 2
    var pro1 = entradadedatos.getRange(ca,4,1,1).getValue();
    var k1 = entradadedatos.getRange(ca,8,1,1).getValue();
    var u1 = entradadedatos.getRange(ca,6,1,1).getValue();
    var c1 =entradadedatos.getRange(ca,10,1,1).getValue();
  }
  entradadedatos.getRangeList(['D50', 'D52', 'D54', 'D56', 'D58', 'D60', 'D62', 'D64', 'D66', 'F56', 'F58', 'F60', 'F62', 'F64', 'F66', 'H56', 'H58', 'H60', 'H62', 'H64', 'H66', 'J56', 'J58', 'J60', 'J62', 'J64', 'J66'])
  .clear({contentsOnly: true, skipFilteredRows: true});
}  
function pr(){
   var entradadedatos = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ventas");
   entradadedatos.getRange('a4').setValue("hola")
}
function buscar(e,a){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const pros = ss.getSheetByName("Inventario");
  const rangedata = pros.getRange(3,12,pros.getLastRow()-1,13).getValues();
  const matchrow = rangedata.find(r => r[0] == e);
  const valie = matchrow[a]
  return valie
}
function Borrar() {
  var entradadedatos = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Entrada de Datos");
  entradadedatos.getRangeList(['D6', 'D8', 'D10', 'D12', 'D14', 'D16', 'D18', 'D20', 'D22', 'D24', 'F24', 'F22', 'F20', 'F18', 'F16', 'F14', 'F12', 'F10', 'H10', 'H12', 'H14', 'H16', 'H18', 'H20', 'H22', 'H24'])
  .clear({contentsOnly: true, skipFilteredRows: true});
};
function C() {
   var entradas = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Entrada de Datos");
   var cliente = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Clientes");
   var nc = entradas.getRange("M26").getValue();
   var t = entradas.getRange("O26").getValue();
   cliente.insertRowsBefore(entradas.getRange('3:3').getRow(),1);
   cliente.getRange("b3").setValue(nc);
   cliente.getRange("c3").setValue(t);
   cliente.getRange("B3:C").sort(2);
   entradas.getRangeList(["M26","O26"]).clear();
   entradas.getRange("d6").setDataValidation(SpreadsheetApp.newDataValidation().requireValueInRange(cliente.getRange("B3:B")).build());
};
function pago(){
  var resumen = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Resumen");
  var cod = resumen.getRange("i6").getValue()
  var cant = resumen.getRange("h6").getValue()
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const pros = ss.getSheetByName("Ventas")
  const rangedata = pros.getRange(4,1,pros.getLastRow(),1).getValues();
  Logger.log(pros.getLastRow())
  var count = 0
  var index = 0
  while(count<cant){
    if(rangedata[index]==cod){
      pros.getRange(index 4,11).setValue("p")
      count  = 1
    }
    index  = 1
  }
  Logger.log(rangedata[0])

}
function onEdit(e){
   var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName();
   var resumen = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Resumen");
   var entradadedatos = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Entrada de Datos");
   const ss = SpreadsheetApp.getActiveSpreadsheet();
   const row = e.range.getRow();
   const col = e.range.getColumn(); 
   const ce = resumen.getRange(row,col).getValue();
   if(entradadedatos.getRange("H27").getValue() == "OK"){
     ss();
     entradadedatos.getRange("H27").setValue("");
   }
   if(entradadedatos.getRange("D38").getValue() == "OK"){
     cliente();
     entradadedatos.getRange("D38").setValue("");
   }
   if(sheet == "Ventas" && col == 4 && row > 3){
     SpreadsheetApp.getUi().alert("aaaaaa");
     ss.getSheetByName("Ventas").getRange(row,8).setValue("a");
   }
   SpreadsheetApp.getUi().alert("eee");

   


   if(sheet == "Resumen" && col == 8 && row > 23 && ce == "P" || ce == "p"){
     
     var cod = resumen.getRange(row,col-6).getValue();
     var cant = resumen.getRange(row,col-7).getValue();
     const ss = SpreadsheetApp.getActiveSpreadsheet();
     const pros = ss.getSheetByName("Ventas");
     const rangedata = pros.getRange(4,1,pros.getLastRow(),1).getValues();
     Logger.log(pros.getLastRow());
     var count = 0;
     var index = 0;
     while(count<cant){
       if(rangedata[index]==cod){
         pros.getRange(index 4,11).setValue("p");
         count  = 1;
       }
       index  = 1;
     }
     const f = resumen.getRange(row,col);
     f.setValue("");

   }
 

}
function cliente(){

  var entradas = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Entrada de Datos");
  var cliente = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Clientes");
  var nc = entradas.getRange("D35").getValue();//nombre
  var t = entradas.getRange("F35").getValue();//codigo
  cliente.insertRowsBefore(entradas.getRange('3:3').getRow(),1);
  cliente.getRange("b3").setValue(nc);
  cliente.getRange("c3").setValue(t);
  cliente.getRange("B3:C").sort(2);
  entradas.getRangeList(["d35","f35"]).clear();
  entradas.getRange("d6").setDataValidation(SpreadsheetApp.newDataValidation().requireValueInRange(cliente.getRange("B3:B")).build());
} 

CodePudding user response：

Replace

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName();

by

SpreadsheetApp.getActiveSheet().getSheetName();

or by

SpreadsheetApp.getActiveSheet().getName();

CodePudding user response：

The line that you think is causing the problem is probably not the correct line because the error suggests that the problem is that you are accessing a file by id and that requires permission https://www.googleapis.com/auth/drive but onEdit is a simple trigger and cannot perform operations that require permission. Try moving to an installable trigger.

The real problem with the below code is that it gets executed on every edit in the entire spreadsheet:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName(); var resumen = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Resumen"); var entradadedatos = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Entrada de Datos"); const ss = SpreadsheetApp.getActiveSpreadsheet(); const row = e.range.getRow(); const col = e.range.getColumn(); const ce = resumen.getRange(row,col).getValue(); if(entradadedatos.getRange("H27").getValue() == "OK"){ ss(); entradadedatos.getRange("H27").setValue(""); } if(entradadedatos.getRange("D38").getValue() == "OK"){ cliente(); entradadedatos.getRange("D38").setValue(""); } if(sheet == "Ventas" && col == 4 && row > 3){ SpreadsheetApp.getUi().alert("aaaaaa"); ss.getSheetByName("Ventas").getRange(row,8).setValue("a"); } SpreadsheetApp.getUi().alert("eee");

So it's a huge burden on your entire spreadsheet. You need to figure out when you want this to run and when you don't.

I'm not suggesting that this version works. I'm just saving my rewrite of your code because I rather start from it on any further questions.

function onEdit(e) {
  const sh = e.range.getSheet();
  var sh1 = e.source.getSheetByName("Resumen");
  var sh2 = e.source.getSheetByName("Entrada de Datos");
  if (sh2.getRange("H27").getValue() == "OK") {
    uf1();//undefined function
    sh2.getRange("H27").setValue("");
  }
  if (sh2.getRange("D38").getValue() == "OK") {
    uf2();//undefined function
    sh2.getRange("D38").setValue("");
  }
  if (sh.getName() == "Ventas" && e.range.columnStart == 4 && e.range.rowStart > 3) {
    SpreadsheetApp.getUi().alert("aaaaaa");
    e.source.getSheetByName("Ventas").getRange(e.range.rowStart, 8).setValue("a");
  }
  SpreadsheetApp.getUi().alert("eee");
  if (sh.getName() == "Resumen" && e.range.columnStart == 8 && e.range.rowStart > 23 && e.value == "P" || e.value == "p") {
    var cod = sh1.getRange(e.range.rowStart, e.range.columnStart - 6).getValue();
    var cant = sh1.getRange(e.range.rowStart, e.range.columnStart - 7).getValue();
    const sh3 = e.source.getSheetByName("Ventas");
    const vs3 = sh3.getRange(4, 1, sh3.getLastRow() - 3, 1).getValues();
    
    var count = 0;
    var index = 0;
    while (count < cant) {
      if (vs3[index] == cod) {
        sh3.getRange(index   4, 11).setValue("p");
        count  = 1;
      }
      index  = 1;
    }
    const f = sh1.getRange(e.range.rowStart, e.range.columnStart);
    f.setValue("");
  }
}