in Google Apps Script web app, getting redirect_uri_mismatch, but I may not even be on the right track

Question

I'm trying to build a google apps script that handles two spreadsheets concurrently - one contains private information for users; the other is public.

Because there's private information stored in it, I need to ensure that only the user has access to it. What I ultimately decided on (which may not be the best way to handle this) was to create a folder and file in the user's own google drive to store the data. I handle that inside my code.gs, and it works perfectly.

The problem comes when I try to access that data through my web app (which uses the same code.gs file). Even though users had to authorize my app to access their data when it first came up and tried to go make the file, the driveapp and spreadsheetapp authorizations don't seem to count for the web app. So, even though I have the fileId and url, I can't access it. I get the following error:

ACCESS_DENIED This spreadsheet is not publicly viewable and requires an OAuth credential

Am I correct in assuming that if I go in and build an OAuth credential using a clientID, that only works on MY spreadsheets, not my USER's spreadsheets? When I borrowed from Google's own page about spreadsheet authorization, https://developers.google.com/chart/interactive/docs/spreadsheets#Authorization, and built my app with only that (swapping in the urls and clientIDs as appropriate), I got this error:

That’s an error.

Error: redirect_uri_mismatch

The JavaScript origin in the request, https://[gobblydygook1]-script.googleusercontent.com, does not match the ones authorized for the OAuth client. Visit https://console.developers.google.com/apis/credentials/oauthclient/[validClientID].apps.googleusercontent.com?project=901868773794 to update the authorized JavaScript origins.

Learn more Request Details
response_type=permission id_token
scope=https://www.googleapis.com/auth/spreadsheets
openid.realm=
redirect_uri=storagerelay://https/[gobblydygook1]-script.googleusercontent.com?id=auth590482
client_id=[validClientID].apps.googleusercontent.com
ss_domain=https://[gobblydygook1]-script.googleusercontent.com
gsiwebsdk=shim
That’s all we know.

Unfortunately, I don't have my own domain... when I try to put in the actual website of my script, or the links that the error message provides to me, in the Credentials page, it doesn't seem to take them (or maybe it does and never shows them to me?).

All that said, I'm not even sure I'm doing this right in the first place, given that I'm trying to access a spreadsheet I created in my user's folder.

Can my code.gs somehow pass the authorization it used to get into that file over to the browser?

I could have code.gs go collect my data, but... doing so seems to be a few orders of magnitude slower than visualization queries.

Here's the code, in case you want to see it:

index.html:

<!DOCTYPE html>
<html>
  <head>
  <?var userSheetURL = getUserSheetUrl();?>
  </head>
  <body>
    <h1>Hello, World!</h1>
    <h3>User sheet URL is <?=userSheetURL?></h3>
    <div id="user_sheet_url" data-url="<?=userSheetURL?>"></div>
    <?!= include('demo'); ?>
    <button id="authorize-button" style="visibility: hidden">Authorize</button>
    <script src="https://apis.google.com/js/auth.js?onload=init"></script>  
  </body>
</html>

demo.html:

<link href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" rel="stylesheet"/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">

  var clientId = '[validClientId].apps.googleusercontent.com';
  var scopes = 'https://www.googleapis.com/auth/spreadsheets';

  function init() {
    gapi.auth.authorize(
        {client_id: clientId, scope: scopes, immediate: false},
        handleAuthResult);
  }

  function handleAuthResult(authResult) {
    var authorizeButton = document.getElementById('authorize-button');
    if (authResult && !authResult.error) {
      authorizeButton.style.visibility = 'hidden';
      makeApiCall();
    } else {
      authorizeButton.style.visibility = '';
      authorizeButton.onclick = handleAuthClick;
    }
  }

  function handleAuthClick(event) {
    gapi.auth.authorize(
        {client_id: clientId, scope: scopes, immediate: false},
        handleAuthResult);
    return false;
  }

  function makeApiCall() {
    var userUrl = $('#user_sheet_url').attr('data-url');
    //the following alert is never triggered
    alert(userUrl);
    var tqUrl = userUrl + 'gviz/tq' +
        '?tqx=responseHandler:handleTqResponse' +
        '&access_token=' + encodeURIComponent(gapi.auth.getToken().access_token);
    var slashScript = "/script"
    document.write('<script src="' + tqUrl +'" type="text/javascript"><' + slashScript + '>');
  }

  function handleTqResponse(resp) {
    document.write(JSON.stringify(resp));
  }
</script>

(the call to getUserSheetUrl calls a function in code.gs that returns the url of the user's file I created. I can see that's returning successfully the string I want in the browser window)

