How to call web app from a google sheet bound script to modify the sheet

Question

I'm trying to make a WebApp that can be triggered by a Google sheet's bound script to modify the sheet under my authority. I've tried working with installable triggers but I need more functionality than what they offer.

I copy and paste the code from Google Apps Script: Temporarily run script as another user and attempted to get it functioning but kept getting the same error Exception: Request failed for https://script.google.com returned code 401. Truncated server response: followed by the page's HTML. I'm having difficulty getting even a basic version of this working and the link above is the most/only relevant guide I've found.

Below is my attempt to get basic functionality (it returns the same error mentioned above). If I open the web app in my browser it adds 1 to the first cell successfully, but I can't figure out how to properly call the web app from the sheet's bound script.

From the spreadsheet's bound script:

function myFunction() {
  UrlFetchApp.fetch('web app url');
}

From the web app:

function doGet() {
  var ss = SpreadsheetApp.openById('spreadsheet id');
  var sheet = ss.getSheetByName('Sheet1');
  var cell = sheet.getRange(1, 1);
  cell.setValue(cell.getValue() + 1);
}

All I need is a functioning example. After that, I can take it from there.

They use curl for most of their examples. This is my first time encountering curl. Is it necessary for what I'm attempting or is it just their preference? I'd prefer to not have to learn it at this point for this project, but if it's necessary I will. — BadSushi
Curl is not needed. Sample scripts are provided both in curl and in apps script. You need to send access token in your fetch request. In addition, make a dummy call to DriveApp.getFilesBy...() to include drive scopes in the access token. — TheMaster

Cooper Cooper · Accepted Answer · 2019-09-05T21:59:12

Try this:

This is runs as me and only I can access it. So you may wish to modify it.

function myFunction() {
  Logger.log(ScriptApp.getService().getUrl());
  var url=ScriptApp.getService().getUrl();
  var params = {
    method: "get",
    headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
  };
  UrlFetchApp.fetch(url,params);
}

function doGet() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  sh.getRange("A1").setValue(sh.getRange("A1").getValue() + 1);
  var html=Utilities.formatString('Process Complete: Current Value: %s',sh.getRange("A1").getValue());
  return HtmlService.createHtmlOutput(html);
}

And these are the scopes I added:

"oauthScopes": ["https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/spreadsheets"]

How to call web app from a google sheet bound script to modify the sheet

1 Answers