Google Sheets: How can I test if there are any time-driven triggers assigned to sheet?

Question

Short version: I want to output text in cell B12 if a time-driven trigger has been set for the sheet. I am using a free Google account (not G Suite). If you don't want to read my long version, you can skip to (2) at the end of long version and then to code.

Long version: I created some digital paperwork for our employees. We need to backup these sheets as we cannot rely solely on the Google Drive storage. I was dumbfounded when I realized that the .gsheet files that are pulled down when using Google Drive Backup and Sync are not actual backup files - they are just a file containing a URL which points to the cloud location. So, I figured out how to write a script [function backup()] to make a copy of the sheet as an XLSX file and put it in another folder in GDrive. That is done and works great when I combined it with a time-driven trigger that is installed programmatically.

Next comes the problem. There is a sheet that is named TEMPLATE. Every morning, the supervisors make a copy of the TEMPLATE sheet. They then use the copy to record their daily work. Every job needs a separate sheet. Herein lies the problem - time-driven triggers are not copied when you copy a sheet. So every new sheet that is made (by copying the TEMPLATE) does not have the time-driven trigger installed.

My solution that does not work: To programmatically install time-driven triggers on a newly copied sheet, authorization is required first. To solve this, I created another script that creates a custom menu. In that custom menu the user can click a button that runs the another script I created onOpen(). When selected, onOpen() creates the time-driven trigger programmatically but first asks for authorization. This means on every single job (new sheet created by copying TEMPLATE), someone has to click through the authorization steps to allow the script to run. We run multiple jobs per day. This is a hassle for my employees. I am trying as hard as I can to work around this issue.

(1) I don't think there is any way around the authorization, but if anyone knows any way, PLEASE let me know!

(2) I do not want to require the users to remember to click the custom menu and then the function:

• I want B12 to designate if the user needs to access the custom menu to run the function.
  • So B12 would have the following text written to it "Must choose at top: Custom Menu/Initialize Backend" **IF** a trigger has not been created.

My limited understanding is that the code below works when run straight from the script editor because it bypasses the limitations of the built in function onEdit(e).

When I edit the sheet within the defined range that the code lists, nothing happens because onEdit(e) will not call ScriptApp.getProjectTriggers().

I have spent days researching other solutions, and now I am hoping some kind soul will lend their knowledge. Maybe I can take an entirely different approach? I sure hope this is possible.

For clarity, I hope we can find an answer to what I laid out in (2). But, below is what I want to do stated simply from start to finish. If you can think of a different way to approach this altogether, please let me know:

• End user makes copy of TEMPLATE Google Sheet to use for the day.
• Scripts in the TEMPLATE get copied too, so end user does not have to do anything else.
• Later that day, scripts make a copy of the Google Sheet and saves it as an XLSX file in another Google Drive folder at specified time.
• I use Google Drive Backup and Sync to download all the XLSX files to my comptuer, and then I use CMD Robocopy to transfer to server on a schedule.

function onEdit(e) 
{
  var editRange = { // D2:H8
    top : 2,
    bottom : 8,
    left : 4,
    right : 8
  };

  // Exit if we're out of range
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;

  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;

  // We're in range
  var ss = e.range.getSheet();

  // onEdit won't call ScriptApp.getProjectTriggers() CAN'T MAKE THIS WORK
  if (ScriptApp.getProjectTriggers() < 1) {
    ss.getRange("B12").setValue('Must choose at top: Custom Menu/Initialize Backend');
  }
  else
  {
     ss.getRange("B12").setValue('');
  }
}

Answer 1

Goal:

• Automatically convert/backup all Google sheet files to xlsx files daily.

Issue:

• Attempting to call ScriptApp.getProjectTriggers() from a simple trigger. Simple trigger doesn't have enough authorization to get project triggers.

Solution:

• Use propertiesService to store trigger id, when created. If not present in properties, display a banner.

Snippet:

• onEdit:

var triggerId = PropertiesService.getScriptProperties().getProperty('tid');
if (!triggerId) {
    //or display a banner
    //@see https://stackguides.com/questions/57480181
    ss.getRange("B12").setValue('Must choose at top: Custom Menu/Initialize Backend');
}

• Initialize Backend:

var tid = ScriptApp.newTrigger('myFunc')
    .timeBased()
    .everyDays(1)
    .create()
    .getUniqueId();
PropertiesService.getScriptProperties().setProperty({'tid':tid});

Alternative Approaches:

• If all the files are in a single drive or if someone has edit access to all the files, you can create backups using DriveApp in a single script file(instead of multiple projects).

  • This would be the preferred method.
  • If it's not in a single drive, This can also be accomplished if supervisors make a copy in a folder with edit access to a common user(a user who has access to all files)

• Publish a add-on. This will prevent multiple script projects and multiple authorizations and limit quota usage.

To Read:

• Properties
• Triggers
• DriveApp
• Drive API
• Add ons
• Quotas

Answer 2

Try this:

You must perform an edit within the range. Also you must supply the spreadsheet id as shown below in the code. You also have to run the installAnOnEditTrigger() function and name the function "xxOnEdit" also include the e paramenter to hold the event object.

function xxOnEdit(e) {
  var editRange = {top : 2,bottom : 8,left : 4,right : 8};
  if(e.range.rowStart<editRange.top || e.range.rowStart>editRange.bottom || e.range.columnStart<editRange.left||e.range.columnStart>editRange.right) return;
  var sh=e.range.getSheet();
  if (ScriptApp.getProjectTriggers().length==0) {
    sh.getRange("B12").setValue('Must choose at top: Custom Menu/Initialize Backend');
  }else{
    sh.getRange("B12").setValue('Scripts: ' + ScriptApp.getProjectTriggers().length);
  }
}

function installAnOnEditTrigger() {
  var trgA=ScriptApp.getProjectTriggers();
  var found=false;
  for(var i=0;i<trgA.length;i++) {
    if(trgA[i].getHandlerFunction()=="xxOnEdit") {
      found=true;
      break;
    }
  }
  if(!found) {//This prevents you from creating more than one installable trigger
    ScriptApp.newTrigger("xxOnEdit").forSpreadsheet("SpreadsheetId").onEdit().create();
  }
}