Can I ask you about your question? 1. About I'm trying to access a spreadsheet I created in my user's folder., you want to access to a Spreadsheet in other user's Google Drive. Is my understanding correct? 2. Where are the scripts of index.html and demo.html put? 3. What is getUserSheetUrl()? — Tanaike
1. yes. 2. I don't know how the script editor stores things, but they're all in my project, the same project that has the code.gs that creates those files. 3. It just searches the other user's folder for the file with a specified name, and returns the url by combining the google path with the fileId. — TulsaNewbie
Thank you for replying. From your replying, you want to access a Spreadsheet in user's Google Drive using the scripts putting in your own Google Drive. I could understand like this. If my understanding is correct, unfortunately, you cannot directly access to the Spreadsheet in user's Drive even if the access token retrieved with the oauth2 process is used. As a workaround, when the Spreadsheet is shared with you, you can access to it using the access token. At that time, you can retrieve the access token by ScriptApp.getOAuthToken(). If I misunderstood your situation, I apologize. — Tanaike
hmmm... then perhaps I can activate sharing with me programmatically somehow? I'll look into that. In the mean time, I've simply put a code segment in to retrieve it from code.gs, and that's working fine. — TulsaNewbie
If You solved this issue, feel free to post Your own answer and mark it as the correct one. After all, I feel this is an interesting topic. — deblocker

TulsaNewbie TulsaNewbie · Accepted Answer · 2019-06-25T20:51:59

While I'm still sure there is a way to navigate the authentication in a more direct manner, I came up with the following workaround:

In my code.gs, I already had the following segment to initialize:

function initializeUserFile() {
  var folders = DriveApp.getFoldersByName(userFolderName);
  if(!folders.hasNext()) {
    //Folder does not exist in user's drive, so create it
    userFolder = DriveApp.createFolder(userFolderName);
  } else {
    //Folder does exist, so get it
    userFolder = folders.next();
  }
  var files = userFolder.getFilesByName(userFileName);
  var file;
  var fileId;
  if(files.hasNext()) {
    //File exists in user's folder, so get it
    file = files.next();
  } else {
    //File does not exist in user's folder.  Check root.
    files = DriveApp.getFilesByName(userFileName);
    if(!files.hasNext()) {
      //file does not exist
      fileId = SpreadsheetApp.create(userFileName).getId();
      file = DriveApp.getFileById(fileId);
    } else {
      //file is in root
      file = files.next();
    }
    //file is in root, whether because we found it or because we had to create it.  
    //so move it to the folder we need it in
    userFolder.addFile(file);
    DriveApp.getRootFolder().removeFile(file);
  }
  userFileId = file.getId();
  userSpreadsheet = SpreadsheetApp.openById(userFileId);
  initializeSheet(userInfoSheet, userInfoSheetName, userSpreadsheet);  
  initializeSheet(userCrewSheet, userCrewSheetName, userSpreadsheet);
  initializeSheet(userBonusSheet, userBonusSheetName, userSpreadsheet);
  removeSheet(userSpreadsheet, "Sheet1");
  Logger.log("spreadsheet = " + file.getName());
  Logger.log("folder = " + userFolder.getName());
}

function initializeSheet(sheet,sheetName,targetSheet) {
  if(!targetSheet) {
    targetSheet = activeSpreadsheet;
  }
  sheet = targetSheet.getSheetByName(sheetName);
  //The following only fires if the sheet doesn't exist
  if(sheet == null) { 
    sheet = targetSheet.insertSheet();
    sheet.setName(sheetName);
    setHeaders(sheet,sheetName);
  }
}

I'll skip listing setHeaders, as it's long... basically just decides what page it's on and creates the appropriate first row (headings) for that page.

So when a new user accesses the app, this routine fires and creates the file and folder. If an existing user accesses it, it basically just initializes important variables, like userFolder, userFileId, and userSpreadsheet.

The only one important for the next part is userSpreadsheet:

function getUserCrewTable(sheetName) {
  initializeUserFile();
  var sheet = userSpreadsheet.getSheetByName(sheetName);
  var range = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
  return range.getValues();
}

From inside my web app, I can call google.script.run.getUserCrewTable() and get an array containing everything on the page. I call InitializeUserFile again because it will have forgotten the variables in the mean time... that gives me userSpreadsheet targeting the user's spreadsheet. I can pass the specific sheet I'm looking for (since I have two), and get back the table. I call it as follows:

google.script.run
            .withSuccessHandler(handleUserQueryResponse)
            .withFailureHandler(failureHandler)
            .getUserCrewTable("my_crew");

function handleUserQueryResponse(response) {
  userData = google.visualization.arrayToDataTable(response);
}

that userData is now a dataTable, and can be used anywhere a dataTable can be used.

in Google Apps Script web app, getting redirect_uri_mismatch, but I may not even be on the right track

1 Answers