Use Google Forms & Google Sheets as a Database

It turns out it is possible to scale to millions of users with Google Sheets as a database. While there's a line to be crossed somewhere, (probably before a million users) Google Forms and Google Sheets are a great way to get your project off the ground.

Introducing openform. Heavily inspired by (and designed to work with) opensheet, openform is a Free API for Google Forms.

Getting Started

1. Create a Google Form and add a few short response questions.
2. Make sure the form doesn't require emails by clicking the "Send" icon in the top right, and changing "Collect email addresses" to "Do not collect".
3. If you are in a Google Workspace account, you may need to let people outside your domain open your form.

Using the API

All you need to use openform is your spreadsheet ID. The openform API uses the following format:

You can find your form id in the public url of your Google Form. The url should end in viewform.

That's it! You can now use the API to read and write to your Google Form.

Getting Form Information

You can get a JSON representation of your form by making a GET request to the API. You'll need to do this at least once to get the question IDs for your form. These IDs are necessary to write to your form.

Here's an example using Javascript's fetch function:

fetch('https://openform.eliot.sh/your_id')
  .then(response => response.json())
  .then(data => console.log(data))

Here's an example of what the response looks like:

{
  "title": "openform Demo",
  "description": null,
  "collectEmails": "NONE",
  "questions": [{
   	"title": "Name",
   	"description": null,
   	"type": "TEXT",
   	"options": [],
   	"required": false,
   	"id": 1842043344
  }, {
  	"title": "Favorite Food",
  	"description": null,
  	"type": "TEXT",
  	"options": [],
  	"required": false,
  	"id": 1985287416
  }],
  "error": false
}

Writing to your Form

Using the IDs from the previous step, you can write to your form by making a POST request to the API. The body of the request should be a JSON object with the question IDs as keys and the answers as values.

Here's another example with Javascript:

fetch('https://openform.eliot.sh/your_id', {
  method: 'POST',
  body: JSON.stringify({
    1842043344: 'Eliot',
    1985287416: 'Pizza'
  })
})
  .then(response => response.json())
  .then(data => console.log(data))

Notice how we used the question IDs from the previous step as keys in the JSON object.

Accessing Responses

openform is designed to be used in tandem with opensheet, an excellent free API for getting data Google Sheets. Simply link a Google Sheet to your form, and opensheet will handle the rest. Google Sheets also opens the door to automation with Google Apps Script, which is great for processing data after submission!

To learn more about opensheet, check out this great tutorial by it's creator Ben.

Conclusion

More in-depth documentation for openform is available on GitHub.

If you have any questions, don't hesitate to reach out through the homepage of this website, or email me at eliot <at> hertenste <dot> in.

If you've found this project useful, consider sponsoring me on GitHub. It helps me keep projects like this one free and open source.