Save data from a web form to a spreadsheet


We'll show you how to make a JavaScript application that adds rows of data to a Google spreadsheet from a web form. For example, you can save names from a signup form to a Google Spreadsheet. We’ll use PHP as our server-side proxy language for this tutorial, but you can also use Node.js, Ruby, Java, or Python.

To learn more about using non-PHP proxy languages, take a look at the Web Application Server tutorial.

Get Set Up

1Log in to Temboo. If you don't already have an account, you can register for free here.

2Create a new .html file and save it as index.html into a folder where you'll save the rest of your project files.

3Create a new .php file and save it as proxy-server.php into the same project folder as your index.html file.

4Download the latest Temboo JavaScript SDK and unzip the file.

5Change the name of the resulting folder to js-sdk and move it into the same folder as your index.html file.

6Download the latest Temboo PHP SDK and unzip the file.

7Change the name of the resulting folder to php-sdk and move it into the same folder as your index.html.html file. Your project folder should look similar to the screenshot below:

Create your spreadsheet

8Since this sketch uses a Google Spreadsheet, you'll also need a Google account.

9Login to Google's Developer Console, and create a new Project if you haven't done so already.

10Under APIs & auth > APIs, make sure you've enabled API Access for the Google Drive API.

11Under APIs & auth > Credentials, create a new Client ID and specify Web application for the Application Type.

12When configuring the Consent Screen, make sure you fill out the Email Address and Product Name fields.

13Save the Consent Screen details and specify this callback URL as the Authorized Redirect URI:

https://ACCOUNT_NAME.temboolive.com/callback/google

14Go to the the Google > OAuth > InitializeOAuth Choreo, and specify the Client ID from the app you registered at Google and the following Scope: https://spreadsheets.google.com/feeds/

Run the Choreo from our site by clicking the Generate Code button.

The InitializeOAuth choreo will return an authorization URL and a callback ID (required for the FinalizeOAuth step).

15Open a new web browser, navigate to the authorization URL returned by the InitializeOAuth Choreo, and click "Accept" to grant the app access to your Google account.

16Go to the Google > OAuth > FinalizeOAuth Choreo, and supply the callback ID returned earlier by the InitializeOAuth Choreo. Run this Choreo from our site by clicking Generate Code. This process will return a Refresh Token which can be used along with the Client ID and Client Secret to authenticate with Google.

17Create a Google Spreadsheet in Google Drive. The columns in your spreadsheet will need to have labels for this example to work. It doesn't matter what the column labels are, but there must be text in the first row of each column. In this example our spreadsheet has one column called Names as seen below:

A screenshot of a spreadsheet taking data from JavaScript - note the column names

Test the Choreo

18Go to the Google > Spreadsheets > AppendRow Choreo. Select JavaScript from the drop down menu at the top of the page and plug in the required inputs into the proper fields as shown in the screenshot below:

Testing the AppendRow Choreo from our website

19When you click Generate Code on the Choreo page, you'll see "success" in the Output box, and you'll see new data added to your Google Spreadsheet.

Start Coding

20Scroll down to find the Code section of the Choreo page. Copy and paste the code snippet into your index.html file.

21In the same file, make sure that your script source points to the correct location of the temboo.js SDK file.

<!-- Adjust to point at the copy of the Temboo JavaScript SDK on your server -->
<script src="js-sdk/js/temboo.js">< /script>

22In the same file, we'll also want to confirm that the location of our proxy is correctly specified. Our server is proxy-server.php.

// Instantiate the client proxy with the URI of your server proxy
var temboo = new TembooProxy('proxy-server.php');

23You'll notice another code snippet box below the Code section on the Choreo page called Proxy Code. Use the dropdown menu to select PHP, then copy and paste the code snippet into your proxy-server.php file.

24In the proxy-server.php file, make sure that the require_once line points to the correct location of the PHP SDK file in the php-sdk folder that you downloaded earlier.

require_once('php-sdk/src/temboo.php');

Add an input form

If you run your PHP server now, your Google Spreadsheet will be updated and you'll see a "success" message in the developer console. However, we can make this a bit more interesting by incorporating an input form for the values we'd like to add to our spreadsheet.

25In your index.html file, find the line where you set the RowData input and remove it or comment it out. This input will now be set by the form we're about to create.

//appendRowChoreo.setInput('RowData', 'Martha');

26Now comment out the .execute method at the end of your script. We'll now make it so that the .execute method is called only when our form submit button is clicked.

//appendRowChoreo.execute(showResult, showError);

27Add the following HTML form with an input field and a submit button after the JavaScript <script></script> tags of your code:

