0

I have sheet that I have been working on that has an installable onEdit trigger, that works as it should. But my issue is that when I copy the sheet you have to go into App Scripts and run the create trigger to set the authorization. I have as you'll see in the code below tried to install a menu item that would allow the user to run the create trigger without going into the project. I would really like to find a way if at all possible to get around the authorization each time the file is copied. The users will be creating several copies throughout the day so I am trying to make this as easy as possible for the end user. I am open to any suggestions on getting this to run smoothly.

    var ui = SpreadsheetApp.getUi()
    var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    var ssID = spreadSheet.getId();
    var ssName = spreadSheet.getName();
    var s1 = spreadSheet.getSheetByName('Sheet1');
    var tp = spreadSheet.getSheetByName('Time Pivot');
    var qa = spreadSheet.getSheetByName('QA Pivot');
    var lastRow = s1.getLastRow();
    var lastColumn = s1.getLastColumn();
    var cell = spreadSheet.getSheets()[0].getRange('A2');

    function onOpen() {

      tp.getRange('G1').setValue('StartTime');
      tp.getRange('G2').setValue(new Date());

      ui.createMenu('TSB Stuff')
        .addItem('Do Me First', 'createEditTrigger')
        .addSeparator()
        .addSubMenu(ui.createMenu('Scan Type')
          .addItem('Scan A IMEI', 'scanAImei')
          .addItem('Scan A SIM', 'scanASim')
          .addItem('Scan B IMEI', 'scanBImei')
          .addItem('Scan B SIM', 'scanBSim')
          .addItem('Scan Tablets', 'scanTablet'))
        .addSeparator()
        .addItem('Find Duplicates', 'columnMain')
        .addItem('Insert Tracking Pivot', 'insertTrackingPivot')
        .addItem('Insert QA Pivot', 'insertQAPivot')
        .addItem('Insert Time Pivot', 'insertTimePivot')
        .addItem('Save Excel', 'saveAsXlsx')
        .addItem('Send Email', 'sendMail')
        .addItem('Clear Contents', 'clearSheet')

        .addToUi();

    }

    function createEditTrigger() {
      ScriptApp.newTrigger('onEdit')
        .forSpreadsheet(spreadSheet)
        .onEdit()
        .create();
    }

    function onEdit() {
      var row = s1.getActiveCell().getRow();

      if (s1.getName() == "Sheet1") {
        var c = s1.getActiveCell();
        var timeCell = c.offset(0, 12);
        if (c.getColumn() == 1) {
          var modelCell = c.offset(0, 4);
          var invTypeCell = c.offset(0, 6);
          var returnValues = getModelInvType(String(c.getValue()));
          var modelInfo = returnValues.toString().split(',');
          var f2Value = s1.getRange('F2').getValue();
          var f = c.offset(0, 5)
          var cpRange = s1.getRange('H2:J2');
          var psRange = s1.getRange(row, 8, 1);
          cpRange.copyTo(psRange);
          modelCell.setValue(modelInfo[0]);
          invTypeCell.setValue(modelInfo[1]);
          timeCell.setValue(new Date());
          f.setValue(f2Value);
        }
      }
    }

    function getModelInvType(c) {
      const dbUrl = 'jdbc:google:mysql://ConnectionName/dbName';
      const user = '*******';
      const userPwd = '*************';
      const output = []
      const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
      const stmt = conn.prepareStatement('SELECT Model,idInvType FROM ProductDetail WHERE Serial = ?');
      stmt.setString(1, c);
      const resultSet = stmt.executeQuery();
      const numCols = resultSet.getMetaData().getColumnCount();
      while (resultSet.next()) {
        tmpCols = numCols;
        const row = [];
        while (tmpCols--) row.push(resultSet.getString(numCols - tmpCols));
        output.push(row)
      }
      stmt.close();
      resultSet.close();
      return output; //return output
    }

Rubén
  • 34,714
  • 9
  • 70
  • 166
Robert
  • 1
  • 2
  • Auth needs to happen every time. But try to remove the `starttime` part out of the onOpen, because this will need permissions (to alter data in the sheet), so the menu does not appear. And place the global `ui` inside the `onOpen`. – RemcoE33 Mar 31 '22 at 18:54
  • If you are creating an installable onEdit you definitely should not name it onEdit and you might consider creating a function that looks for a trigger handler before creating another one because you really only want one for each function – Cooper Mar 31 '22 at 23:20

0 Answers0