How to transfer data from webapp to addon

Question

1. Project_1 is a container-bound script. A container is a readable spreadsheet (Template).

Code_1:

function doPost(e) {
  return HtmlService.createHtmlOutput(JSON.stringify(e));
}

The user makes a copy of the Template, deploys the script (Project_1) as a webapp with permissions: "Execute as: Me, Who has access: Anyone". The user is the owner of Project_1.

2. Project_2 is a script deployed as an add-on. The user from point 1 is not the owner of Project_2.

Code_2:

function sendPost() {
  var sheetURL = SpreadsheetApp.getActiveSpreadsheet().getUrl();

  var webAppUrl = "https://script.google.com/macros/s/###/exec"; // 7: Part_1 - WebApp: Tester

  // var auth = ScriptApp.getOAuthToken();
  // var header = { 'Authorization': 'Bearer ' + auth };
  var payload = { scriptName: 'updateData', sheetURL: 'sheetURL' };
  var options = {
    method: 'post',
    // headers: header,
    muteHttpExceptions: true,
    payload: payload
  };

  var resp = UrlFetchApp.fetch(webAppUrl, options);
  var respTxt = resp.getContentText();
  console.log('resp: ' + respTxt);
}

function doPost(ev) {
  var respWebapp = func(ev);
}

The user installs an add-on (Project_2).

3. The flow in the direction of addon -> webapp is fine: when sendPost() starts, it sends a request to the webapp and receives a response with the necessary data_1 in response.

The flow in the direction of "someone on the web" -> webapp also flows well: when requesting a webapp_url receives the transferred data_2.

4. I am trying to transfer data_2 to an addon. I read a lot about scripts.run, but it seems that this option is not applicable in such a situation. There is also nowhere to add an eventListener.

5. I would not want to deploy webapp from my account, so as not to spend my quota for simultaneous executions (<= 30). Also I would not like to do a sidebar, etc. in the spreadsheet and try to screw eventListener to html. I assume that with this approach, the listener (if it is possible to add it there at all) will be active only when ui is active (the spreadsheet is open and the sidebar is active). Data can come at any time of the day and must be immediately transferred to the addon.

Added: I feel like I'm stumped. Therefore I reaches out to the community in the hope that someone would suggest a workaround or a new strategy for this initial data. By initial data I mean provide the opportunity for more than 30 users to exchange messages in both directions Spreadsheet <--> External service (for example, Telegram) and at the same time not fall under the limit of 30 simultaneous script executions.

Added_2: I'm going to assign a bounty, so I'm transferring here from the comments what is missing in the post and updating the question itself.

I rejected the option with immediate entry into the sheet, because this will cause constant calls to the spreadsheet and slow down the performance of the system as a whole.

I am aware of the existence of Google cloud functions and Google compute engine, but would like to stay within the free quotas.

QUESTION: How to transfer data from webapp to addon and execute func () or which workaround to use to achieve the goals?

Answer 1

I understand that the solutions proposed in the comments, by others and myself, can't work in your scenario because it can't stand an average delay of 30 seconds. In that case I strongly advise you to set up a Cloud project that can be used as an instant server, as opposed to triggers/apps/etc.

Answer 2

Here is a list of your requirements:

• Trigger add-on code to run from some external request, not using the add-on user interface or time based trigger.
• Code runs from the user's account, using their quota
• Run the add-on code regardless of whether the user is using the add-on or not. For example, their Google Sheet is closed, and the user may even be signed out.

I only know of one way to do that, and it's with a Sheet's add-on by triggering the "On Change" event by setting a value in a Sheet cell using the Sheets API. The Sheets API must use a special option to set the value "As the User."

The special setting is:

valueInputOption=USER_ENTERED

That option will trigger the "On Change" event even if the Sheet is closed. Obviously the script making the request needs authorization from the user to set a value in a cell of the Sheet. If the script sending the request is outside of the user's account then you'd need to use OAuth. The add-on would need to install an "On Change" trigger for the Sheet and the function that the trigger is bound to would need to determine whether the change was from the special cell designated for this special functionality.

If the request to set a value in the users Sheet is from outside of that users Google account, then the user of the Sheet would need to somehow authorize the OAuth credentials for the Sheets API to make a change to the Sheet.

Depending upon the programming language being used with the Google Sheets API, there may be a Sheets API Library specifically for that language. You can also use the Sheets REST API.

There is an example here on StackOverflow of using the Sheets REST API from Apps Script, but if the external request is from some code that isn't Apps Script, it won't be exactly the same.

Answer 3

In "Code_1" and "Code_2" use a shared data store. In other words, instead of directly passing the data from "Code_1" to "Code_2", make that Code_1 write to the datastore and "Code_2" read from it.

One possibility among many is to use a spreadsheet as a database. In this case you might use on change triggers to do some action when the spreadsheet is changed by one of the "Code_1" scripts and/or use time-driven triggers to do some action with certain frequency or at certain datetime.