Sheets

Display user specific spreadsheet data on a Web App

Allow users to only view their data from a spreadsheet with the help of a Google Apps Script web app.

Sourabh Choraria

13 Dec 2019 • 6 min read

Display user specific spreadsheet data on a Web App

here's how to allow users to only view data related to their email ID - a good use-case would be that for teachers wanting students to view only their status updates / reports & not anyone else's.

disclaimer: this setup involves making your "master" spreadsheet publicly available (or domain-wide, if on G Suite); albeit, ensuring that the data is not viewable by non-owners/editors

problem statement

coincidentally, i stumbled upon scenarios that seemed quite similar (at least to me) & were reported just a day apart from one another (also, on 2 totally different platforms) but more importantly, happened while i was on a break from work (still am, as of when i'm writing this post 😛) which allowed me to work on something that is a real-world problem and provide a solution that real-humans would use (hopefully) -

'mike feldman' on twitter wanted kids to be able to track their "standards" & not see everyone else's
'diana t' over at stack overflow wanted to create a report card for students - again, this was whilst not wanting students to access other's data

the similarities here were that the data was being stored in a spreadsheet and the limitations were that of sharing access with everyone involved (instructors/teachers & students).

solution

let me start by saying that this is going to be fairly convoluted as i couldn't think of a more easier way to achieve what ought to be done.

while i'd originally proposed 2 plausible ways to achieve what was required (1st was to build an add-on & the 2nd was a web-app) - i'd later understood why a web-app would be preferred over an add-on.

architecture

the structure to implementing a web-app are as follows -

preparing the sheet to display exactly what ought to be displayed to users over a web-app (similar to the header image from this post)
- if this requires us to create a new sheet and importing only certain ranges (in a specific order), we should rather get that done on a spreadsheet itself as opposed to handling it programatically (via apps script)
- writing code takes more effort and maintenance as opposed to building simple IMPORTRANGE & QUERY formulae
very carefully setting up public sharing option (on the sheet) & also deploying the web-app to be accessed in a very specific manner (full details have been listed under setup)
- what this does is disable non-owners/editors to either view or download the overall master data

codebase

you can make a copy of my script here or use the code from below -

the backend - Code.gs:

var sheetID = 'sheet-ID-goes-here'; // enter the Spreadsheet ID
var dataSheet = 'Demo'; // enter the name of the sheet that contains ALL user data
var emailHeader = 'Email'; // replace this with the header name (per Row 1) where user emails exist

/* ========== DO NOT EDIT BELOW THIS LINE ========== */

var activeUser = Session.getActiveUser();
var ss = SpreadsheetApp.openById(sheetID);

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Index').setTitle('Display Specific User Data');
}

function currentUser() {
  if (activeUser !== '') {
    return activeUser.getEmail();
  } else {
    return "Couldn't detect user!!!";
  }
}

function getData() {
  var sheetName = dataSheet;
  var activeSheet = ss.getSheetByName(sheetName);
  var values = activeSheet.getDataRange().getValues();
  var header = values[0];
  var emailIndex = header.indexOf(emailHeader);
  var userData = [];
  for (var i = 0; i < values.length; i++) {
    if (values[i][emailIndex] == activeUser) {
      userData.push(values[i]);
    }
  }
  if (userData.length > 0) {
    var tableStart = '\n<table class="centered responsive-table striped">';
    var tableHead = '\n<thead>\n<tr>';
    for (var j = 0; j < header.length; j++) {
      tableHead = tableHead + '\n<th>' + header[j] + '</th>';
    }
    tableHead = tableHead + '\n</tr>\n</thead>';
    var tableBody = '\n<tbody>';
    for (var k = 0; k < userData.length; k++) {
      tableBody = tableBody + '\n<tr>';
      for (var l = 0; l < userData[0].length; l++) {
        tableBody = tableBody + '\n<td>' + userData[k][l] + '</td>';
      }
      tableBody = tableBody + '\n</tr>\n';
    }
    var tableEnd = '</tbody>\n</table>';
    var tableHtml = tableStart + tableHead + tableBody + tableEnd;
    return tableHtml;
  } else {
    return '<table class="centered responsive-table striped"><tbody><tr><td>No data found.</td></tr></tbody></table>';
  }
}

code.gs

the frontend - Index.html:

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <base target="_top">
    <link href="https://fonts.googleapis.com/css?family=Montserrat&display=swap" rel="stylesheet">
    <style>
        body {
            font-family: 'Montserrat', sans-serif;
        }
    </style>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>
    <script>
        window.onload = function() {
            google.script.run.withSuccessHandler(function(user) {
                document.getElementById("currentUser").innerHTML = 'Current User: ' + user;
            }).currentUser();

            google.script.run.withSuccessHandler(function(userData) {
                document.getElementById("dataTable").innerHTML = userData;
            }).getData();
        }
    </script>
</head>
<body>
    <div class="container center">
        <div class="row">
            <div class="col s12">
                <h4 class="light"><br /></h4>
                <h4 class="light" id='currentUser'></h4>
            </div>
        </div>
        <div class="row">
            <div class="col s12">
                <div id='dataTable'>
                </div>
            </div>
        </div>
    </div>
</body>
</html>

index.html

setup

for sheet and script owners (makers)

prepare the spreadsheet with all the necessary data
create a dummy sheet (within the same spreadsheet) & hide the "master" sheet
- by doing this, folks would not be able to unhide the sheet (post implementing steps 3 & 4) & you can verify the same on my dummy sheet here
setup link sharing where folks only get to have "view" access - & based on your preference, either select the sharing to be accessible by folks outside your domain or within

while you're at it, check the options to disallow folks to download the sheet or change access (these would be unchecked by default)

next, head over to the apps script and set it up by replacing all the required variables (refer codebase for the actual code)
finally, go to Publish > Deploy as web app... and choose:
- Project version: New
- Execute the app as: User accessing the web app
- Who has access to the app: Anyone (or Anyone within your domain - based on your preference)

for end-users accessing the web-app

given that the web-app is configured to run as the user accessing it, the link would ask for certain permissions whilst throwing "dangerous" warnings - do not fear, its all part of the process and the google apps script web app setup experience 😌

here's what you'd be expected to see on first use (only) & ought to do in order to access the web app -

press the review permissions button > login using your desired gmail / g suite account
click on advanced from the next pop-up and then on "go to <whatever would be the app name> (unsafe)"
finally, click on allow button for the web app to start showing data

read this if you're still unsure about allowing access to the web app via this setup.