<form id="form">
	<input id="data" />
	<button id="append" type="submit">Submit</button>
</form>

28Add the following code to execute the Choreo when the submit button is clicked. Make sure to place this code after the end of your form:

<script>
// Execute choreo when submit button is clicked
document.getElementById('append').onclick = function() {
    appendRowChoreo.setInput('RowData', document.getElementById('data').value); 
    appendRowChoreo.execute(showResult, showError);
}

// Disable submit button when clicked
document.getElementById('form').onsubmit = function(e) {
     e.preventDefault();
    document.getElementById("append").disabled=true;
}
</script>

29Finally, add this line of code at the end of both showResult and showError callback functions. This re-enables the submit button when the Choreo has executed.

document.getElementById("append").disabled=false;

30Run your PHP server and view your JavaScript webpage in a browser. If you enter a value into the form field and press submit, the console should print "success" and you'll have a new data element added to your Google Spreadsheet!

Running the AppendRow Choreo from your web form

Note: The first time you run this sketch, you may receive a warning from Google prompting you to authorize access from a third-party system. Follow their instructions about visiting this page to give your code access to your Google account.

31 Here's the complete code:

JavaScript Code:

<!-- Adjust to point at the copy of the Temboo JavaScript SDK on your server -->
<script src="js-sdk/js/temboo.js"></script>

<script>
// Instantiate the client proxy.
// You may need to adjust the path to reflect the URI of your server proxy
var temboo = new TembooProxy('proxy-server.php');

// Add the appendRow Choreo
var appendRowChoreo = temboo.addChoreo('jsAppendRow');

// Add inputs
appendRowChoreo.setInput('Username', GOOGLE_USERNAME);
appendRowChoreo.setInput('Password', PASSWORD);

appendRowChoreo.setInput('SpreadsheetTitle', SPREADSHEET_NAME);

// Success callback
var showResult = function(outputs, outputFilters) {
    // Display outputs
    if(outputFilters) {
        // Display named output filters
        for(var name in outputFilters) {
            console.log(name + ':');
            for(var item in outputs[name]) {
                console.log('    ' + outputs[name][item]);
            }
        }
    } else {
        // Display raw outputs
        for(var name in outputs) {
            console.log(name + ': ' + outputs[name]);
        }
    }
    document.getElementById("append").disabled=false;
};

// Error callback
var showError = function(error) {
    if(error.type === 'DisallowedInput') {
        console.log(error.type + ' error: ' + error.inputName);
    } else {
        console.log(error.type + ' error: ' + error.message);
    }
    document.getElementById("append").disabled=false;
};

</script>
<form id="form">
    <input id="data" />
    <button id="append" type="submit">Submit</button>
</form>

<script>
// Execute choreo when submit button is clicked
document.getElementById('append').onclick = function() {
    appendRowChoreo.setInput('RowData', document.getElementById('data').value); 
    appendRowChoreo.execute(showResult, showError);
}

// Disable submit button when clicked
document.getElementById('form').onsubmit = function(e) {
     e.preventDefault();
    document.getElementById("append").disabled=true;
}
</script>

Proxy Code:

<?php

// Require the core Temboo PHP SDK and required libraries
require_once('php-sdk/src/temboo.php');

// Instantiate the session and Choreo
$session = new Temboo_Session('ACCOUNT_NAME', 'APP_NAME', 'APP_KEY');
$appendRowChoreo = new Google_Spreadsheets_AppendRow($session);

// Act as a proxy on behalf of the JavaScript SDK
$tembooProxy = new Temboo_Proxy();

// Add Choreo proxy with an ID matching that specified by the JS SDK client
$tembooProxy->addChoreo('jsAppendRow', $appendRowChoreo);

// Set default input values
$tembooProxy->allowUserInput('jsAppendRow', 'Username')->allowUserInput('jsAppendRow', 'Password')->allowUserInput('jsAppendRow', 'RowData')->allowUserInput('jsAppendRow', 'SpreadsheetTitle');

// Execute choreo
echo $tembooProxy->execute($_POST['temboo_proxy']);
?>

What Next?

Now that you've mastered working with the JavaScript SDK and Google Spreadsheets, check out the rest of the 2000+ Choreos in our Library and get inspired for your next project.

Once you've got your code up and running, you're ready to move on and do more. From monitoring your running applications, to moving your generated Temboo code to your preferred development environment and sharing it with colleagues, collaborators and friends - we've got you covered.

Need Help?

We're always happy to help. Just email us at support@temboo.com, and we'll answer your questions.


Back