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
}