Posted in Google Apps for Education, Green Schools, Paperless Schools

How to combine frevvo with Google Apps for Paperless Schools

Too much paper in schools

Many K-12 schools use Google Apps for Education – a suite that provides email, collaborative documents etc. School districts have hundreds of forms that can be digitized: Enrollments, Admissions, Emergency Contact Forms, Consent Forms and so on.

As a result, schools still use tons (literally 140 tons per year on average) of paper. That paper usage is not just inefficient, but goes against sustainability and environmental goals.

Combining frevvo + Google Apps (Sheets, Drive, Maps etc.) to create really cool and useful solutions for K-12 school districts is an easy way to reduce paper usage. From electronic signatures to dynamic pick lists to using Google Drive as a content repository to approval workflows, the combination just makes completing routine, day-to-day business tasks easier and burnishes your green credentials.

Imagine a K-12 school already using Google Apps in various areas. All staff and students have accounts, they use it for homework etc. frevvo helps teachers save time that they can use for more important things – the business of education.

Let’s take a look at various ways to make the school district business office more efficient for teachers and school district employees by adding frevvo to the mix.

  1. Save submissions from a frevvo form into a Google Sheet.
  2. Perform automated mileage calculations using the Google Maps API.
  3. Create dynamic drop down controls where the choices come form a Google Sheet.
  4. Update a Google Sheet with values from a form.
  5. Save documents generated by your frevvo form to Google Drive.
  6. Put it all together to create a leave approval workflow that connects with a Google Sheet, routes between multiple users for signatures, updates the Google Sheet and saves PDF documents to Google Drive.
  7. Configure Single Sign On to frevvo using Google Apps credentials so teachers are not wasting time remembering passwords and entering information manually .

A quick note: this article goes into technical details on how to use frevvo + Google Apps for the above use cases.

Save Submissions to a Google Sheet

Paperless Field Trip infographic

Field Trips are a way of life for a K-12 teacher. Whatever the subject or the field trip destination, students must obtain signed parental consent forms before they can participate. Typically, this is a tedious and manual process for the teacher.

An electronic permission slip makes life a lot easier. Parents access it via email or a secure portal. Mandatory fields and form validations guarantee that forms are filled appropriately so there’s less error correction. Each time a completed form is submitted, a Google sheet is updated with selected information. Quickly scanning a Google Sheet to verify parental consent from any device is much easier and more efficient than wading through a pile of paper forms.

Learn more: Paperless field trips infographic.

All Consent forms have a section to collect the parent’s name and contact information. We will use that section in our example to demonstrate how to collect this information in a Google Sheet.

Step 1: First, you have to 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.

Step 2: Create your form. We’ve created a simple example contact form with a few fields. If you need help creating a form, check out the Quick Demo and Form Designer tutorial videos on our website.

Example of a simple example contact form with Name, Street, City, State/Province, Zip Code and Country fields.
Properties panel for the State/Province field in the Simple Contact form. The control name, state matches the column name in the Google Spreadsheet.

Step 3: Create your Google Spreadsheet. Make sure the column header names match the Name property of each control. You can view the spreadsheet we created for the form above at this link (it’s read-only). See how the column names are Name, Email, Street, City etc. The Name property of the corresponding control in the form is set to match exactly.

Step 4: Connect your form to your spreadsheet using the Save to Google Sheets wizard. Detailed documentation is here. You must login using your user id and auth token (from Step 1), select a spreadsheet and worksheet and click Finish.

That’s it. When the form is submitted, the data will be saved to the selected Google Sheet. You can perform all the usual operations on the data e.g. see the Graphs Tab in our example sheet and you can see how we have created some sample graphs to analyze our submission data.

Google graphs from submitted data in the spreadsheet. Pie chart showing countries of contacts and world map showing locations of contacts.
Google Spreadsheet graphs for submission data

Automated mileage calculations using the Google Maps API

Mileage reimbursement is a routine business approval workflow used in many organizations. Unfortunately, it’s usually a PDF form (for example, see here ).  It’s tedious and mistake-prone. Calculate miles manually from odometer readings, figure out the total mileage and reimbursement amount.

Branford Patriots Mileage Reimbursement Approval workflow that uses the Google Distance Matrix API to automatically calculate mileage.

What a complete waste of time! With frevvo, everything is automated. Enter your origin and destination and the system will calculate the travel distance using the Google Distance Matrix API.  Of course, it’ll automatically add all the mileage and calculate the total amount.
Try it yourself by clicking the image or this mileage reimbursement template.

Dynamic pick lists from a Google Sheet

Google Sheet with First Name, Last Name and Employee ID columns. 12 rows of data.

Every school business office has numerous processes such as Travel Reimbursement, Time Off Requests, Cheque Requests that staff perform routinely. These are usually just forms that are routed for approvals and signatures.

Dynamic pick lists (drop downs) are a very common feature of these types of workflows. With frevvo, you can use business rules to dynamically initialize the options (choices) in a pick list from a Google Sheet. We’ll use this sample Google Sheet to discuss. It has a row for each employee: First Name, Last Name, Employee Id.

We use a rule to read information from the Sheet and populate the employee pick list. Here’s the relevant business rule:

var x;

