There’s a really basic thing missing from Google Forms. What’s missing ? A way of stamping responses with some sort of code to be able to link them back to some user or some other fixed data not in the form. There are many questions on StackOverflow on this topic and very few solutions.

Let’s look at a workaround these missing Forms features by using Firebase to track a journey through Google Forms.

This article will touch on the beauty of Firebase rules, installed triggers and the Apps Script FormsApp service, and how to easily read and write to Firebase from Apps Script.

Page Content hide

1.4 Prefilled email address

1.4.1 Hidden or readonly fields

1.5 Other ideas

1.6 So what to do?

1.6.1 Potential ideas

1.7 Solution

1.7.1 Apply via the App

1.7.2 Apps Script Form onSubmit

1.7.3 The responseUrl

1.7.4 What if the respondent changes their email address in the form

1.7.5 What if the respondent changes their email to impersonate someone else

1.7.6 The email digest

1.8 The Firebase rules

1.8.1 Defining admins

1.8.2 Reading

1.8.3 Writing

1.9 OnSubmit code

1.10 Creating the trigger

1.11 Writing and reading Firebase

1.12 Next

1.13 Links

1.14.2 Using Firebase and Apps Script to link Google Forms reponses

1.14.3 Using a service account with Firebase in NodeJs

1.14.4 Firebase service accounts on Node.js

1.14.5 Firebase auth for graphql clients

1.14.6 Firebase JSON REST access library for Apps Script

Motivation

We have an App for a charitable foundation that runs competitions where funding awards are given for research projects which feature innovative ideas to improve our industry.

We need to detect that someone has already submitted an application, and allow them to edit it if they want.

Straightforward requirement, right?

Background

We run a number of these competitions, the submissions are judged by a panel, each of whom need to have access to the detail of each submission, and the questionairre to collect detail about the submissions are created and managed by a couple of (non developer) administrators.

So Google Forms, with a spreadsheet to collect the data is ideal as they can do all that without any need for intervention by a developer or a DBA.

The journey

In order to limit submissions to qualified applicants, they have to first register with our App to get access to the form. An unregistered user starts here with a timeline, and an invitation to register or login before applying.

They can now apply, which directs them to the appropriate Google Form for the competition.

The application page now looks like this, with a link to edit the already submitted form. The fundamental requirement is that the Google Form responseUrl – available at submission time of the completed form – becomes associated with and available to, the authenticated App user only.

Authentication

The App is using Firebase authentication, so we already have a unique Firebase user id for each user (along with the email address associated with their Firebase uid). We can use a Google Form prefilled Url to the form to initially populate the form with this email address and some other data.

Prefilled email address

Now we hit the first problem with Google forms (and surely a very simple enhancement). The email address (or anything else we prefill) can be changed by the user, so there’s a risk that we lose the connection between the App (the uid via the email address) and the form.

Hidden or readonly fields

There is (seriously) no such thing as a hidden or readonly field in a Google Form. This could so easily be solved by Google Forms implemented some or all of these approaches.

A hidden field type that can be passed via a prefilled url.
Prefilled fields that are optionally sticky even if the question for which it’s a reponse is never visited. If this was available, you could use Forms routing to create a question that was never visited and could therefore never be changed. The behavior of Forms today though, is to set unvisited fields to blank, even if they have a prefilled value.
A ‘readonly’ field than cannot be modified in the form
A redirect url on submission.

But it doesn’t have any of things, so the respondent can change anything they want thus losing the connection to the user authenticated in the App.

Yet, none of these ehnacements appear difficult or complex to implement!

Other ideas

Forms provides various ways of picking up ids, but each one seems designed to frustrate this very simple traceability requirement

You can insist that the respondent provides an email. But they can provide anything they like.
Mandatory emails cannot be prefilled. (Seriously?)
You can insist on a google login to the form, but not everyone has a google login. They could make one specially, but it might not be the one they register with our app with (so no connection again), and if they do make one just for the purposes of the form, they probably won’t look at it much – yet it’s the one we’d want to use to communicate their application progress through.

So what to do?

Potential ideas

Access the form via the form API or the spreadsheet via the spreadsheet API from the App to see if we can find their reponse and responseUrl for editing – but then we get into showing scary messages to get authorization in the App, and we still don’t guarantee to be able to connect the App to the form response.
Add a watch to the email question via the form API, and create a pubsub message to some cloud function to do something when the email question is triggered. Life is too short for that.
Rewrite the google form as part of the webapp (you could still use the forms API to submit the response). Quite a few people on Stackoverflow have resorted to this, but one of my design objectives was no development in the form process so the admin team could continue to work autonomously in tools they were familiar with.

Solution

Since I’m using Firebase anyway, i figured I could use the realtime database to record when an application was made and Apps Script onSubmit event to update that database item when the form was submitted to record the responseUrl.

I won’t be showing the code from the App (a standard web app using Vue, Vuex, Vuetify and firebase client) in this article, but I’ll go through the Apps Script code and the firebase data structure.

Apply via the App

