Creating a Form Widget that Saves the Input to a Google Sheet

In this tutorial, we are going to implement a simple newsletter subscription form widget that saves the submitted form data to a third-party service.

If you aren’t familiar with creating widgets for a Scrivito-based web application, we recommend to go through Adding an Option to a Widget first.

Now, this is what our form is going to look like. We’ll define the widget’s model class and its editing configuration to allow editors to customize the button text as well as the confirmation text to be displayed after submitting the form.

Next, we’ll provide and discuss the form component. Form rendering always needs to take account of state and handle events to keep the user informed, so this part is about user friendliness, too.

Last but not least, we’ll outline how the form data can be saved to a Google spreadsheet (or any storage service accessible through a URL) – as a quick and minimalist approach to handling registrations, subscriptions and the like on a small scale.

Create the model class

We’ll name our widget “SubscriptionFormWidget” and equip it with just two attributes:

  • The buttonText allows you to set just that.
  • The confirmationText is displayed after successfully submitting the form.

Here’s the model class definition – there’s not much to it:

Copy
// src/Widgets/SubscriptionFormWidget/SubscriptionFormWidgetClass.js

import * as Scrivito from 'scrivito';
Scrivito.provideWidgetClass('SubscriptionFormWidget', {
  attributes: {
    buttonText: 'string',
    confirmationText: 'string',
  },
});

Make the attributes editable

We want editors to always be able to provide the button and the confirmation texts, independently of the state the form is in. So let’s provide the configuration required for editing the texts on the widget’s properties dialog. As you might remember from other tutorials, the configuration also takes care of how the widget is presented in the widget selection dialog.

Copy
// src/Widgets/SubscriptionFormWidget/SubscriptionFormWidgetEditingConfig.js

import * as Scrivito from 'scrivito';
import buttonWidgetIcon from '../../assets/images/button_widget.svg';

Scrivito.provideEditingConfig('SubscriptionFormWidget', {
  properties: [
    'buttonText',
    'confirmationText',
  ],
  title: 'Subscription Form',
  description: 'Newsletter subscription widget.',
  thumbnail: `/${buttonWidgetIcon}`,
  attributes: {
    buttonText: {
      title: 'Submit button text',
    },
    confirmationText: {
      title: 'Confirmation text',
      description: 'Displayed after successfully submitting the form',
    },
  },
});

Provide the widget component

The form our widget is to display should take care of several situations. Each of them can be managed using state:

  • The form has not yet been submitted. If any of the input fields is empty, a hint is displayed, and the submit button is given a deactivated look.
  • The form has been successfully submitted, meaning that the form data has been sent to a Google script. The form is replaced with the confirmation text.
  • Sending the form data failed. This causes the form to be redisplayed together with a try-again error message.

All this is handled using four states, name, email, sent, and error.

Whenever state is involved, a widget component needs to be implemented as a React component class, not as a function. To have the actual component instances interact with Scrivito (with respect to editing und updating content), they need to be made known to Scrivito using Scrivito.provideComponent.

Here’s the “SubscriptionFormWidget” component:

Copy
// src/Widgets/SubscriptionFormWidget/SubscriptionFormWidgetComponent.js

import * as React from 'react';
import * as Scrivito from 'scrivito';
class SubscriptionFormWidget extends React.Component {
  constructor(props) {
    super(props);
    this.state = {
      name: '',
      email: '',
      sent: false,
      error: false,
    };
    this.onInputChange = this.onInputChange.bind(this);
    this.onFormSubmit = this.onFormSubmit.bind(this);
  }

  onFormSubmit(e) {
    e.preventDefault();
    const scriptUrl = 'https://script.google.com/macros/s/123456abcdef/exec';
    const url = `${scriptUrl}?callback=ctrlq&name=${this.state.name}&email=${this.state.email}`;
    fetch(url, {mode: 'no-cors'}).then(
      () => { this.setState({ sent: true }); },
      () => { this.setState({ error: true }); }
    );
  }

  onInputChange(e) {
    this.setState({ [e.target.name]: e.target.value });
  }

