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

If you do not have an access token for your Google Account, perform this one time step.

Form designers must obtain a Google access token for every Google account you wish to use with your forms/flows. For example if you connect your forms/flows to a Google account info@frevvo.com and support@frevvo.com, you will need to obtain two access tokens.

Follow these steps to use the frevvo Google connector consent UI to obtain an access token for each Google Account.

Open your web browser and login to your Google Account
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
Click Allow if you see the message below: This gives access to your account even when your device is turned off.
Copy the access token. THIS IS VERY IMPORTANT. The token is used as the password for wizards and rules.
Repeat steps 1-2 for all Google accounts you will be using with the frevvo Google connector.

An access token generated for a in-house installation running the Google connector, will not work with the Google connector running in the frevvo cloud. You must generate a separate token for each environment.

It is very important to save the token once you obtain it for a given account. The token allows the connector to access the account. There is a limit on the number of tokens that are issued per client-user combination. If the token limit is exceeded, older refresh tokens stop working. A token can be revoked if it is not used in a 6 month period.

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:

/*member password, user*/

if (UpdateGoogleSheet.clicked) {
  var eid = EId.value; // Unique key in the Google Sheet row  
  var headers = {"user":"<google id>","password":"<your access token>"};
  var updatequery = '/google/spreadsheets/update/key/<your spreadsheet key>?wsname=Locations&query=employeeid="' + eid + '"';
  var updateparams = '&updates=location=' + Location.value + ',extension=' + Extension.value;
        
  eval('x=' + http.put(updatequery + updateparams, null, headers, false));
}

It’s triggered by clicking on the Update Google Sheet button.
We setup headers and an update query using your access token and spreadsheet key (the long ID in the URL of the Google Sheet). In this example, the name of the Google Sheet tab is Locations. Change the wsname= parameter to the name of your Google Sheet tab if you named the tab something different.
Add updateparams: we’re updating location and extension with new values.
Run the update – perform an http.put() and eval the results.

Try it yourself using the sample form.

Update a Google Sheet

Step 1: Obtain an access token

Step 2: Add the Business Rule