I have implemented 2 Triggers in Apps script. The goal of these triggers are to execute only on 15th and last day of each month. Current issue, triggers are attempting to execute multiple times daily which is causing error exception: This script has too many triggers. Triggers must be deleted from the script before more can be added.
Trigger 1 (copyDatabase):
Expected results: Trigger copies a specified range from Source sheet (Index) to Destination sheet (All Work Orders) on the 15th of each month and last day of each month (between 11pm to midnight)
/// TRigger
ScriptApp.newTrigger("copyDatabase")
.timeBased()
.atHour(23)
.everyDays(1)
.create();
function copyDatabase() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Index");
var desinationsheet = ss.getSheetByName("All Work Orders");
var startRow = 2;
var numRows = sheet.getLastRow() - startRow + 1;
var startCol = 23;
var numCols = 14;
var startColTarget = 1;
var dataRange = sheet.getRange(startRow, startCol, numRows, numCols);
var data = dataRange.getValues();
var Copy = "Copy";
var firstEmptyRow = sheet.getRange("A2:P").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
var dataToCopy = data.filter(row => row[2] !== Copy);
desinationsheet.getRange(firstEmptyRow, startColTarget, dataToCopy.length, dataToCopy[0].length).setValues(dataToCopy);
}
Trigger 2 (exportTriggerFunction):
Expected results: Trigger executes function and exports to specified sheet/tabs to Excel on the 15th (between 11pm to midnight) and last day of each month (between 11pm to midnight)
///////////////
/// TRigger
ScriptApp.newTrigger("exportTriggerFunction")
.timeBased()
.atHour(23)
.everyDays(1)
.create();
function exportTriggerFunction()
{
var today = new Date();
var lastDayOfMonth = new Date(today.getFullYear(), today.getMonth()+1, 0);
if(today.getDate() == lastDayOfMonth.getDate() )
{
var exportSheetIds =
["560568812","1440952465","439649389","513481136",
"1088500659","133657514","1618608630","802444630",
"1834450484","657423004","682313931","1980296394","635144452"]; // Please set the sheet IDs you want to export.
const ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var name = sheet.getRange('Reference!M2').getDisplayValue();
var destination = DriveApp.getFolderById("1HRchNqQ5_0LYzfULw1hWN_ALNuijsy2q"); // Google Drive Folder Location
// 1. Copy the active Spreadsheet as a tempora Spreadsheet.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet().copy('tmp');
const sheets = spreadsheet.getSheets();
const check = sheets.filter(s => exportSheetIds.includes(s.getSheetId().toString()));
if (check.length == 0) {
throw new Error("No export sheets.");
}
// 2. Convert the formulas to the texts.
sheets.forEach(sheet => {
if (exportSheetIds.includes(sheet.getSheetId().toString())) {
const targetRange = sheet.getDataRange();
targetRange.copyTo(targetRange, { contentsOnly: true });
}
});
// 3. Delete/Exclude sheets: Index and Reference
sheets.forEach(sheet => {
if (!exportSheetIds.includes(sheet.getSheetId().toString())) {
spreadsheet.deleteSheet(sheet);
}
});
SpreadsheetApp.flush();
// 4. Retrieve the blob from the export URL.
const id = spreadsheet.getId();
const xlsxBlob = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/export?id=${id}&exportFormat=xlsx`, { headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` } }).getBlob();
// 5. Create the blob as a file.
destination.createFile(xlsxBlob.setName(`${name}.xlsx`));
// 6. Delete the temporate Spreadsheet.
DriveApp.getFileById(id).setTrashed(true);
}
}
Initially, my strategy for executing on the 15th was to configure each trigger’s settings
Select type of time based trigger: Month Timer
Select day of month: 15th
And for executing on the last day of the month, I attempted to edit the code script, but this combination is currently causing the error exception.