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..
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.
Yes this is actually fairly simple {I do this pretty regularly for all manner of sheet/formula augmentation in google sheets}. You'll need the other sheet id which is in the URL, if you don't know how to get it I suggest looking that up really quick. then this script will work
function Correction() {
var rowrange = SpreadsheetApp.getActive().getSheetByName('Sheetname').getRange('Rangeofsheetname');
var rowdata = rowrange.getValues();
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheetname');
for (var i = 0; i<rowdata.length; ++i) {
row = rowdata[i];
var sheetIDs = row[0];
var values = sheet.getRange('rangewithformulawithno=sign').getValue(); //Current sheet/range that you want to copy into the new sheet
SpreadsheetApp.openById(sheetIDs).getSheetByName('Sheettofix').getRange('Range').setFormula("="+values); //destination sheet/range that you want to copy values into
// note that the above will insert a formula from the table that youve created, you could augment line 9 to do whatever you would normally do in your current sheet with the exception of commands that require UI... UI commands should be worked around with non-UI based commands
}
}-You can set up a function in the "sheet 1" spreadsheet that modifies a cell value in the "sheet 2" spreadsheet.
-Then you can use an onEdit trigger in the "sheet 2" spreadsheet to execute specific function(s) when that cell's value is changed.
-Don't forget to clear that cell value after your desired function(s) so this can always work.
