GAS - Authentication w/ UrlFetchApp - Form to Spreadsheet

1
votes

I am testing the functionality of UrlFetchApp and passing data from a Form and its Spreadsheet. I know it's possible to do this another way, however I am testing the functionality of UrlFetchApp (first time using it) within google scripts themselves, and want to get it to work with this method.

Here's the scenario I got, add a bound script to a Form App as so:

function makeRequest() 
{
  var webAppUrl = "https://script.google.com/macros/s/WebAppID/exec";

  var auth = ScriptApp.getOAuthToken();
  var header = { 'Authorization': 'Bearer ' +  auth };
  var options = { 'method':'post', 'headers':header };

  var resp = UrlFetchApp.fetch(webAppUrl, options);

  Logger.log(resp);
}

Add a bound script to the attached spreadsheet:

function doPost()
{  
  var ss = SpreadsheetApp.openById('ssID');
  var name = ss.getName();
  return ContentService.createTextOutput(name);
}

And then publish this second script attached to the sheet as a web app with only myself to have access.

Currently the above code does not work. The following error appears on the Form side of the script:

Request failed for https://script.google.com/macros/s/WebAppID/exec returned code 401. Truncated server response: Unauthorized

Unauthorized

Error 401

(use muteHttpExceptions option to examine full response) (line 12, file "Code")

Fails on the UrlFetchApp line of code.

However, if I remove the header option, then publish the web app for public use, this works just fine. Obviously this is not wanted.

What am I missing regarding authentication between scripts that I own?

Side Notes:

Anyone know why SpreadsheetApp.getActiveSheet() doesn't work when run in this fashion? That script is directly bound to a google sheet, so kind of odd.

2
google-apps-script

2 Answers

4
votes

Ok, found the answer to my own question. It was quite simple really. Needed to add the following scope to my project for accessing a spreadsheet:

https://www.googleapis.com/auth/drive

The easiest way I found to do this is to add a simple function like this and call it:

function authorizeDrive()
{
  var forScope = DriveApp.getRootFolder();
}

Doesn't need to return or do anything, just call any method from the DriveApp. Once run, it'll then popup with a dialogue for authorization. Don't even need to call this every time you do your main method calls. Don't even need to leave it coded in the script either. I wonder if there is way to just simple add the scope you need to a project from a properties window (I didn't find any). Or perhaps a way to pass a parameter along with UrlFetchApp regarding what scope need authorized.

Buy anyhow this still wasn't too bad.

Regarding my side note, I still haven't found a reason as to why SpeadsheetApp.getActiveSheet() returns null or undefined. I have to open by ID or URL, which is a pain. Especially since this is a container bound script. Also noticed that Logger.log() doesn't actually add anything to the Logger when run in this manner. If anyone could still shed some light on either of these, that would be great.

0
votes

You need to get the 'Spreadsheet' object first.

SpeadsheetApp.getActive().getActiveSheet()

However, if you are creating an add-on menu you can use 'SpreadsheetApp.getActiveSheet()'

function myFunction() {
  var lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
  var range = SpreadsheetApp.getActiveSheet().getRange(lastRow, 1, 1, 26);
  SpreadsheetApp.setActiveRange(range);
}

function onOpen(e) {
  SpreadsheetApp.getUi().createAddonMenu()
      .addItem('showLastRow', 'myFunction')
      .addToUi();
}