CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Weilies
Weilies

Reputation: 530

How to fire an onOpen event in Google Spreadsheet when a spreadsheet was 'Make a Copy'?

I know some basic javascript/app-script and i have my following code

function onTimesheetOpen() {  
  var source = {
    ssId : '1Mqbh09mj_UoRZiQkzw1gOVpHFiYk-8qZvpnDbzCYOFY',
    shName : 'prompt',
    a1Notation : "A1:A100",
  };

  var target = {
       ssId : SpreadsheetApp.getActiveSpreadsheet().getId(),
    shName : 'Setups',
    a1Notation : "A1:A100",
  };

  AsExt.UTLCopyCell(source, target);
}

It's a READONLY timesheet template shared to a group of workers, where it was intended to copy list of employees from employee sheet (source) and populate into my target (the template sheet). The onTimesheetOpen() trigger because i set a trigger "From Spreadsheet", "On Open" event. Everytime the template opened, it will copy updated employees from master employee sheet.

The workflow for the workers are to 'make a copy' from the template timesheet file to their drive (My Drive) and i noticed the trigger will be deleted everytime i made a copy. It's quite different from traditional programming language and i do hope to get some helps from the gurus :) I couldn't expect every worker to open script editor after 'made a copy' as they are not IT trained, their jobs is to get the timesheet, which pull for a list of latest employees (master file).

I also tried the installable trigger https://developers.google.com/apps-script/guides/triggers/installable which is also not that suitable.

In order to create a trigger into their copied file, they have to run following code ONCE (which are confusing as they not IT trained)

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onTimesheetOpen')
      .forSpreadsheet(ss)
      .onOpen()
      .create();
}

What's the best way to share a readonly template to workers and it will directly grab latest employee list from master employee sheet without let them touching the code?

Thanks in million!

Upvotes: 0

Views: 945

Answers (2)

Jack Brown
Jack Brown

Reputation: 5892

Alternate Solution: Since most of your users are on the smart phones, you can do the following. You can publish your sheet, just select webpage option. And it will create link like so:

https://docs.google.com/spreadsheets/d/e/2PACX-1vSLilJvnFXiPW1ZsqRNQIVQDXasxYihzbEpxCO54nSpjv1IBIur1p5U7G8oJ2P8ThmZXYuV6LPJVZ8s/pubhtml?gid=725782869&single=true

Which you can directly share with your workers or you can use importHtml() function of the google sheets to import these publish values into your sheet like so

IMPORTHTML("https://docs.google.com/spreadsheets/d/e/2PACX-1vSLilJvnFXiPW1ZsqRNQIVQDXasxYihzbEpxCO54nSpjv1IBIur1p5U7G8oJ2P8ThmZXYuV6LPJVZ8s/pubhtml?gid=725782869&single=true","table",1)

Notes
1) The link can be viewed by anyone
2) The if viewed online it suppose to update every 5minutes or so.
3) If viewed in sheet using importHtml(), it will refresh when you reopen the sheet.
4) ImportHTML() does require to authorized like importRange() so will still work when copied.
5) You can publish your source sheet directly.

Upvotes: 0

Stefan van Aalst
Stefan van Aalst

Reputation: 778

Have you tried:

function onOpen(e) {}

And with

  ScriptApp.getProjectTriggers()

You probably can check if the trigger is already installed.

Upvotes: 0

Related Questions