Triggering an add-on via script, is it possible ?

[Clement P]

Hello, 

I'm working on a AppInventor project and I'm using an AppScript to link the app with a googlesheet. Thanks to this script I can "send" data to the googlesheet and read values from this one. I do this because I need to use a solver. So I added an add-on solver on the googlesheet. But I noted that I have to activate it by the button "Solve", which i can't do from my application/script. 

So my question is : Is there a way triggering this button thanks to a script ? 

Thank you for the attention 

Clement P

[Alan Wells]

There is no direct, "built-in" way to trigger add-on code from some other code that is outside of the add-on.  There might be a way to do it indirectly, depending upon your situation.  If the code that would trigger the add-on can use the Sheets API, and there is a way to authorize permission to make changes to the spreadsheet, then you could trigger the "On Change" event in the Sheet, and the add-on would need to install an On Change trigger.  That's the only way that might be a possibility.  Are you already using the Sheets SDK for Android?

[Clement P]

I'm sorry but I know nothing about coding (that's I came here to find help).

What is "the sheet SDK for Android" ? 

Here is the script I use to make the link between the googlesheet and the application Ai2, and the add-on of my google sheet. 

The script is published as web app.

I hope these informations will help ...

[Alan Wells]

I did a search on AppInventor and it looked like it was for Android.

If you want to share code it's better to just post the code rather than an image of the code.

What is the app?  You stated that you are linking an app to the Google Sheet.

[Clement P]

Well, it's a project for my master degree in strength and conditioning. I have to develop an application which provide a training session based on personal time record at running. I use AppInventor because predecessors have started on it.

So, when the athlete use the app, they enter their records. These values are sending on the googlesheet thanks to the script. On this GS calculations are made including the solver. Then he reads the values obtained by the solver and write them in the app. Finally, a training session will be created thanks to these special values from the solver. 

Here is the script :

function doGet(e) {

  

  // ### Write INPUT to A1 to M1 on Sheet2 ###

  if(e.parameter.func == "WRITE") {

    var ss = SpreadsheetApp.getActive();

    var sh = ss. getSheetByName("Sheet2");

    sh.clearContents();

    sh.appendRow([e.parameter.a1,e.parameter.a2,e.parameter.a3,e.parameter.a4,e.parameter.a5,e.parameter.a6,e.parameter.a7,e.parameter.a8,e.parameter.a9,e.parameter.a10,e.parameter.a11,e.parameter.a12,e.parameter.a13,e.parameter.a14,e.parameter.a15]);

    return ContentService.createTextOutput("Data Successfully Written"); 

    }

  

  // ### Read OUTPUT from C2 to C8 on Sheet1 ###

  else if(e.parameter.func == "READ") {

    var ss = SpreadsheetApp.getActive();

    var sh = ss.getSheetByName("Sheet1");

    var rg = sh.getRange("C2:C8").getValues();

    var outString = [];

      for(var row=0; row<9 ; ++row){

      outString.push(rg[row]);

      }

    

    return ContentService.createTextOutput(outString);                     // will return a1,a2,a3,a4,a5

    

    //return ContentService.createTextOutput(JSON.stringify(outString));   // will return [[a1],[a2],[a3],[a4],[a5]]

  }

   

}

And the screenshot of the Ai2, to show you the link between the script and the app.

[Alan Wells]

I see from the image that the published URL of the Web App is in the AppInventor code.  Is that correct?  So AppInventor is making a GET request to the doGet(e) function in your Web App.  Then your Web App will either write values to the Sheet or get values to return to AppInventor.  You are not the owner of the Solver add-on, you just installed it.  So, you have no control over how the Solver add-on works.  Unless the Solver add-on was set up to get triggered by an On Change event, then you're "out of luck."  If the Solver add-on had the option of installing an On Change event, then your Web App could use the Sheets API with a special trick to set values "As User" which would trigger the On Change event.  You would need to contact the developer/owner of the Solver Sheets add-on and have them add that capability.  I highly doubt that the add-on already has that capability.  Unless the Solver add-on has the capability to install an On Change event that will run the add-on code when a value is set in the Sheet, then what you want to do is not possible.  You could contact the owner of the Solver add-on, and ask them if they would collaborate with you.  Otherwise, you'd need some other app that can do what solver does that has an API.  An API is something that the app provides in order for external apps to connect with them.  You need an app that does what Solver does, and provides an API that you can use.

[Clement P]

Ok thank you for your explanation. 

I'll try to contact the developer of the add-on. 

[Clement P]

Well, here is what they told me : 

"We will be unable to send you our code as this is proprietary information. I can suggest using a coding language that is compatible with Google sheets, just as VBA is with excel."

I don't understand how it could be work ?

[Alan Wells]

The add-on developer doesn't need to give you any code.  But, they would need to understand how to implement this.  Have you looked for other code that does the same thing?  Maybe on GitHub?

[clément pomarede]

Hello, I searched on GitHub but honnestly I don't understand how to get a code. And if I get I should find the good part of the code or change it, and I'm not able to do this. 

[Alan Wells]

Accept the things you can not change, and be at peace.