2

ow to start a sidebar from the onEdit event in a google sheet, this is my code, where I can be wrong

function onEdit(e){
  var data=[];
  var sourceS = "Livio - Tarde - Revisar",
      targetS = "Reportes - Asignación",
      sourceR = {
        column: 4,
        row: 4,
        endRow: 76,
        endColumn: 26
      }
  if (e.source.getActiveSheet().getName() === sourceS || e.range.rowStart < sourceR.row || e.range.columnStart < sourceR.column || e.range.rowStart > sourceR.endRow || e.range.columnStart > sourceR.endColumn){
    var row = e.range.getRow();
    Logger.log(e.range.getA1Notation())
    var mergedRanges = e.source.getActiveSheet().getRange(row, 1).getMergedRanges();
    for (var i = 0; i < mergedRanges.length; i++) {
      Logger.log(mergedRanges[i].getA1Notation()+" Docente: "+mergedRanges[i].getDisplayValue());
      data['docente'] = mergedRanges[i].getDisplayValue();
    }
    Logger.log( e.source.getActiveSheet().getRange(row, 2).getValue())
    var mergedRanges2 = e.source.getActiveSheet().getRange(row, 28).getMergedRanges();
    for (var i = 0; i < mergedRanges2.length; i++) {
      Logger.log(mergedRanges2[i].getA1Notation()+" Horas Totales: "+mergedRanges2[i].getDisplayValue());
      data['ht'] = mergedRanges[i].getDisplayValue();
    }
    var mergedRanges3 = e.source.getActiveSheet().getRange(row, 29).getMergedRanges();
    for (var i = 0; i < mergedRanges3.length; i++) {
      Logger.log(mergedRanges3[i].getA1Notation()+" Horas Extras: "+mergedRanges3[i].getDisplayValue());
      data['hex'] = mergedRanges[i].getDisplayValue();
    }

    var html = HtmlService.createHtmlOutput('<div style="padding: 5px;">'+
                                            '<div>Docente: <p id="docente"></p></div>'+
                                            '<div>Horas Totales: <p id="ht"></p></div>'+
                                            '<div>Horas Extras: <p id="hex"></p></div></div>')
    .setTitle('Tracking sidebar')
    .setWidth(300);

    var ui = SpreadsheetApp.getUi();
    ui.showSidebar(html);

    Logger.log("docente: "+data['docente'])
    return data
  }
}

the sidebar only starts from the menu or running the function from the editor

Rubén
  • 34,714
  • 9
  • 70
  • 166
Stanley Illidge
  • 191
  • 2
  • 5
  • 2
    Simple triggers like onEdit cannot automatically run services that require authorization like HtmlService. See restrictions on simple triggers https://developers.google.com/apps-script/guides/triggers/#restrictions Instead, try using installable triggers. Rename your function to showSidebar(), go to Edit -> Current project triggers, select your function and choose 'From spreadsheet' - onEdit'. Finally, save the trigger – Anton Dementiev Jan 26 '18 at 17:21

0 Answers0