Trigger a script to execute on external sheet google-apps-scripts

Question

I have a worksheet ("WS-1") in google-apps that does a certain process... Then, I have another worksheet "WS-2" that reads that information via Vlookup and importrange.

I would like trigger the Script in WS1 (to run in WS-1) from WS-2, so I don't need to open WS-1 every time....

Is this possible? Thank you !!

P.S.: another solution is to make the script in WS-1 to automatically run every day..

Answer 1

Yes it's possible...but read further

Let me say from the beginning here, that when I use the term spreadsheet I'm trying to use it in the same sense that the API uses it. So In my vocabulary there is no such thing as a Worksheet. There are Spreadsheets which contain sheets.

I spent sometime this morning thinking about this problem. I took two spreadsheets and wrote some functions to see if I could get projects in different spreadsheets to communicate or projects in the same spreadsheet to communicate and as near as I can tell. They are autonomous entities. Functions defined outside of them are undefined inside of them. And so if you want to trigger another spread to perform an operation using functions that it contains in it's script projects you will have to setup a trigger for that project script.

I took this idea a step further by using two spreadsheets and I created a simple protocol for them to communicate with each other by using a time based trigger to get them to read their messages sheets. They both have similar messages sheets. You can get them to perform several operations by using a readMessages function similar to the function below:

function readMessages()
{
  var ss=SpreadsheetApp.openById(SS1ID);
  var sh=ss.getSheetByName('Messages');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var br='<br />';
  var s='';
  var hA=vA[0];
  for(var i=1;i<vA.length;i++)
  {
    var data=vA[i];
    if(data[data.length-1]=='sent')
    {
      switch(data[data.length-2])
      {
        case 'Read Data':
          data[data.length-1]='rcvd';
          readData()
          break;
        case 'No Action Required':
          data[data.length-1]='rcvd';
          ackMessage(data);
          break;
        default:
          data[data.length-1]='rcvd';
          sendMessage(['Unknown Message Type']);
          break;

      }

    }
  }
  rg.setValues(vA);
}

I didn't actually setup the timebased triggers I just ran the readMessages() function manually but the effect is the same. By issuing commands from one spreadsheet you can cause the other spreadsheet to perform the desired actions. As long as there is some trigger source to trigger the remote sheet to perform the readMessages() function and you follow a known protocol between the two.

This is what the message sheets on each spreadsheet look like:

So yes I think that with these caveats in mind, it is possible for a spreadsheet to cause another remote spreadsheet to perform specific operations that are already contained in the project that's being triggered to read the readMessages function.

Without the triggers to initiate the read action then one spreadsheet can read the data from another spreadsheet but it has no access to functions contained within the other script.

If I'm wrong I hope someone corrects me. This is what I think based upon my study today.