1

I have a sheet (sheet-A) that gets it's information from another sheet (sheet-B) using IMPORTRANGE(). In sheet-B I have a function that formats cells based on the a value in the row. At the moment I run this function on a timer because IMPORTRANGE() does not trigger an OnEdit or OnChange event.

I am wondering if there is some other mechanism to trigger an event on sheet-B from sheet-A so that I can run the function on demand1 and not every minute. At times I may need to run the function every few seconds, and I'd like to keep the formatting somewhat interactive, other times the sheet may not be in use for weeks and I don't need to run the function every minute.

1: By "on demand" I mean to run my function when the value of a sheet from an external spreadsheet file changes.

In other words, I'm looking to automate the trigger of the function. For example in my case the user does not have access to the UI when viewing sheet-B and they are editing sheet-A from another machine. In my case the only way I have found to make this work is by using the time-based trigger.

I looked at simple and installable trigger official documentation some time ago, as I said I'm using a simple time-based trigger that runs every minute right now. I'm sure I also looked at installable triggers as well but unfortunately one of their restrictions is they do not run if a file is opened in read-only (view or comment) mode.

Improve this question
3
  • Have you reviewed the simple and installable trigger official documentation?
    – Rubén - Volunteer Moderator - ♦
    Commented Mar 12, 2017 at 15:28
  • I looked at both of these some time ago, as I said I'm using a simple time based trigger that runs every minute right now. I'm sure I also looked at installable triggers as well but unfortunately one of their restrictions is They do not run if a file is opened in read-only (view or comment) mode. I know I did not specify this restriction in my question, but I don't know what I don't know.
    – hoss
    Commented Mar 12, 2017 at 15:36
  • No problem. I'm just asking to narrow the scope of the question in order to help the community including myself to provide a better answer than the one that I already posted. By the way, all the relevant details about the question should be included on the question itself, comments are intended to be temporary.
    – Rubén - Volunteer Moderator - ♦
    Commented Mar 13, 2017 at 13:23

1 Answer 1

Reset to default
1

NOTE: Answer to revision 4

Google Apps Script on edit events are triggered by user made changes only, they aren't triggered on automatic changes like does that occur to automatic recalculation of import functions and functions like NOW. By the other hand, in order to make a change on an external sheet, you should use an installable trigger.

For further details see Installable triggers and search about this both on this site and on Stack Overflow.

NOTE: Answer to revision 1

Besides the use of the run command from the Google Apps Script editor, to run a script on demand you could use a custom menu. There are several threads that include examples of uses of custom menus on this site. Here are few examples:

References

Improve this answer
7
  • Hi, thanks for the answer. However I'm looking to automate the trigger of the function. For example in my case the user does not have access to the UI when viewing sheet-B and they are editing sheet-A from another machine. In my case the only way I have found to make this work is by using the time based trigger.
    – hoss
    Commented Mar 12, 2017 at 14:56
  • The question says "I am wondering if there is some other mechanism to trigger an event on sheet-B from sheet-A so that I can run the function on demand and not every minute". My answer mentions "some other mechanism" to run a script. Is your question about "custom events"?
    – Rubén - Volunteer Moderator - ♦
    Commented Mar 12, 2017 at 15:19
  • I guess I missed the word automate. you have already edited my question so thanks. but yes I am looking to run a function on one sheet based on the state of another sheet.
    – hoss
    Commented Mar 12, 2017 at 15:22
  • Running a function on "on demand" and running a function based on an event of another function are two different things. Please refine your question.
    – Rubén - Volunteer Moderator - ♦
    Commented Mar 12, 2017 at 15:26
  • I sorry, I don't understand, I want to run my function when the value of another sheet changes, isnt that both "on demand" and on an event. the issue is I don't know if there is an event that can satisfy my need for the function to run on demand. I honestly am not sure how I could rephrase the question.
    – hoss
    Commented Mar 12, 2017 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.