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

Can Google App Scripts create Web Apps with Multiple User Accounts?

I would like to create a web app that provides a dashboard visualisation of data from a Google spreadsheet.

From reading around it seems it's quite easy to do this, however, I would like the web app to serve multiple users without every user having access to every other's data. I've read around and I can't find a good answer on whether this is technically possible.

Ideally, I would supply a username and password to every user and they would use that to login on a login landing page. Once logged in, permissions assigned to their account would limit the data their dashboard is allowed to pull from the Google sheet.

How would I go about doing something like this?

Solution

  • From the second option is definitely the one I want, if you know of any resources or tutorials on how to do what you just said please put them in an answer, I believe your goal is as follows.

    • Spreadsheet is not shared with users, and the users access Web Apps as the owner of Web Apps. In this case, the users are not required to log in to their own Google accounts. The users input their own user name and password, and by the user name and password, users can see their own data on Web Apps.

    In order to explain this using a simple sample, how about the following sample?

    In this sample situation, by inputting the values of name and password, a specific value corresponding to the inputted name and password is shown. In this sample, Web Apps is used.

    Usage:

    1. Create a Google Apps Script project.

    In order to use Web Apps, please create a new Google Apps Script project.

    2. Sample script.

    Please copy and paste the following script to the script editor of the created Google Apps Script project.

    Google Apps Script side: Code.gs

    About url, please set your Web Apps URL after the Web Apps was deployed. Because the URL can be known when the Web Apps is deployed.

    const url = "https://script.google.com/macros/s/###/exec"; // Please set Web Apps URL.

// Please set user's name and passwork and the sheetname of the user.
// To use the sheet name is a sample situation for explaining this method.
const obj = [
  { name: "sampleName1", password: "samplePassword1", content: "sample content 1" },
  { name: "sampleName2", password: "samplePassword2", content: "sample content 2" },
  { name: "sampleName3", password: "samplePassword3", content: "sample content 3" },
  ,
  ,
  ,
];

function doGet(e) {
  const { name, pass, } = e.parameter;
  let error = false;
  if (name && pass) {
    const find = obj.find(f => f.name == name && f.password == pass);
    if (find) {
      const html = HtmlService.createTemplateFromFile("showData");

      // This is a sample value. Please modify this for your actual situation.
      html.data = find.content;

      return html.evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
    }
    error = true;
  }
  const html = HtmlService.createTemplateFromFile("login");
  html.url = url;
  html.error = error ? "Login error" : "";
  return html.evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

    HTML & Javascript side: login.html

    <p><?!= error ?></p>
<input type="text" id="name" placeholder="Please input login name.">
<input type="password" id="pass" placeholder="Please input login password.">
<input type="button" value="login" onclick="sample()">
<script>
function sample() {
  const url = "<?!= url ?>";
  const [name, pass] = ["name", "pass"].map(e => document.getElementById(e).value);
  window.open(`${url}?name=${name}&pass=${pass}`, '_top');
}
</script>

    HTML & Javascript side: showData.html

    <?!= data ?>

    3. Deploy Web Apps.

    The detailed information can be seen at the official document.

    Please set this using the new IDE of the script editor.

    1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
    2. Please click "Select type" -> "Web App".
    3. Please input the information about the Web App in the fields under "Deployment configuration".
    4. Please select "Me" for "Execute as".
      • This is the importance of this workaround.
    5. Please select "Anyone" for "Who has access".
    6. Please click "Deploy" button.
    7. Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.
    8. Please set your Web Apps URL to the value of url in the above sample script. And, please reflect the latest script in the Web Apps. By this, your script can be used. Please be careful about this.

    5. Testing:

    When you access your Web Apps URL like https://script.google.com/macros/s/###/exec using your browser, you can see the log in. When the above script is used, when you input sampleName1 and samplePassword1 are put to the name and password, respectively, you can see the content of sample content 1. This value is for the specific name and password.

    Note:

    • This is a simple sample script and situation for explaining my proposal. So, please modify this for your actual situation. If you want to ask about the value of html.data = find.content in your actual situation, I would like to recommend to ask as a new question. Because this answer is an explanation of my proposal from your reply of if you know of any resources or tutorials on how to do what you just said please put them in an answer. Please be careful about this.

    • When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.

    • You can see the detail of this in the report "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

    References: