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.
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.
On This Page:
Rule syntax and best practices to follow are discussed in the Writing Rules to Retrieve and Update Data in a Google Sheet topic.
Step 1: Obtain an access token
If you do not have an access token for your Google Account, perform this one time step.
Follow these steps to obtain your Google Account token.
Login to your Google Account
Go to:
Cloud customers: https://app.frevvo.com/google/consent
- In-house customers: http://<your frevvo server domain name>/google/consent
Click Allow if you see a screen like the one below (Google often updates their UI so this may differ from what you see).
Copy the access token. Save it to a safe location, you will use it when configuring your forms/workflows.
- You must obtain a Google access token for every Google Account you wish to use with your forms/workflows. Repeat steps #1-4 above for each Google Account.
- In-house customers with multiple servers must obtain Google Access Tokens for each server / Google Account.
- Google limits the number of access tokens per Google Account. If the token limit is exceeded, older refresh tokens stop working.
- Google may revoke Access Tokens unused for ~6 months. If this happens you must obtain a new new token and update your forms/workflows.
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='+encodeURIComponent('employeeid="' + eid + '"');
var updateparams = '&updates='+encodeURIComponent('location='+Location.value+',extension='+Extension.value);
eval('x=' + http.put(updatequery + updateparams, null, headers, false, 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.
The column name on a Google sheet must match the control name. The matching is case-insensitive and any spaces in the column name are ignored. A control named "FirstName" matches a column header "first name." However, references to Google Sheet columns in your rule must be lower case and cannot contain spaces. The correct reference for this example is "firstname."