I have a script that has been working wonderfully the past few weeks, until this morning when it started failing due to there being too many installable triggers on the project.
The script on the main spreadsheet generates new spreadsheets from a template. These new spreadsheets need an installable onEdit trigger to edit values back on the original (main) spreadsheet. I have a function that worked before for generating a new installable trigger, however this was added to the main spreadsheet, not the new one. The issue obviously is that there's a hard limit to the amount of installable triggers that can be tied to one spreadsheet, so I'd like a way to get them associated with the newly generated spreadsheets instead.
I've asked this question before without much resolution, first in the comments here and then in a question here. I also found a very similar question asked four years ago (the answer to this one said there was no solution, I just want to see if anything's changed in four years).
When I tried using my new spreadsheet's ID in my createInstallableTrigger function, it still just kept generating triggers in the main spreadsheet.
I need to know if what I'm asking for is even possible, but if it's not, I'll need a new solution to this problem. The code I'm showing below DOES work, but only up until the limit on triggers is reached.
These are the relevant functions:
function createInstallableTrigger(funcName,ssId) {
if(!isTrigger()) {
ScriptApp.newTrigger(funcName).forSpreadsheet(ssId).onEdit().create();
}
}
function isTrigger(funcName){
var r=false;
if(funcName){
var allTriggers=ScriptApp.getProjectTriggers();
for(var i=0;i<allTriggers.length;i++){
if(funcName==allTriggers[i].getHandlerFunction()){
r=true;
break;
}
}
}
return r;
}
Here's the whole script:
function main(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = e.range;
var newId;
var newName;
var hyperlinkString;
var isJob;
var i;
if(sheet.getRange('L1').getValue() == 10) return;
//Check if edit occurred in relevant range
if((range.getColumn() !== 1) && (range.getColumn() !== 2)) return;
if((range.getRow() == 1) || (range.getRow() == 2)) return;
//Check if both columns were filled after edit
if((range.getColumn() == 1) && (range.offset(0,1).isBlank() == true)) return;
if((range.getColumn() == 2) && (range.offset(0,-1).isBlank() == true)) return;
//Get new document name from concatenate formula in column H
if(range.getColumn() == 1) newName = range.offset(0,7).getValue();
if(range.getColumn() == 2) newName = range.offset(0,6).getValue();
//Check whether the edits occurred on the jobs list or deliveries list (indicated by '4' or '5' in L1)
//Calls function to create new job sheet or delivery sheet from template, gets ID of new spreadsheet
if(((range.getColumn() == 1) && (range.offset(0,3).isBlank() == true)) || ((range.getColumn() == 2) && (range.offset(0,2).isBlank() == true))) {
if(sheet.getRange('L1').getValue() == 4) {
if(range.getColumn() == 1) range.offset(0,3).setValue("Please wait");
if(range.getColumn() == 2) range.offset(0,2).setValue("Please wait");
newId = newJob();
isJob = 1;
}
if(sheet.getRange('L1').getValue() == 5) {
if(range.getColumn() == 1) range.offset(0,3).setValue("Please wait");
if(range.getColumn() == 2) range.offset(0,2).setValue("Please wait");
newId = newdelivery();
isJob = 0;
}
}
//Updates hyperlink if sheet already exists
else {
if(range.getColumn() == 1) {
hyperlinkString = range.offset(0,3).getFormula();
newId = hyperlinkString.substring(77,121);
}
if(range.getColumn() == 2) {
hyperlinkString = range.offset(0,2).getFormula();
newId = hyperlinkString.substring(77,121);
}
return;
}
//Set the name of the new spreadsheet
SpreadsheetApp.openById(newId).rename(newName);
//Enter name and date information onto new spreadsheet
if((sheet.getRange('L1').getValue() == 4) || (sheet.getRange('L1').getValue() == 0) || (sheet.getRange('L1').getValue() == 1)) isJob = 1;
else isJob = 0;
//If it's on a job, update all sheets on itemization spreadsheet (rooftop/electrical pick/return)
if(isJob == 1) {
//If name is entered, update name first then date
if(range.getColumn() == 1) {
for(i=0;i<4;i++) {
SpreadsheetApp.openById(newId).getSheets()[i].getRange('B1').setValue(range.getValue());
SpreadsheetApp.openById(newId).getSheets()[i].getRange('B2').setValue(range.offset(0,1).getValue());
}
}
//If date is entered, update date first then name
if(range.getColumn() == 2) {
for(i=0;i<4;i++) {
SpreadsheetApp.openById(newId).getSheets()[i].getRange('B2').setValue(range.getValue());
SpreadsheetApp.openById(newId).getSheets()[i].getRange('B1').setValue(range.offset(0,-1).getValue());
}
}
}
//If it's on an incoming delivery, just update the main (only) sheet
if(isJob == 0) {
//If name is entered, update name first then date
if(range.getColumn() == 1) {
SpreadsheetApp.openById(newId).getSheets()[0].getRange('B1').setValue(range.getValue());
SpreadsheetApp.openById(newId).getSheets()[0].getRange('B2').setValue(range.offset(0,1).getValue());
}
//If date is entered, update date first then name
if(range.getColumn() == 2) {
SpreadsheetApp.openById(newId).getSheets()[0].getRange('B2').setValue(range.getValue());
SpreadsheetApp.openById(newId).getSheets()[0].getRange('B1').setValue(range.offset(0,-1).getValue());
}
}
//Creates hyperlink to new spreadsheet
if (range.getColumn() == 1) range.offset(0,3).setFormula("=HYPERLINK(\"" + SpreadsheetApp.openById(newId).getUrl() +"\",\"Click here for itemization\")");
if (range.getColumn() == 2) range.offset(0,2).setFormula("=HYPERLINK(\"" + SpreadsheetApp.openById(newId).getUrl() +"\",\"Click here for itemization\")");
//Sort list descending from most recent date
sheet.getRange("A3:D1000").sort({column: 2, ascending: false});
}
function newJob() {
//Open template
var jobTemplateSS = SpreadsheetApp.openById("ID Redacted");
//Create new spreadsheet from copy of template spreadsheet
var newSS = jobTemplateSS.copy("Untitled Job");
//Get folder
var jobFolder = DriveApp.getFolderById("ID Redacted");
//Get ID of new file
var newSSFile = DriveApp.getFileById(newSS.getId());
//Copy file to the correct directory and delete the instance created in root
jobFolder.addFile(newSSFile);
DriveApp.getRootFolder().removeFile(newSSFile);
//Automatically generate an installable trigger on new SS that detects edits
createInstallableTrigger('IncrementDecrement',newSS.getId());
//Pass ID of new spreadsheet back to calling function
return(newSS.getId());
}
function newdelivery() {
//Open template
var deliveryTemplateSS = SpreadsheetApp.openById("ID Redacted");
//Create new spreadsheet from copy of template spreadsheet
var newSS = deliveryTemplateSS.copy("Untitled Delivery");
//Get folder
var deliveryFolder = DriveApp.getFolderById("ID Redacted");
//Get ID of new file
var newSSFile = DriveApp.getFileById(newSS.getId());
//Copy file to the correct directory and delete the instance created in root
deliveryFolder.addFile(newSSFile);
DriveApp.getRootFolder().removeFile(newSSFile);
createInstallableTrigger('IncrementDecrement',newSS.getId());
//Pass ID of new spreadsheet back to calling function
return(newSS.getId());
}
function createInstallableTrigger(funcName,ssId) {
if(!isTrigger()) {
ScriptApp.newTrigger(funcName).forSpreadsheet(ssId).onEdit().create();
}
}
function isTrigger(funcName){
var r=false;
if(funcName){
var allTriggers=ScriptApp.getProjectTriggers();
for(var i=0;i<allTriggers.length;i++){
if(funcName==allTriggers[i].getHandlerFunction()){
r=true;
break;
}
}
}
return r;
}
function IncrementDecrement(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = e.range;
//Check if edit occurred in valid range, and whether the edit occurred on a job or delivery sheet (indicated by a 0,1,or 2 in L1)
if ((range.getColumn() !== 1) && (range.getColumn() !== 3) && (range.getColumn() !== 4) && (range.getColumn() !== 5) && (range.getColumn() !== 6)) return;
if(range.getRow() < 4) return;
if ((sheet.getRange('L1').getValue() !== 0) && (sheet.getRange('L1').getValue() !== 1) && (sheet.getRange('L1').getValue() !==2)) return;
//Wait to declare all variables until checks occur to maximize efficiency
var masterSS = SpreadsheetApp.openById("ID Redacted");
var invsheet = masterSS.getSheetByName("Inventory");
var q;
var deltaQ;
var sku;
var nextCell;
var currentCell;
var cellLink;
var total;
var desc;
var i;
//Set current cell to work on as the edited cell
currentCell = range;
//If edit occurred in first column (SKU) fetch description & value from inventory
if(range.getColumn() == 1) {
//Save SKU
sku = currentCell.getValue();
//Save current cell
cellLink = currentCell;
//Scan through inventory sheet and find matching SKU line, fetch info
currentCell = invsheet.getRange('A3');
while (currentCell.isBlank()==false) {
if(currentCell.getValue() == sku) {
desc = currentCell.offset(0,1).getValue();
q = currentCell.offset(0,2).getValue();
currentCell = cellLink;
break;
}
currentCell = currentCell.offset(1,0);
}
//Return to saved cell
currentCell = cellLink;
//Enter description and value on itemization sheet
currentCell.offset(0,1).setValue(desc);
currentCell.offset(0,3).setValue(q);
}
//If edit occurred in third column (quantity)
if(range.getColumn() == 3) {
//Save quantity and SKU
q = currentCell.getValue();
sku = currentCell.offset(0,-2).getValue();
//Saving prior quantity value. If no value, set to 0.
currentCell = currentCell.offset(0,8);
if (currentCell.isBlank() == true) currentCell.setValue(0);
//Find and save the change in quantity by subtracting new quantity by prior quantity
currentCell = currentCell.offset(0,1);
currentCell.setValue(q - currentCell.offset(0,-1).getValue());
deltaQ = currentCell.getValue();
//Save current cell
cellLink = currentCell;
//Search through inventory sheet and find matching SKU line
currentCell = invsheet.getRange('A3');
while (currentCell.isBlank()==false) {
if(currentCell.getValue() == sku) {
//If it's a pick ticket, subtract the quantity used from inventory quantity
if (sheet.getRange('L1').getValue() == 0) {
currentCell.offset(0,3).setValue(currentCell.offset(0,3).getValue() - deltaQ);
currentCell.offset(0,4).setValue(currentCell.offset(0,4).getValue() - deltaQ);
//Update total price on job list
for(i=3; i<1000; i++) {
if(masterSS.getSheetByName("Job List").getRange(i,1).getValue() == sheet.getRange(1,2).getValue()) {
masterSS.getSheetByName("Job List").getRange(i,1).offset(0,2).setValue(sheet.getRange(1,15).getValue());
break;
}
}
}
//If it's a return ticket or inbound delivery, add the quantity returned/received to the inventory quantity
if ((sheet.getRange('L1').getValue() == 1) || (sheet.getRange('L1').getValue() == 2)) {
Logger.log(q);
Logger.log(currentCell.offset(0,3).getValue());
Logger.log(deltaQ);
Logger.log(currentCell.offset(0,3).getValue() + deltaQ);
currentCell.offset(0,3).setValue(+(currentCell.offset(0,3).getValue()) + +deltaQ);
currentCell.offset(0,4).setValue(+(currentCell.offset(0,4).getValue()) + +deltaQ);
//If it's a return ticket, update total price on job list
if(sheet.getRange('L1').getValue() == 1) {
for(i=3; i<1000; i++) {
if(masterSS.getSheetByName("Job List").getRange(i,1).getValue() == sheet.getRange(1,2).getValue()) {
masterSS.getSheetByName("Job List").getRange(i,1).offset(0,2).setValue(sheet.getRange(1,15).getValue());
break;
}
}
}
//If it's an inbound delivery, update total price on delivery list.
if(sheet.getRange('L1').getValue() == 2){
for(i=3; i<1000; i++) {
if(masterSS.getSheetByName("Delivery List").getRange(i,1).getValue() == sheet.getRange(1,2).getValue()) {
masterSS.getSheetByName("Delivery List").getRange(i,1).offset(0,2).setValue(sheet.getRange(4,7).getValue());
break;
}
}
}
}
}
//Increment for the 'while loop' that searches for SKU in inventory
nextCell = currentCell.offset(1,0);
currentCell = nextCell;
}
//Return to saved cell
currentCell = cellLink;
//Change value of 'prior quantity' to new quantity in case of further changes
currentCell = currentCell.offset(0,-1);
currentCell.setValue(q);
//Return to orginally edited cell
currentCell = currentCell.offset(0,-8);
}
//If edit occurred in fourth, fifth, sixth columns (value, ext. value, total cost)
if((range.getColumn() == 4) || (range.getColumn() == 5) || (range.getColumn() == 6)) {
//If on job sheet, update total price on job list
if ((sheet.getRange('L1').getValue() == 0) || (sheet.getRange('L1').getValue()== 1)) {
for(i=3; i<1000; i++) {
if(masterSS.getSheetByName("Job List").getRange(i,1).getValue() == sheet.getRange(1,2).getValue()) {
masterSS.getSheetByName("Job List").getRange(i,1).offset(0,2).setValue(sheet.getRange(4,6).getValue());
break;
}
}
}
//If on delivery sheet, udate total price on delivery list
if (sheet.getRange('L1').getValue() == 2) {
for(i=3; i<1000; i++) {
if(masterSS.getSheetByName("Delivery List").getRange(i,1).getValue() == sheet.getRange(1,2).getValue()) {
masterSS.getSheetByName("Delivery List").getRange(i,1).offset(0,2).setValue(sheet.getRange(4,7).getValue())
break;
}
}
}
}
}
Thanks for the advice y'all. I'm just a summer intern trying not to have my work fall apart as soon as I leave here :)
