How do I configure Apps Script time-based Triggers to execute successfully?
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.
Solution
I believe your goal is as follows.
- You have 2 functions
copyDatabaseandexportTriggerFunction. - Those functions work fine.
- You want to execute these functions by the time-driven trigger.
- In your situation, you want to execute only one time both functions
copyDatabaseandexportTriggerFunctionfrom 23:00:00 to 00:00:00 on the 15th day and the last day of each month.
In this case, how about the following modification?
Modification points:
- When I saw your script is your actual script, it seems that
ScriptApp.newTrigger("copyDatabase")...andScriptApp.newTrigger("exportTriggerFunction")...are used as the global. In this case, whencopyDatabaseandexportTriggerFunctionare run, 2 time-driven triggers are installed. And, the triggers are not deleted. I guessed that your showing image might indicate it.
In order to achieve your goal, how about the following flow?
- Run a script for installing the time-driven trigger.
- In this case, the 15th day or the last day of this month is selected from today's date and time.
- When the time-driven trigger is run, the functions
copyDatabaseandexportTriggerFunctionare run. And, the next time-driven trigger is installed.- In this case, the 15th day or the last day of this month is selected from today's date and time.
- Here, the functions
copyDatabaseandexportTriggerFunctionare used by putting into a function likewrapFunction. From your showing script, I guessed that both functions might be able to be finished within 6 minutes.
When this flow is reflected in your script, it becomes as follows.
Modified script:
Please copy and paste the following script to the script editor. And, please manually run the function installTriggers. By this, the 1st time-driven trigger is installed. After this, the functions copyDatabase and exportTriggerFunction are automatically run by automatically installed the time-driven trigger.
In this modification, only one time-driven trigger is used.
// --- I added the below script.
function installTriggers() {
// These values are from your question.
const time = 23;
const dates = [15, 0];
const extraTime = 60; // seconds
const functionName = "wrapFunction";
ScriptApp.getProjectTriggers().forEach(t => {
if (t.getHandlerFunction() == functionName) {
ScriptApp.deleteTrigger(t);
}
});
const now = Date.now();
const triggerDate = dates.map(e => {
const d = new Date();
d.setMonth(d.getMonth() + (e > 0 ? 0 : 1), e);
d.setHours(time, 0, 0);
if (d.getTime() < now + (extraTime * 1000)) {
d.setMonth(d.getMonth() + 1);
}
return d;
}).sort((a, b) => a.getTime() > b.getTime() ? 1 : -1)[0];
ScriptApp.newTrigger(functionName).timeBased().at(triggerDate).create();
}
function wrapFunction() {
installTriggers();
copyDatabase();
SpreadsheetApp.flush();
exportTriggerFunction();
}
// ---
// --- Below script is your showing script.
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);
}
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);
}
}
Note
- In this modification, your
ScriptApp.newTrigger("copyDatabase")...andScriptApp.newTrigger("exportTriggerFunction")...are removed. If you don't remove them, this script cannot be correctly worked. Please be careful about this.
References:
- Formula for filtering list based off of key with multiple criteria in Google Sheets
- Conditional Dropdowns in Google Sheets Based on Multiple Criteria
- How to use Apps script to move a lot of data rows at same time to different spreadsheet automatically base on time
- How to ignore empty rows when submitting to database
- Custom Menus Google Apps Script
- Google Sheets Index Sidebar of all tabs - add search functionality
- How do I compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match on Google Sheets?
- Google script Display array with rows of different sizes
- List rows in array when the cell contain number in the string
- ImportXML XPath issue using Google Sheets on a simple web scraping query
- Return index of cell that contains match on two strings
- Conditional formatting if cell past date and another cell empty with arrayformula
- How to find the Second Space from the right in Google Sheets?
- Joining more than two WHERE statements in Query Language
- Python gspread create sheet if not exist
- Find if cell contents exist in array
- Google Apps Script: Finding an element in a Column and returning the corresponding row
- Active VS Effective User for Google Sheets
- Rearrange data in table format to rows
- I am not able to extract URL from these cell values using Google Apps Script
- How to count a specific word from a string in a cell from the entire column in google sheets
- Error returned by action script when calling an API with UrlFetchApp.fetch
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- How to execute a Google Apps Script ONLY on the edited row
- How to copy the last row in one table and append to another table in the same GSheet (the same tab)
- Replace duplicate row with new data in Google Sheets using Apps Script
- In Google sheets, how does one either return a blank cell or perform a function based on another cells lack of content?
- Incorrect result of iferror formula via Apps Script
- How to Extract URLs from HYPERLINK in Google Sheets
- Using a reference cell as part of a column in a google sheets query