The Apply button in the App writes this to Firebase – the writer’s credentials is the authenticated firebase user, and the prefilled email address passed to the google form is the firebase user email.

It is keyed on …/formid/emaildigest/firebaseuid/{the application object}, where emaildigest is derived from the logged on users email (the same as is passed as a prefill to the form), and firebaseuid is the unique user id of the authenticated user. This uid is the one we’d love to be able to pass secretly to Forms, but there’s no way to do it.

Apps Script Form onSubmit

Submitting the form triggers an Apps script event handler which finds the submission record associated with the email digested from what the user submitted. It doesn’t know the Firebase uid, and only knows the current value of the email response in the form, so it retrieves any application items associated with this email digest.

Here’s what the onSubmit handler writes. Note that the Firebase writer is the owner of the Google Form (not the user) since onSubmit is an installed Trigger. The owner of the Google form is the same as the owner of the Firebase realtime database – they share the same cloud project.

The responseUrl

This is recorded in the application object, written back, preserving the uid as the key it discovers keyed on the email in the form. A key characteristic of the responseUrl is that it remains constant, no matter how many times the form responses are edited. We can use this!

What if the respondent changes their email address in the form

In this case, the onSubmit won’t find the firebase entry, and therefore can’t assign a responseUrl, and the App won’t be able to offer up a link to edit the form associated with its authenticated user.

What if the respondent changes their email to impersonate someone else

This is a trickier case, because it can’t know that the email has been changed – so it writes the responseUrl against the changed email address key, again preserving whatever uid it found. You might think this will mean that the app would present the genuine owner of the email address with the wrong response url, but we’ll see later how to prevent that.

The email digest

Since Firebase cannot have a property containing some special characters, you can’t use the email address as a property name. Emails addresses are compressed and digested to create a valid Firebase key. Although I’m using email address, you could use any unique value/id that the App knows, and can be prefilled on the form – whatever it is though, the user could potentially change it in the form.

On Apps Script I’m using the bmLZString library to digest the string, and in the App I’m using ls-ztring node module.

 const digester = (str) => bmLZString.LZString.compressToEncodedURIComponent(str)

digesting the email address

The Firebase rules

The key to ensuring that the app wires up the responseUrl matching the logged in user is the Firebase rules. I’ve omitted the firebase base url here, and only show the rules from the interesting part of the path.

{
  "rules": {
   ....,

              "$formid": {
                "$emailDigest": {
                  "$emailuid": {
                    ".read": "auth.uid === $emailuid || root.child('admins').child(auth.uid).exists()",
                    ".write": "(auth.uid === $emailuid && auth.token.email===newData.child('email').val()) || 
                    (root.child('admins').child(auth.uid).exists() && newData.exists() && !data.exists())",
                  }
                }
			  }
  }
}

firebase rules

The firebase entry can be accessed by either

An admin – the owner of the Apps Script onSubmit function
An authenticated user – whoever is logged into the App

Defining admins

The simplest way is to make a table of the uids of admins. In my case I only have 1.

  "admins" : {
    "a7xxxxxxxxS2" : true
  },

admins uid

In the database rules section, we can check if the accessing user is an admin like this

root.child('admins').child(auth.uid).exists()

checking if the authenticated user is an admin

Reading

