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.

  • Have you reviewed the simple and installable trigger official documentation? – Rubén Mar 12 '17 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 Mar 12 '17 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 Mar 13 '17 at 13:23
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

  • 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 Mar 12 '17 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 Mar 12 '17 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 Mar 12 '17 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 Mar 12 '17 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 Mar 12 '17 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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