CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsweb-applicationshttp-status-code-401
BadSushi
BadSushi

Reputation: 233

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

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.

Upvotes: 0

Views: 1481

Answers (1)

Cooper
Cooper

Reputation: 64082

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"]

Upvotes: 1

Related Questions