Live Forms v7.1 is no longer supported. Click here for information about upgrading to our latest GA Release.

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 5 Next »

Let's take a look at how you can update existing values in a sheet. It’s another common scenario that can be used for a variety of tasks (e.g. limiting the number of submissions for a particular form or creating a sequentially increasing counter). We’ll use this sample Google Sheet to discuss. It has a row for each employee: Employee Id, Location and Extension.

 On This Page:

First, it’s important to note that Google Sheets is not a transactional system like a database and results can be unpredictable if multiple users update the same Sheet at the same time.

Step 1: Obtain an access token

As before, you have to first get an access token. This is a one-time step.

  1. Open your web browser and login to your Google Account

  2. Go to:

    1. Live Forms Online Cloud customers: https://app.frevvo.com/google/consent

    2. Live Forms In-house customers: http://<your frevvo server domain name>/google/consent
  3. Copy and Save the access token. THIS IS VERY IMPORTANT. The token is used as the password for  wizards and rules. 
  4. Click on the Accept button.

Step 2: Add the Business Rule

We’ve created a simple example form. Select the employee, a location and a new extension number. Click the Update Google Sheet button, wait a few seconds and see that the sheet was successfully updated. We did this using this rule:

var x;

    if (Connect.clicked) {
        var headers = ‘ {“
            user”: ”sales @frevvo.com”,
            ”password”: ” < access token > ”
        }’;
        var readquery = ‘/google/spreadsheets / query / key / < spreadsheetkey > /w/Employees’;
        eval(‘x = ’+http.get(readquery, headers));

        var opts = [”];
        for (var i = 0; i < x.results.length; i++) {
            opts[i + 1] = x.results[i].employeeid + ‘ = ’+x.results[i].firstname + ‘‘ +x.results[i].lastname;
        }
        EId.options = opts;
    }
  1. It’s triggered by clicking on the Update Google Sheet button.
  2. We setup headers and an update query using your access token and spreadsheet key (the long ID in the URL of the Google Sheet).
  3. Add updateparams: we’re updating location and extension with new values.
  4. Run the update – perform an http.put() and eval the results.

Try it yourself using the sample form.

Google Connector Known Issues

Please report any issues or feedback to us here. See the list below for some known issues with the Google Connector.

TicketDescriptionWork-around
#16848Google Spreadsheet - Boolean control : Fetching values true/false should not be case sensitive

Google sheets automatically converts true, True, false, and False values to upper case. Click here for more information. Let's say you have a checked Boolean control value stored in your spreadsheet  as TRUE.  This might cause an issue when reading from Google spreadsheet, and setting the value of the Boolean checkbox. Implement one of the following as a workaround:

  • Convert to lowercase before setting the value of the T/F control
  • Format the cells in your spreadsheet as Plain Text by clicking the 123 button and selecting Plain Text.

  • Enter the text as: 'true - (the ' prevents Google sheets from changing the value to upper case).

#17952Some number values update to date values in Google SheetsIn some cases, Google Sheets may change valid number values to dates values. The best way to handle this situation is to write scripts to clean up the bad data in your Google Sheets.
  • No labels