Create a Google Site with a section requiring authentication with Forms, Sheets & App Script

If you Google “how to add login to a Google Site” you get many hits for questions and few answers. I wondered if we could somehow create a Google Site with access control for a “member’s area”. Turns out, you can’t really do that. We can, however, simulate that behavior by creating two sites, where one contains the “public pages” and another one the “private pages”.

First, we create a site that we want to be public. Here’s an example of a published Google Site: We publish this one as visible for everyone.

Publish dialog (in Norwegian) – showing access for all for our public page.

Now, create a folder to contain another site, which will be your membership site.

Membership site in its own folder

This one is published only to “specific persons”. These people will need to have Google accounts to access the membership site. Now, we can add new users by using the sharing functions in Google Drive, but we want it to be a bit more automated. This is the reason we put the site in its own folder.

Google used to have a version of Google Sites that could be scripted, but that version is now considered legacy. There is a new version of Google Sites that we are using but that one cannot be scripted. However, we can manipulate permissions for a folder in Google Drive using App Script, and permissions are hierarchical.

Let’s assume our users have received an access code that grants them access to the site, for example in a purchase process. We can create a Google Form for people to get access after obtaining this code. They will have to add their Gmail address + the secret access code.

Signup form for our membership page (also in Norwegian, tilgangskode = access code)

When the user submits this form, we need to process it and grant access to the parent folder of our membership site. First, make sure you go into the answers section, and click the “Google Sheet” symbol to create a Sheet for containing the signups.

Click the green Sheet symbol to create a Google Sheet to hold signup answers.

In Sheets, we create two extra tabs – one for the “secret access code”, and another for a block list in case there are users we want to deny signing up. To make the data from the spreadsheet accessible to Google App Script, we now click the Google App Script link on the Extensions menu in the Google Sheet.

Go to App Script!

In App Script, we add the Sheets + Gmail services. We create a function to be triggered each time the form is submitted. Our function is called onFormSubmit. We click the “trigger button” in the editor to add a trigger to make this happen.

Trigger button looks like a clock!

In the trigger config, choose trigger source to be “spreadsheet”, and event type to “form submission”. Now the trigger is done, and it is time to write some code. In pseudocode form, this is what needs to happen:

  • Get the e-mail address of the new user + the access code submitted by the user
  • Check if the access code is correct
    • If correct, check if the user is on a block list. If the user is not blocked, grant access to the parent folder of the membership site using the Google Drive service. Send an e-mail to the user that access has been granted.
    • If incorrect, don’t grant access. Perhaps log the event

The App Script code is just JavaScript. Here’s our implementation.

function onFormSubmit(event) {
  var useremail = event.values[1]
  var accesscode = event.values[2]
  var correctcode = sheetTest()
  var blocked = getBlockedUsers()
  if (accesscode == correctcode) {
    if (blocked.flat().includes(useremail)) {
      Logger.log("Blocked user is attempting access: " + useremail)
    } else {
      Logger.log("Provide user with access: " + useremail)
      var secretfolder = DriveApp.getFolderById("<id-of-folder>")
      GmailApp.sendEmail(useremail, "Access to Membership Site", "Body text for email goes here...")
  } else {
    Logger.log("Deny user access")

function sheetTest() {
  var sheet = SpreadsheetApp.openById("<Spreadsheet-ID>");
  var thiscell = sheet.getSheets()[1].getDataRange().getValues()
  var correctcode = thiscell[thiscell.length-1][0];
  return correctcode

function getBlockedUsers() {
  var sheet = SpreadsheetApp.openById("<Spreadsheet-ID>");
  var blocklist = sheet.getSheets()[2].getDataRange().getValues()
  return blocklist

Now, we are done with the “backend”. We can now create links in the navigation menus between the public site and the membership one, and you will have a functioning Google Site with a membership area as seen from the user’s point of view.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s