if (Connect.clicked) {
    var headers = ‘{
        “user”: ”<Google User Id>”,
        ”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 Connect button.
  2. We setup headers and a query using your access token and spreadsheet key (the long ID in the URL of the Google Sheet).
  3. Run the query – perform an http.get() and eval the results.
  4. Parse the results into an array. The array elements are “hagen=Walter Hagen” etc.
  5. Set the options to the array.
Brandenberg Instititute with a pick list that is initially empty. Click a Connect button and it will populate the choices in a Select Employee dropdown.
Dynamic pick list from a Google Sheet.

The pick list will display the actual names (Walter Hagen, Alexa Stirling etc.) whereas the values returned upon selection will be the ids (hagen, stirling etc.) so it’s easy to perform further lookups.

Try it yourself by clicking on this link or on the image above.

Update a Google Sheet

Google Spreadsheet with Employee ID, Location and Extension columns. 11 rows of data.

Updating existing values in a Google sheet is 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).

Using frevvo, it’s easy to update a Google sheet to generate unique sequential PO numbers or to update employee data or any data in the sheet.

We’ll use this sample Google Sheet as a simple example to show you how its done. 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.

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 it using this rule:

if (UpdateGoogleSheet.clicked) {
    var eid = EId.value; // Unique key in the Google Sheet row
    var headers = ‘{
        “user”: ”<Google User 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.

Save generated documents to Google Drive

Save documents to Google Drive and, essentially, use Google Drive as an affordable system for managing documents. This is a great solution for many K-12 schools.

When a parents submit a completed Permission Form, or a Leave Approval workflow completes, or your school creates a Purchase Order, you want to save the documents somewhere. Many companies still save them in email folders as attachments or manually drop them into a shared network drive. With frevvo, you can automatically upload them to Google Drive.

Permission Form for Student Email, Online Documents, Calendar, Videos and Sites.
Upload to Google Drive

We’ve created a sample form and a shared Google Drive folder for demo purposes. Ironically, it’s a paper permission form that a parent must sign so students can use less paper and do things electronically.

Click on the link above or on the image, fill in a student name, sign the form and enter an email address (don’t worry, it’s only used for this demo to email you the signed PDF. We won’t save it, it won’t appear on the PDF and it won’t be saved to Google Drive).

When you submit, the form gets uploaded to Google Drive and saved in a folder with the student’s name so you can easily find it later. You’ll also get an email.

Save to Google Documents wizard.
Upload to Google Drive

How can you connect your own form or workflow to Google Drive. It’s easy:

  1. In Google Drive, create a parent folder that will hold all the submissions for this form.
  2. In the Form Designer, simply click on the Doc Actions (what happens to my data) wizard and select the “Save to Google Documents” option.
  3. In the wizard that appears, enter your credentials (see above for how to get an access token).
  4. Select the parent folder you created earlier in Step 1.
  5. For Submission folder, you may enter a fixed value (not recommended) or use a template. In this case, we have chosen the StudentsName control. For example, if you enter John Adams in the Student Name control, the submission documents will be stored in a subfolder called JohnAdams.

That’s it. Try it out for yourself using our sample form and see how the form PDF is saved to Google Drive. If your form had attachments (e.g. receipts for an Expense Report) they would also be saved into the subfolder.

Process Automation – route for approvals

Put it all together to create a leave approval workflow that connects with a Google Sheet, routes between multiple users for signatures, updates the Google Sheet and saves PDF documents to Google Drive.

1. Employee Requests Leave

Leave Request Form with Leave Start Date, Annual Leave available and sick Leave Available columns.
Leave request Form

First, the employee logs in to the system (either directly to frevvo or integrated with Active Directory or other LDAP or SAML) and requests leave by filling out a form. This form reads Annual and Sick days from this Google Sheet for the currently logged in employee, shows them in the pick list labels and sets up the pick list options so that the employee can only select a valid # of days. The employee then signs the request digitally. This locks the section from being further edited. Finally, the employee sends the request to his/her manager for approval.

2. Manager Approval

2 iPhones: one showing Manager Approval on Task List and seocnd one showing Manager Approval section for Leave Approval workflow.

The workflow then routes the request to that employee’s manager for approval. Since frevvo knows who the employee is, it can route to the specific person that needs to approve. The manager will get a notification email with a link to the leave request. He/she can then access the leave approval via the built-in task list and approve the request. If there are questions/concerns, the manager can reject it back to the employee for more information.

3. HR Department Processing

Finally, the workflow routes to the HR department for processing. Once HR has completed processing, the HR manager can click Finish and the workflow completes.

4. Update Google Sheets & Save to Google Drive

Once the workflow completes, the Google Sheet is automatically updated to reflect the changes in available days. The system will automatically subtract the number of approved days from the number that were originally available so that the data in the Sheet is always up-to-date.

Image of PDF uploaded to Google Drive.
PDF uploaded to Google Drive

The PDF Leave Approval document is also uploaded to Google Drive. The workflow is configured so that frevvo’s Google Connector will create a subfolder with the employee’s first and last names and the leave start date and upload the PDF of the approval to that subfolder so that it’s very easy to find at a later date.

5. Notify the Employee

Finally, the employee who originally requested the leave is also notified via email that the leave was approved and the PDF document is attached to the email. This way, the employee can save the documents for his/her records.

Single Sign On using Google credentials

Finally, for organizations that are already using Google Apps, it makes sense to sign in to frevvo using the same credentials.

Since Google Apps acts as a SAML Identity Provider and since frevvo supports SAML, you can easily sign in to your frevvo Cloud tenant or On-premise system using your Google Apps credentials. It’s easier and faster to submit travel requests or absence records if you don’t have to remember yet another password and enter employee information manually.

Follow these easy steps to set up Google SSO.

Dynamic forms and automated workflows with Google Apps

Photo by Green Chameleon on Unsplash

Leave a Reply