How do I use the Google Spreadsheet Save action?

Details
Published: Wednesday, 17 September 2014 15:16

ChronoForms v5 includes a Google Spreadsheet Save action that will let you save your form results to a Google Docs spreadsheet. This FAQ has a simple example of using the action.

Important: the standard version of this action no longer works because Google made a change to the way they authorise users. You can download a beta version of an updated action here. Please advise me - Bob aka GreyHead if you find any bugs.  

First, you need to set up the spreadsheet in Google Apps. Open a new Spreadsheet, give it a suitable name - I used 'CF test' - and add column names into row 1. In my test I used 'text1', 'text2' and 'text3'. That's all that is needed here.

Second create you form and use Array Names for the inputs that you need to save. I used the suggested 'GSheet' model ID and so my inputs are called GSheet[text1]GSheet[text2] and GSheet[text3]

Drag a copy of the Google Spreadsheet Save action into the On Submit event of the form in the Setup tab and Edit the action.

Save and close the action; Save the form and test.

If you have the spreadsheet open when you submit the form you should see the new record inserted immediately. 

If the submission isn't working add a Debugger action to the form On Submit event and see if you get helpful error messages shown from ChronoForms or from the Google Apps response.

Notes:

Setting up OAuth 2.0

The following is taken from a guide written by a beta-tester for the GSheet Export [GH] action (see the note above). 

The Google side – enable the API, and create your connection

  1. Go to console.developers.google.com. You’ll arrive at the Dashboard.
  2. Assuming you haven’t created a project before, on the top bar, you’ll see a dropdown menu called “Select a Project”. Click on that, and choose “Create a Project”.
  3. Give your project a name, then agree to the terms of service.
  4. Click Create.
  5. The project will load. On the dashboard, click on the APIs title on the APIs window.
  6. Click on Google APIs.
  7. In the resulting screen, find the Google Apps APIs section, then click on Drive API.
  8. Enable the API.
  9. Now, on the left hand column, choose Credentials.
  10. Click on the Add Credentials drop down menu. Choose OAuth 2.0 client ID.
  11. Choose Web Application
  12. Give it a name
  13. In the Authorised JavaScript origins field, put in your web site URL. Leave the URI field empty.
  14. Click Create.
  15. Add another set of credentials. This time, choose Service Account.
  16. Choose P12.
  17. Once you’re done, your credentials area should look like the below. You’ll need the client ID with the long code, the service account email address, and you’ll need to download the P12 file.

Create a Google Spreadsheet

Chronoforms – create form and connect

  • Now, go to Chronoforms, create your form. I assume you know how to create a form, otherwise, Bob’s other guides can cover this.
  • Note: in the Chronoforms form, name your fields as “GSheet[your field name from your Google Spreadsheet]”. I populated both the name and the ID field with this value – I couldn’t be bothered finding out which field needs to be populated as such.
  • In the Setup screen, apart from everything else which normally happens with using Chronoforms, drag a GSheet Save [GH] action into your On Submit action. The configuration of this action are:
    • Enable
    • Client ID = the long string Client ID from your OAuth 2.0 credentials. In Google, you may notice another Client ID value in the Service Account – don’t use that one.
    • Email address = the Service Account email address
    • P12 file: you’ll need to upload this file first. Bob’s readme file from when you downloaded the action, that tells you where to put it. Once done, copy the name of the file and put it in this field.
    • Sheet name = the name of the spreadsheet created earlier
    • Worksheet name = the name of the worksheet within the spreadsheet
    • Data path = GSheet
  • Test your form to see if everything works.