So this means that the item can be read by only the admin, or where the uid matches the authenticated user.

 "$emailDigest": {
   "$emailuid": {
       ".read": "auth.uid === $emailuid || root.child('admins').child(auth.uid).exists()",

reading

Writing

This rule says that only admins and authenticated users can write.

If it’s an authenticated user, then the item they are writing must match the email address they are logged on with.
If it’s an admin, they can neither create nor delete an item – only update

"$emailDigest": {
  "$emailuid": {
   ".write": "(auth.uid === $emailuid && auth.token.email===newData.child('email').val()) || 
                    (root.child('admins').child(auth.uid).exists() && newData.exists() && !data.exists())",

writing

These rules mean that even if someone modifies their form to impersonate another’s email, only the original user will be able to see their own version of the responseUrl.

OnSubmit code

This is code that will run as the user finally submits the form as an installed trigger, container bound to the Form you are tracking. This user is the same one as the admin defined in the firebase rules.

There are 3 states to look out for

The digest key references an item without a responseUrl – this means it’s the first submission. The entry is updated with the responseUrl for this set of responses, forever associating them with this digest key. If the email is not the one they registered with, it won’t be visible to them from the App thus preventing access to the wrong reponseUrl.
The digest key references an item with a reponseUrl (and a fiebase uid). This means it’s an edit of existing responses. The responseUrl in the firebase item must match the one for this set of responses otherwise it’s ignored. It means that someone has used the email address in their form of someone else
There is no matching firebase item. The form is being used directly rather than via the app (not the normal workflow). Ignore.


function registerSubmit(e) {
  sendPack(e)
}


const sendPack = ({ response, source }) => {

  const getBase = () => PropertiesService.getUserProperties().getProperty('firebasebase')
  const digester = (str) => bmLZString.LZString.compressToEncodedURIComponent(str)
  const getPublishedId = (form) => form.getPublishedUrl().replace(/.*\/([^\/] )\/viewform/, "$1")
  const findResponse = (itemResponses, title) => itemResponses.find(ir => ir.getItem().getTitle() === title)
  const itemResponses = response.getItemResponses()
  const getMriiRef = (form, email) => `${getBase()}/${getPublishedId(form)}/${digester(email)}`
  
  const data = [{
    title: 'Email Address',
    name: 'email'
  }, {
    title: 'Name',
    name: 'displayName'
  }].reduce((p, packItem) => {
    const fr = findResponse(itemResponses, packItem.title)
    if (!fr) console.error('Couldnt find ', packItem, ' in form')
    p[packItem.name] = fr && fr.getResponse() || ''
    return p
  }, {
    submissionTime: response.getTimestamp(),
    responseUrl: response.getEditResponseUrl()
  })

  const url = getMriiRef(source, data.email)
  const currentData = getFirebase(url)

  const uids = Object.keys(currentData || {})
  if (!currentData) {
    // this could happen if form was completed outside the website
    console.log(`no submission data found for ${url}`)
  } else if (!uids.length) {
    // this shouldnt ever happencon
    console.error(`found entry for ${url} with no content`)
  } else {

    if (uids.length > 1) {
      // if this happens, more than 1 uid is claiming the email - however the App will only let the real owner have it
      // so we can still go ahead
      console.error(`found multiple entries for ${url} ${uids.join(",")}`)
    }
    // so which in current data do we use?
    // only matching reponseurl's or no reponseurls are allowed
    const uid = uids.find(k => currentData[k].responseUrl === data.responseUrl) || uids.find(k => !currentData[k].responseUrl)
    if (!uid) {
      console.error(`couldnt find matching reponseUrl ${data.responseUrl},${url}:${uids.join(",")}`)
    } else {
      const responseUrl = currentData.responseUrl || data.responseUrl
      const newData = {
        ...currentData[uid],
        ...data,
        responseUrl
      }
      setFirebase(`${url}/${uid}`, newData)
    }
  }

}
const findResponse = (itemResponses, title) => itemResponses.find(ir => ir.getItem().getTitle() === title)

onSubmit

Creating the trigger

First you need to set up and install a trigger to run whenever someone submits their responses. You can do this via the IDE, but it’s more accurately repeatable (and self documenting) if you just simply use a one off script to create the trigger.

const createTriggers = () => {
  cleanTriggers()
  var form = FormApp.getActiveForm();
  ScriptApp.newTrigger('registerSubmit')
    .forForm(form)
    .onFormSubmit()
    .create();
}

const cleanTriggers = () =>
  ScriptApp.getScriptTriggers().forEach(trigger => ScriptApp.deleteTrigger(trigger))

creating trigger

As an aside, another hole in FormsApp is the omission of an onOpen trigger when the user opens the form to complete it.

There is an onOpen trigger, but it only fires when someone opens the form for editing. This catches many who (quite rightly) imagine that an onOpen trigger would be far more useful if it fired when a respondent opens a form rather than opening it for editing.

If a trigger like this existed, we could tie the form more accurately and more easily to the submitter. The timestamp would more or less tally with the redirection from the App as a clue.

You could probably do this nowadays thanks to the new Forms API, by adding a watch, and triggering a cloud function to reconcile.

That’s for another day – watch this space if you are interested in following along.

Writing and reading Firebase

Firebase is easily accessible via its REST endpoint. First of all though, you’ll need to add some scope sto your appscript.json manifest file.

  "oauthScopes": [
    "https://www.googleapis.com/auth/userinfo.email",
    "https://www.googleapis.com/auth/firebase.database",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/script.scriptapp",
    "https://www.googleapis.com/auth/forms"
  ]

firebase and form scopes

And the end point which I’m storing in the user property service as a one off exercise

const oneOff = () => {
  PropertiesService.getUserProperties().setProperty('firebasebase', 'https://xxxx.firebaseio.com/x.../x.../x.../x...')

}

fiebase endpoint

And here are some useful functions to read and write to firebase



  const getFirebaseHeaders = () => ({
    "Content-type": "application/json",
    "Authorization": "Bearer "   ScriptApp.getOAuthToken()
  })
  const getFirebaseOptions = () => ({
    muteHttpExceptions: true,
    headers: getFirebaseHeaders()
  })

  const pokeFirebase = (url, extraOptions = {}) => {
    const options = {
      method: "GET",
      ...getFirebaseOptions(),
      ...extraOptions
    }
    const response = UrlFetchApp.fetch(url   ".json", options)
    if (response.getResponseCode() !== 200) {
      console.error('failed', url, response.getContentText())
      return null
    } else {
      return JSON.parse(response.getContentText())
    }
  }

  const setFirebase = (url, payload) => {
    return pokeFirebase(url, {
      method: "PUT",
      payload: JSON.stringify(payload)
    })
  }

  const getFirebase = (url) => {
    return pokeFirebase(url)
  }