How to get triggers to work in a copied spreadsheet + script?

Question

I'm currently creating a spreadsheet where users can enter an item number and the sheet will return a description and price in the next two columns.

The spreadsheet pulls the item information from another sheet (not another page, rather a whole different URL) and the sheet updates itself every time an item number is entered (no vlookup because the info is on another URL).

Most likely, multiple people will need this form at the same time, and they will also need a copy for reference records. They all have access to the "Master File", the one I have, and I was hoping they could simply make a copy and then fill out the form.

However, while the code in my script works just fine on the Master File, when they make a copy the program won't run. I know it has to do with the triggers not being copied over and I've read up on writing triggers in the script, but here's the problem.

The users cannot see the script - that is, we don't want them to see any code. So they can't go in, turn on triggers via "Resources" or click the run/debug in script editor.

So basically I need a user to be able to open a shared, view only file, make a copy of a spreadsheet (which gets info from another sheet and has triggers), and use that spreadsheet buy inputting item numbers. Most of these people should not be able to see the inner workings, and wouldn't understand any of it anyway.

I was thinking a possible solution would be like what they do in this video around 25:56 or 37:355 where they can press a button and it writes the triggers. They don't go over how to do it though.

Answer 1

If your users are all in a private domain, your best solution would be to publish a private add-on (i.e. available only to domain users). That's not an option if you're using a consumer account.

Alternatively, you can use a menu-driven function to programmatically create the trigger(s) you need. This is effective in your case, because:

• The "original" spreadsheet is shared as read-only, and users are expected to make a personal copy to enter their own data.
• The users will be owners of their copies, so contained scripts will run as them and for them.

For example, you can try this shared spreadsheet. It's shared public, read-only, but if you save a copy, you will see a Custom Menu that sets a trigger function, and updates the first cell in the spreadsheet. Ten seconds late, the trigger function updates it again.

The demo script is contained in the spreadsheet, so you can see it for yourself there. Here's all it contains:

// Create a menu that will initialize the trigger
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Initialize spreadsheet', 'setTrigger')
      .addToUi();
}

function setTrigger() {
  // clear any existing triggers
  var triggers = ScriptApp.getUserTriggers(SpreadsheetApp.getActive())
  for (var i=0; i<triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  // set new trigger
  ScriptApp.newTrigger("runTrigger")
           .timeBased()
           .after(10*1000)    // 10s delay
           .create();
  announce("Trigger set. Wait for it...");
}

function runTrigger() {
  announce("Trigger fired! This completes our demo.");
}

// Update first cell in spreadsheet
function announce(message) {
  var range = SpreadsheetApp.getActive().getSheets()[0].getRange("A1");
  range.setValue(message);
}

Instead of a menu, you could include a "button" image, and link a script to that. I didn't look at the video, but that's probably what they did. You can see more about that (silly, imho) option in How do you add UI inside cells in a google spreadsheet using app script?

Answer 2

From what I understood you don't need a script here, spreadsheet formulas will do the trick.
to import data from one spreadsheet to an other you can use the formula "importData" and put these data in a hiden sheet. then you can use "vlookup" formula on this import or even better a "filter" formula (try the filter formula you'll love it).