  render() {
    const isFormFilled = this.state.name && this.state.email;
    const confirmationText =
      (this.props.widget.get('confirmationText') ||
      'Thanks for subscribing to our newsletter') +
      `, ${this.state.name}!`;
    if (this.state.sent) {
      return (
        <div className="alert alert-success">
          <strong>{ confirmationText }</strong>
        </div>
      );
    }
    return (
      <div className="container component-section">
        <section className="row">
          <div className="col-md-5">
            <form onSubmit={ this.onFormSubmit } className="form">
              <div className="form-group">
                <label>Name:</label>
                <input onChange={ this.onInputChange }
                  type="text" name="name" className="form-control" required
                />
              </div>
              <div className="form-group">
                <label>Email:</label>
                <input onChange={ this.onInputChange }
                  type="email" name="email" className="form-control" required
                />
              </div>
              <p>
                <small className={ `${isFormFilled ? 'invisible' : ''}` }>
                  Please fill in both fields.
                </small>
              </p>
              { this.state.error &&
                <p>An error occurred. Please try again later.</p>
              }
              <button type="submit" className={ `btn btn-primary${isFormFilled ? '' : ' disabled'}` }>
                { this.props.widget.get('buttonText') || 'Subscribe' }
              </button>
            </form>
          </div>
        </section>
      </div>
    );
  }
}

Scrivito.provideComponent('SubscriptionFormWidget', SubscriptionFormWidget);

Form submission and rendering

Submitting the form invokes the onFormSubmit event handler that requests our Google script using fetch, passing to it the name and email states as parameters. Depending on the request result, either the sent or error state is set to true.

Only if sent is true, the form has been successfully submitted, causing just the confirmation text to be displayed. If the form has not been submitted yet, or if requesting the submission URL lead to an error, only the form is displayed, in the latter case together with a corresponding message.

Tracking form field input

To always have the form field values at hand, the component records changes to the fields using an event handler, onInputChange. Note that the handler distinguishes the fields by their name and saves their contents to the corresponding state variables. This allows us to check whether the fields meet some condition, e.g. if they are empty. If they are, a hint saying that both fields must be filled in is rendered, and the submit button is displayed in a disabled style.

This is just a very basic form of validation, of course. Extensive validation could go into the event handler, for example.

Save the form data to a Google sheet

If you have a Google account, the easiest way to store form data is to send it to a Google sheet. This is what needs to be done to achieve this:

  • Create a Google sheet for storing the form data. Make the sheet public by granting everyone write access.
  • Provide a Google script that appends incoming (form) data to the sheet. Replace the spreadsheet’s URL below with your own one.

Copy
// Google script that responds to GET requests and adds the passed-in
// parameters (form field values in our case) to a spreadsheet.

function doGet(e){
  const ssapp = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/123456abcdef/edit");
  const sheet = ssapp.getSheetByName("Sheet1");
  return insert_value(e,sheet);
}

function insert_value(request,sheet){
  const d = new Date();
  const name = request.parameter.name;
  const email = request.parameter.email;
  sheet.appendRow([d.toLocaleString(), name, email]);  
  return ContentService
    .createTextOutput(request.parameter.callback + "(Done)")
    .setMimeType(ContentService.MimeType.JAVASCRIPT);   
}

After opening the spreadsheet, the script appends a row consisting of the current date and the request parameters to it – and then returns a simple message.

To make the script accessible to everyone using the subscription form, follow these steps:

  • Select the doGet function and execute it. Grant execution permission to everyone.
  • Deploy the script as a web app. Have the script executed as you, and, again, grant access to everyone, even anonymous users. Copy the script’s URL to the clipboard.
  • Use the script’s URL in the widget’s form submission event handler like shown above in the component.

Final words

That was it – you are now using a third-party service for storing the data submitted via a custom Scrivito form widget!

From a security point of view, this approach is quite shaky, not only because the script’s URL is part of the markup. Regarding the spreadsheet, we recommend to not use it for storing sensitive data and to restrict access to it to those who have its URL (instead of making it findable by everyone).

Happy scripting!