I have been wrestling with an installable trigger issue for a couple of days now. All of my research indicates that an add-on should allow for an installable onEdit() trigger within a spreadsheet, but my attempts keep erroring out. I have simplified my project code a bit to exemplify my issue.
The error message:
Execution failed: Test add-on attempted to perform an action that is not allowed.
My code (listing functions is the order that they are called):
function onOpen() //creates custom menu for the evaluation tool ***FOR ADMININSTRATORS ONLY***
{
var ui = SpreadsheetApp.getUi();
if(!PropertiesService.getDocumentProperties().getProperty('initialized'))
{
ui.createMenu('Evaluation Menu') // Menu Title
.addItem('Create Installable OnEdit Trigger', 'createInstallableOnEditTrigger')
.addToUi();
}
else
{
ui.createMenu('Evaluation Menu') // Menu Title
.addSubMenu(ui.createMenu('Manage Observations & Evidence')
.addSubMenu(ui.createMenu('Create New Observation')
.addItem('Formal', 'createNewFormalObservation')
.addItem('Informal', 'createNewInformalObservation')
)
.addToUi();
}
}
function createInstallableOnEditTrigger() { // installable trigger to create employee look-up listener when user edits the EIN fields on the Documentation Sheet.
var ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger('onEditListener')
.forSpreadsheet(ss)
.onOpen()
.create();
PropertiesService.getDocumentProperties().setProperty('initialized','true');
}
function onEditListener(event) //this function conitnually listens to all edit, but only engages only certain conditions such as when a timestamp is determined to be needed or the Documentation Sheet needs to be auto-populated
{
//Determine whether or not the conditions are correct for continuing this function
var sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); //determines the name of the currently active sheet
if (sheetName.indexOf("Evidence") > -1) // if the active sheet is an evidence collection sheet, a timestamp may be needed
{
populateEvidenceTimeStamp(event, sheetName);
}
else if (sheetName == "Documentation Sheet") //if the active sheet is the "Documentation Sheet" than auto-population and EIN lookups may be needed
{
employeeLookup(event, sheetName);
}
}
What am I missing? Any help is greatly appreciated!!
The below code has been added as requested by @Mogsdad.
populateEvidenceTimeStamp() is dependent upon generateTimeStamp() which is also included below:
function populateEvidenceTimeStamp(event, sheetName)
{
var evidenceColumnName = "Evidence";
var timeStampColumnName = "Timestamp";
var sheet = event.source.getSheetByName(sheetName);
var actRng = event.source.getActiveRange();
var indexOfColumnBeingEdited = actRng.getColumn();
var indexOfRowBeingEdited = actRng.getRowIndex();
var columnHeadersArr = sheet.getRange(3, 1, 1, sheet.getLastColumn()).getValues(); // grabs the column headers found in the 3rd row of the evidence sheet
var timeStampColumnIndex = columnHeadersArr[0].indexOf(timeStampColumnName); //determines the index of the Timestamp column based on its title
var evidenceColumnIndex = columnHeadersArr[0].indexOf(evidenceColumnName); evidenceColumnIndex = evidenceColumnIndex+1; //determines the index of the evidence column based on its title
var cell = sheet.getRange(indexOfRowBeingEdited, timeStampColumnIndex + 1); //determines the individual timestap cell that will be updated
if (timeStampColumnIndex > -1 && indexOfRowBeingEdited > 3 && indexOfColumnBeingEdited == evidenceColumnIndex && cell.getValue() == "") // only create a timestamp if 1) the timeStampColumn exists, 2) you are not actually editing the row containing the column headers and 3) there isn't already a timestamp in the Timestamp column for that row
{
cell.setValue(generateTimeStamp());
}
}
function generateTimeStamp()
{
var timezone = "GMT-7"; // Arizona's time zone
var timestamp_format = "MM.dd.yyyy hh:mm:ss a"; // timestamp format based on the Java SE SimpleDateFormat class. http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
var currTimeStamp = Utilities.formatDate(new Date(), timezone, timestamp_format);
return currTimeStamp;
}
Below is the employeeLookup() function which is dependent upon lookupEIN()
function employeeLookup(event, sheetName)
{
if(sheetName == "Documentation Sheet" && !PropertiesService.getDocumentProperties().getProperty('initialized')) // if the activeSheet is "Documentation Sheet" and the sheet has not yet been initialized
{
var actRng = event.source.getActiveRange();
Logger.log("employeeLookup(): actRng: "+actRng.getRow()+" , "+actRng.getColumn());
if(actRng.getRow() == 4 && actRng.getColumn() == 9 && event.source.getActiveRange().getValue() != "") //if the "Teacher EIN" cell is the active range and it's not empty
{
var ein = actRng.getValue();
clearDocumentationSheetTeacherProfile(); //first clear the teacher profile information to avoid the possibility of EIN/Teacher Info mismatch if previous search did not yield results
var teacherDataArr = lookupEIN(ein, "Teachers");
if(teacherDataArr)
{
//write retrieved teacher data to Documentation Spreadsheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Documentation Sheet");
sheet.getRange(5, 9, 1, 1).setValue(teacherDataArr[1]); // Teacher First Name
sheet.getRange(6, 9, 1, 1).setValue(teacherDataArr[2]); // Teacher Last Name
sheet.getRange(7, 9, 1, 1).setValue(teacherDataArr[3]); // Teacher Email
sheet.getRange(11, 9, 1, 1).setValue(teacherDataArr[4]); // School Name
sheet.getRange(11, 39, 1, 1).setValue(teacherDataArr[5]); // Site Code
sheet.getRange(10, 30, 1, 1).setValue(calculateSchoolYear()); //School Year
}
else
{
Logger.log("employeeLookup(): type:Teachers 'died. lookupEIN() did not return a valid array'"); //alert message already sent by lookUpEIN
}
}
else if (actRng.getRow() == 4 && actRng.getColumn() == 30 && actRng.getValue() != "" && !PropertiesService.getDocumentProperties().getProperty('initialized')) //if the "Observer EIN" cell is the active range
{
Logger.log("employeeLookup(): 'active range is Observer EIN'");
var ein = actRng.getValue();
clearDocumentationSheetObserverProfile(); //first clear the teacher profile information to avoid the possibility of EIN/Observer Info mismatch if previous search did not yield results
var observerDataArr = lookupEIN(ein, "Observers");
if(observerDataArr)
{
//write retrieved observer data to Documentation Spreadsheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Documentation Sheet");
sheet.getRange(5, 30, 1, 1).setValue(observerDataArr[1]); // Observer First Name
sheet.getRange(6, 30, 1, 1).setValue(observerDataArr[2]); // Observer Last Name
sheet.getRange(7, 30, 1, 1).setValue(observerDataArr[3]); // Observer Email
}
else
{
Logger.log("employeeLookup(): type:Observers 'died. lookupEIN() did not return a valid array'"); //alert message already sent by lookUpEIN
}
}
else
{
Logger.log("employeeLookup(): 'active range is not a trigger'");
//do nothing (not the right cell)
}
}
else
{
//Observer log has already been initialized and documentation cannot be altered. notify user
Logger.log("employeeLookup(): 'log already saved.... alerting user'");
logAlreadyInitializedDialogue();
restoreDocumentationSheetData();
}
}
function lookupEIN(ein, type)
{
Logger.log ("lookUpEIN(): 'engaged'");
var ss = SpreadsheetApp.openById(teacherObserverIndex_GID);
var sheet = ss.getSheetByName(type); //lookup type aligns with the individual sheet names on the teacherObserverIndex_GID document
var values = sheet.getDataRange().getValues();
var val = sheet.getDataRange();
for (var i = 1; i < values.length; i++)
{
if(values[i][0] == ein)
{
Logger.log ("lookUpEIN(): values[i]: "+values[i]);
return values[i];
}
else
{
Logger.log ("lookUpEIN(): 'no match found'");
}
}
//a match could not be found
Logger.log("An EIN match could not be found"); // create a feedback pop-up
einNotFoundDialogue(type); //alert user that there is a problem with the provided ein
}
