Search code examples
google-apps-scriptgoogle-sheetsweb-applications

How to authenticate programmatically when running an Apps Script Web App from another Apps Script project

I am new to Apps Script and trying to understand the basics of running/triggering a script in one account using another. With just caveat: I'd like to run the script with the user accessing the script instead of the user owning the script -- in order to assign the run time to the user accessing.

I am however running into the following issues.

Starting with a test SpreadSheet with Sharing set to Anyone With Link can edit and the following code in the script project page:

function doPost(e){
  var sheet = SpreadsheetApp.openById('1tWV6ELJEGkWkSXvdf9kQemvH-tDVTx0od4JHht2ZBeU');
  var tab = sheet.getSheetByName('ref');
  tab.getRange(1,1).setValue(new Date());
  return ContentService.createTextOutput(0)
}

function doGet(e){
  return doPost(e)
}

and having published the project as a Web App with execution set to user accessing and access enabled for Anyone. Entering the following link in the browser manually fills cell A1 with current time and the page displaying '0', as intended,

https://script.google.com/macros/s/AKfycbwNhYg1BRKi38pNf_z0peGuYt6gsqvauCvo-eiGgCYJJk4QDpjm/exec

IF I enter the link while still signed in with the account that created the test SpreadSheet.

If I enter the link with a different browser without GSuite log-in, I am required to sign in, which is also as expected. After all, a G Suite account is required to run the script per deployment of the Web App .

However, when I tried triggering the script using a different GSuite account and Apps Script project, I still ran into the sign-in page and also other issues. Out of

function test1(){
  const scriptURL='https://script.google.com/macros/s/AKfycbwNhYg1BRKi38pNf_z0peGuYt6gsqvauCvo-eiGgCYJJk4QDpjm/exec';  
  var response = UrlFetchApp.fetch(scriptURL)
  Logger.log(response.getContentText())
}

function test2(){
  const scriptURL='https://script.google.com/macros/s/AKfycbxay75fTBt3doTyMFUPK0-GpK9hMZ4hVkYdiwYUBMhPfEN6hUJH/exec';  
  var response = UrlFetchApp.fetch(scriptURL, {
    method:'POST',
    payload:'nothing'
  });  
  Logger.log(response.getContentText())
}

function test3() {
  var sheet = SpreadsheetApp.openById('AKfycbwNhYg1BRKi38pNf_z0peGuYt6gsqvauCvo-eiGgCYJJk4QDpjm');
  Logger.log(sheet.getName());
}

function test4() {
  var token = ScriptApp.getOAuthToken();
  const scriptURL='https://script.google.com/macros/s/AKfycbxay75fTBt3doTyMFUPK0-GpK9hMZ4hVkYdiwYUBMhPfEN6hUJH/exec';  
  var response = UrlFetchApp.fetch(scriptURL, {
    headers: {Authorization:'Bearer '+ token},
    method:'GET',
    payload:'nothing'
  });  
  Logger.log(response.getContentText())
}

test1() produces a long body of text in Log starting with

Logging output too large. Truncating output. 
<!DOCTYPE html>
<html lang="en">
  <head>
  <meta charset="utf-8">
  <meta content="width=300, initial-scale=1" name="viewport">
  <meta name="google-site-verification" content="LrdTUW9psUAMbh4Ia074-BPEVmcpBxF6Gwf0MSgQXZs">
  <title>Sign in - Google Accounts</title>
  <style>

and test2() returns an eror

Exception: Request failed for https://script.google.com returned code 401. Truncated server response: <!DOCTYPE html><html lang="en"><head><meta name="description" content="Web word processing, presentations and spreadsheets"><meta name="viewport" c... (use muteHttpExceptions option to examine full response)
    at test2(Code:23:30)

Neither test triggered the script in doPost(e) since the cell A1 was not updated.

test1() seems to encounter the sign-in page. Yet, at the same time, test1() is executed via a sign-in G Suite account.

It may also be worth noting that that test3() is able to access the Google SpreadSheet file in question. I also tried calling ScriptApp.getOAuthToken() because why not. test4() has the same response as test1().

I don't know where to begin diagnosing with the error encountered in test2().

How do I get around the sign-in page?

In particular, is there a way to make the script accessible by any other GSuite account with access permission to the Google SpreadSheet in question and executed by user accessing as opposed to user owning the SpreadSheet?

What am I doing wrong with the POST version?

Any help is apprecriated!

Solution

  • From your question, I understood your situation as follows.

    • The script of Web Apps is put to the container bound script of the Spreadsheet of 1tWV6ELJEGkWkSXvdf9kQemvH-tDVTx0od4JHht2ZBeU.
    • The Spreadsheet is shared as Anyone with the link of editor.
    • The Web Apps is deployed as Execute the app as: User accessing the web apps and Who has access to the app: Anyone.

    This answer supposes my above understanding. So when my understanding is not correct, please tell me.

    Modification points:

    • At UrlFetchApp, when payload:'nothing' is used, it requests as POST method even when method:'GET' is used. Please be careful this.
    • In your script of Web Apps uses the scope of https://www.googleapis.com/auth/spreadsheets. In this case, at first, it is required to authorize the scope by own browser (each user's browser). When this is not done, even when the script and the access token with the scopes of client side are correct, an error of Authorization needed occurs. Please be careful this. It seems that this is the current specification.
    • If the whole script (test1 to test4) of your client side is the script in your question, the scopes are https://www.googleapis.com/auth/script.external_request and https://www.googleapis.com/auth/spreadsheets. I think that the scope is not enough for requesting the Web Apps. In this case, for example, please add // DriveApp.getFiles() to the script editor. By this, the scope of https://www.googleapis.com/auth/drive.readonly is automatically added by the script editor and this scope can be used for requesting to Web Apps. Also, you can use the scope of https://www.googleapis.com/auth/drive.
      • In the case of scopes of https://www.googleapis.com/auth/script.external_request and https://www.googleapis.com/auth/spreadsheets, an error of Unauthorized occurs.

    When above points are reflected to your situation, it becomes as follows.

    Usage:

    1. Prepare Web Apps side. (server side)

    In this case, I think that your settings can be used. Please redeploy the Web Apps as new version just in case. And please confirm the URL of Web Apps again.

    2. Prepare client side.

    1. Please access to https://script.google.com/macros/s/AKfycbxay75fTBt3doTyMFUPK0-GpK9hMZ4hVkYdiwYUBMhPfEN6hUJH/exec using own browser (user side.) And please authorize the scopes of Web Apps.

    2. I would like to propose to modify test4() of your script as follows.

       function test4() {
   var token = ScriptApp.getOAuthToken();
   const scriptURL='https://script.google.com/macros/s/AKfycbxay75fTBt3doTyMFUPK0-GpK9hMZ4hVkYdiwYUBMhPfEN6hUJH/exec';  
   var response = UrlFetchApp.fetch(scriptURL, {
     headers: {Authorization:'Bearer '+ token},
     method: 'GET',
 //    payload:'nothing',
     muteHttpExceptions: true
   });  
   Logger.log(response.getContentText())

   // DriveApp.getFiles()  // This is used for adding a scope of https://www.googleapis.com/auth/drive.readonly. This is used for requesting to Web Apps.
 }

    3. Please run test4(). By this, 0 is returned from Web Apps and you can see it at the log.

    References: