frevvo + Google Apps: Part 4. Updating a Google Sheet

Does your organization uses Google Apps? You can combine frevvo + Google Apps to create some really cool and useful solutions. In Part 1 of this series, we saw how you can save (write) submissions to a Google Sheet. In Part 2, we saw how you can use the Google Distance Matrix API to automatically calculate mileage and reimbursement amounts. Part 3 showed you how to create dynamic pick lists from a Google Sheet (read).

google-ss-loc-ext

Google Sheet: Employee Location and Extension

Today, we’ll 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.

As before, you have to first get an access token. This is a one-time step. Login to your Google account and then visit: https://app.frevvo.com/google/consent. Follow the prompts and copy the access token and save it. Detailed documentation is here.

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:

if (UpdateGoogleSheet.clicked) {
var eid = EId.value; // Unique key in the Google Sheet row
var headers = ‘{“user”:”<google id>”,”password”:”<access token>”}’;
var updatequery = ‘/google/spreadsheets/update/key/<spreadsheetkey>?wsname=Locations&query=employeeid=”‘ + eid + ‘”‘;
var updateparams = ‘&updates=location=’ + Location.value + ‘,extension=’ + Extension.value;

eval(‘x=’ + http.put(updatequery + updateparams, null, headers, false));
}

  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.

Interested in learning more? Visit our website to view examples, watch videos, sign up for a free 30-day trial or contact us for more information.

Like what you see? Check out Part 5 which shows you how you can save documents generated by your frevvo form to Google Drive.

 

2 Responses to frevvo + Google Apps: Part 4. Updating a Google Sheet

  1. Pingback: frevvo + Google Apps: Part 6. Leave Approval Workflow | frevvoblog

  2. Pingback: frevvo + Google Apps: Part 1. Save to a Google Sheet | frevvoblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: