User Installable Every Minute Trigger to Standalone Script via Button

Question

I would like a user to be able to tap a button in a sheet to install a trigger for a standalone script that will manage a couple of mail functions. I need this because I have a user that cannot follow instructions, and thus I cannot ensure they will create a trigger manually, properly.

I would like them to just open a Google Sheet, tap the button, approve what needs approving, then be good to go.

So, they need

1. The 'main' function of my "amazingLabels" standalone Google Apps Script to trigger every minute.
2. That must be installed by the amLabsInstallSheet Google Sheet. They have read access to both.

I have used this answer as the basis for what I'm trying to do, I haven't found anything better, the only thing I've changed is some names.

---

So, I have created my own standalone script amazingLabels and inside it is the following:

function doGet(e) {
  createTrigger();
  return ContentService.createTextOutput('success!');
}

function createTimeTrigger() {
  UrlFetchApp.fetch('appUrlExec');
}

function createTrigger() {
  var tr = ScriptApp.newTrigger('main');
      tr.timeBased().everyMinutes(1).create();
}

function main() {
  mainOne();
  mainTwo();
};

Where appUrlExec is the /exec link for this amazingLabels standalone script.

Now, I should be able to create a spreadsheet, which we'll call amazingLabelsInstaller with amazingLabels as a library, and inside the bound script for the spreadsheet call:

function testLib() {
  amazingLabels.createTimeTrigger();
}

However I just cannot get it to install that trigger for the user to run every minute.

If I focus on any other function, it works. Ie. if I call 'main' directly with amazingLabels.main(); it will run that function.

What am I doing wrongly here? Is it the new trigger code?

I've tried every combination of deployment, from a Google Suite deployment, to a private account deployment. From running as myself, to running as the user. I've tried them as editors on both document or none (but it should work as viewers, right?), and with both script and sheet deployed or sheet not-deployed.

Any help would be appreciated, apologies if anything is unclear, here, happy to work through it in the comments.

Answer 1

This will help to prevent creating multiple triggers for the same function.

function createTBT(funcname) {
  const ts = ScriptApp.getProjectTriggers().map(t => t.getHandlerFunction());
  if (!~ts.indexOf(funcname)) {
    ScriptApp.newTrigger(funcname).timeBased().everyMinutes(1).create();
  }
}

Answer 2

Make a clear separation between the 2 scripts you need and provide the necessary scopes

• First script:

Is a script bound to the spreadsheet with the content:

function createTimeTrigger() {
  UrlFetchApp.fetch('appUrlExec');
}

Assign function createTimeTrigger to a custom button.

• Second script:

Is your standalone script with the content

function doGet(e) {
  createTrigger();
  return ContentService.createTextOutput('success!');
}


function createTrigger() {
  var tr = ScriptApp.newTrigger('main');
  tr.timeBased().everyMinutes(1).create();
}

function main() {
  mainOne();
  mainTwo();
};

function mainOne() {
...
}

function mainTwo() {
...
}

Make sure to deploy this script as a WebApp and deploy a new version whenever you made any changes.

Also, the deployment options need to be:

"Execute as": "me", "Who has access to the WebApp": "Anyone, even anonymous".

(If you want to deploy as "The user accessing the WebApp": This is also possible, but you would need to manually pass and apply the OAuth2 token).

Very important:

Make sure that your scripts have the correct scopes

• The bound script needs the https://www.googleapis.com/auth/drive.readonly scope to be able to execute a WebApp programmatically through a Urlfetch call. This scope is not automatically assigned -you need to add it to your manifest. Also, you need the https://www.googleapis.com/auth/script.external_request scope for using the UrlfetchApp in general.

• the WebApp needs at least the scope "https://www.googleapis.com/auth/script.scriptapp" to create the trigger - possibly more scopes depending on the functionality

Answer 3

I have found a workaround (not a fix) to achieve my goals:

Don't use Sheets to do the dirty work, use Sites ... and even then, only do it from a private account.

---

If you try to do this from a G-Suite account, I'm relatively sure that having your G-Suite Admin allow anonymous sharing on Drive will allow you to publish the webapp so that it will run for external people. However this is a sweeping change that isn't granular enough to be specifically for scripts, and could be abused.

---

Anyway, essentially the solution is relatively simple:

1. Publish the script as a web app on a private account with the doGet(e) pointing to the required function.
2. Ensure the right scopes are set, as per ziganotschka's answer.
3. Create a Google Sites site with a unique name.
4. Click the '<> Embed' button on the right, and use the "By URL" method.
5. Paste in the exec link from the deployed standalone script.
6. Publish the page.

Now anyone that visits that link can click through the authorisation flow and it will have activated the required function!

It's not perfect, because it's not under the umbrella of the organisation which it should be. Plus it provides a possible issue with reduced script running rate limits for unpaid accounts.