Blog

Stewardship Reporting with Google Forms

Land trusts practicing good land stewardship produce a report each year summarizing stewardship activity and land use for stewarded properties. We’ve seen many methods for gathering data from stewards and producing the final report. Some assemble physical copies of handwritten reports into a binder. Others type up reports in a word processor. Some have built elaborate, form based tools of their own. We helped Littleton Conservation Trust leverage Google Forms to gather the input and used some Google Apps scripting to generate the report as a Google document that could then be edited to add a cover letter and insert any other information.

The Form
Here are the fields we placed on the form:

Property Name – a text field for the property name, could be a dropdown list for better consistency

Steward(s) (Visits per steward) – name(s) followed by number of visit in parentheses such as “Sam Steward (17)”

Total Steward Visits – total # of visits by all stewards for this property

Date of this Report – date field for date this report was filled out

Report Prepared By – who filled out the report

Conditions – grid field with Good, Average, Poor, Not Applicable columns and Signs / Gates, Entrance, Parking, Trails, Boundary Markers, Waterways, Roads, Vandalism/Littering rows.

Other – text field for a custom condition

Other Rating – radio buttons field with Good, Average and Poor (1,2,3)

Public Tours – text field for info on public tours of the land

Land Usage – more general field to describe how the land is used

Brochure/Maps – do brochures and maps need updating or refilling at kiosks?

Projects / Maintenance Completed – long text field

Projects / Maintenance In Progress – long text field

Projects / Maintenance To Be Done – long text field

Additional Remarks – long text field

Collecting Responses
We set the form’s sharing to “Anyone who has the link”. If you have a license for Google G-Suite (paid or through the nonprofit program), you can share with your domain only but that only works if the stewards all have Google Accounts in your domain.

We emailed the stewards the link with the request and instructions for filling out the form. With wide ranging computer skills and habits from years of reporting, we gave them the option of filling out a paper form and sending it in if they’d rather we did the data entry to the form. All but one filled out the online form so there was very little re-entry.

The Script
Next we created a script to process the form responses. We’re working on the best way to package the script and activate it from a nice user interface. For now, it just lives in a document and we run it manually from the script editor.

This script is specific to our form and our reporting format so use it as a starting point but plan to edit it to fit your needs.

Create a new, blank Doc in Google Docs. Type a basic description into the document body and name the document so you know what it holds! Otherwise you may accidentally erase it in the future when you see what looks like an empty document. We named ours “Stewardship Report Script” and wrote “This document holds the script for generating the annual stewardship report from form responses.” in the body.

From the Tools menu, choose Script Editor. Remove the default script template from the editor and replace it with the following:

// Script for generating steward reports from Google Form Responses
// Version 1.0
// 2017-03-26 by Scott Lewis

// styling for the title
var title = {};
  title[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;
  title[DocumentApp.Attribute.FONT_FAMILY] = 'Arial';
  title[DocumentApp.Attribute.FONT_SIZE] = 24;
  title[DocumentApp.Attribute.BOLD] = true;

//styling for normal text
  var normal = {};
  normal[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.LEFT;
  normal[DocumentApp.Attribute.FONT_FAMILY] = 'Arial';
  normal[DocumentApp.Attribute.FONT_SIZE] = 11;
  normal[DocumentApp.Attribute.BOLD] = false;

// styling for headings
  var heading = {};
  heading[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.LEFT;
  heading[DocumentApp.Attribute.FONT_FAMILY] = 'Arial';
  heading[DocumentApp.Attribute.FONT_SIZE] = 16;
  heading[DocumentApp.Attribute.BOLD] = true;

// styling for bold, normal text
  var bold = {};
  bold[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.LEFT;
  bold[DocumentApp.Attribute.FONT_FAMILY] = 'Arial';
  bold[DocumentApp.Attribute.FONT_SIZE] = 11;
  bold[DocumentApp.Attribute.BOLD] = true;

// styling for the conditions table
  var checkStyle = {};
  checkStyle[DocumentApp.Attribute.FONT_FAMILY] = 'Arial';
  checkStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;
  checkStyle[DocumentApp.Attribute.FONT_SIZE] = 14;

// add a row to the conditions table. t = table object, l = label text, c = condition.
function addRow(t, l, c) {
  var r = t.appendTableRow();
  var lc = r.appendTableCell(l);
  lc.setAttributes(bold);
  if (c == "Good") {
    var cellg = r.appendTableCell("✔︎");
    cellg.setAttributes(checkStyle);
    r.appendTableCell();
    r.appendTableCell();
    r.appendTableCell();
  } else {
    if (c == "Average") {
      r.appendTableCell();
      var cella = r.appendTableCell("✔︎");
      cella.setAttributes(checkStyle);
      r.appendTableCell();
      r.appendTableCell();  
    } else {
      if (c == "Poor") {
      r.appendTableCell();
      r.appendTableCell();
      var cellp = r.appendTableCell("✔︎");
      cellp.setAttributes(checkStyle);
      r.appendTableCell();
      } else {
        r.appendTableCell();
        r.appendTableCell();
        r.appendTableCell();
        var celln = r.appendTableCell("✔︎");
        celln.setAttributes(checkStyle);
      }
    }
  }
}

// add a paragraph. b = document body, l = label (added in bold), c = contents (added in normal), ns = newline after label, ne = newline after paragraph body.
function addP(b, l, c, ns, ne) {
  if (c > "") {
    var p = b.appendParagraph("TEMP");
    p.clear();
    var lbl = p.appendText(l);
    lbl.setAttributes(bold);
    if (ns == true) { p.appendText("\r"); }
    var content = p.appendText(c);
    content.setAttributes(normal);
    if (ne == true) { p.appendText(" \r"); }
  }
}

// main function to generate report
function generateReport() {
  var d = new Date();
  var reportYear = d.getFullYear()-1;
// a new Doc will be created each time this script runs. Remember to delete old ones if you are testing changes.
// create the new file and name it with the year appended to the name
  var reportDoc = DocumentApp.create('Annual Land Stewardship Reports for ' + reportYear);
  var reportBody = reportDoc.getBody();
  reportBody.setAttributes(normal);
  // add title page content
  var reportTitle = reportBody.appendParagraph("\r\r\r\r\rAnnual Land Stewardship Reports for " + reportYear + "\r\r\r\r\r\r\r");
  reportTitle.setAttributes(title);
  var dateStr = d.getMonth()+"/"+d.getDate()+"/"+d.getFullYear();
// customize the credits to your organization's specifics
  var reportCredits = reportBody.appendParagraph("Prepared and submitted "+dateStr+" by Stewardship Coordinator and Land Stewards.");
  reportCredits.setHeading(DocumentApp.ParagraphHeading.HEADING2);
//new page after title page is done
  reportBody.appendPageBreak();
// get the form The Id is specific to your form. You can see it in the URL at the top of the page when viewing the form in Google Forms. The first time your script accesses your form, you'll need to give it permission in the popup that appears.
  var form = FormApp.openById('usdlfisfe4r346856fdfdg');  // random Id for this example, not an actual form Id
  var formResponses = form.getResponses();
// loop through the form responses
  for (var i = 0; i < formResponses.length; i++) {
    var formResponse = formResponses[i];
    var itemResponses = formResponse.getItemResponses();
// name this page with the property name
    var pName = reportBody.appendParagraph(itemResponses[0].getResponse()+"\r");
    pName.setAttributes(heading);
// add who filed the report, which stewards visited the property and number of visits per steward and in total
    var reportedDate = itemResponses[3].getResponse();
    addP(reportBody, "Reported by: ", itemResponses[4].getResponse() + " on " + reportedDate, false, false);
    addP(reportBody, "Steward(visits): ", itemResponses[1].getResponse(), false, false);
    addP(reportBody, "Total Visits: ", itemResponses[2].getResponse(), false, true);
// new paragraph for conditions
    var rB = reportBody.appendParagraph("Conditions:");
    rB.setAttributes(bold);
// table of conditions
    var lOther = itemResponses[6].getResponse();
    var aC = itemResponses[5].getResponse();
    var tConditions = reportBody.appendTable();
    var rH = tConditions.appendTableRow();
    rH.appendTableCell("");
    rH.appendTableCell("Good");
    rH.appendTableCell("Average");
    rH.appendTableCell("Poor");
    rH.appendTableCell("N/A");
    addRow(tConditions, "Signs / Gates", aC[0]);
    addRow(tConditions, "Entrance", aC[1]);
    addRow(tConditions, "Parking", aC[2]);
    addRow(tConditions, "Trails", aC[3]);
    addRow(tConditions, "Boundary Markers", aC[4]);
    addRow(tConditions, "Waterways", aC[5]);
    addRow(tConditions, "Roads", aC[6]);
    addRow(tConditions, "Litter / Vandalism", aC[7]);
// now add the rest of the fields
    addP(reportBody, "Brochures: ", itemResponses[9].getResponse(), true, true);
    addP(reportBody, "Public Tours: ", itemResponses[7].getResponse(), true, true);
    addP(reportBody, "Land Usage: ", itemResponses[8].getResponse(), true, true);
    addP(reportBody, "Completed Projects: ", itemResponses[10].getResponse(), true, true);
    addP(reportBody, "In-Progress Projects: ", itemResponses[11].getResponse(), true, true);
    addP(reportBody, "Future / Needed Projects: ", itemResponses[12].getResponse(), true, true);
    addP(reportBody, "Remarks: ", itemResponses[13].getResponse(), true, false);
// new page to get ready for next property
    reportBody.appendPageBreak();
 }
}

 
What is happening in this script?

Each formResponse is a steward's report for a property. We start with the property name as a heading then add the steward'(s) name / number of visits per steward and the total number of visits. This information comes from the first few itemResponses in each formResponse.

Then we create a table and add the itemResponse data from the grid for conditions. We could have retrieved the labels for the rows from the grid itself but we wanted to shorten some names for better fit on the page so we manually typed the labels into the script. We add a row to the table for each condition and our addRow functions places the checkmark in the correct column based on the condition from the form.

Now we add the rest of the fields and the general remarks as additional paragraphs and start a new page to be ready for the next formResponse.

Each time this script runs, it generates a Google Doc named "Annual Land Stewardship Report for yyyy" where yyyy = the year before the current year as reports are typically prepared early in the new year for the prior year. If you run this script repeatedly while modifying it to your needs, you'll end up with a lot of documents all with the same name so delete the unneeded ones as you go to keep things simple.

After you generate the report, you can open it in Google Docs and edit it as needed. Add a cover letter, illustrate issues or progress with graphics or photos, generate a table of contents from the headings or add page numbering and headers and footers.

How do you run the script?
In the script editor, choose your function (generateReport in our example) where it says "Selection function" then press the Play icon.

What's Next?
As mentioned, we will be making this more end-user friendly by moving the script into a form or other element where it can be initiated from clicking a button.

Other improvements we're exploring include using Google's file upload ability in the form so that stewards can upload photos to illustrate issues needing attention or to show the results of projects. We may also add a cover letter generator so that the process is managed by a form that asks a few questions of the stewardship coordinator then presents a button that generates the report including a cover letter generated from his or her answers.

2017 Trail Websites Update

Here’s a video we’re working on for spring conferences that gives a pretty good overview of our current capabilities and how they’ve been applied to several customers’ websites. Of special interest this year is the enhanced online donation form capabilities we added.

First, be genuine

It is tempting to get lost in the maze of Search Engine Optimization, working hard on catchy headlines and keyword filled content, trying to learn the latest in the SEO algorithm race to game the search engines into bringing you more traffic. And you should pay attention to generating traffic. With limited time and budget, should you spend your resources on SEO or the real content of your message?

At Trail Websites, we have found that a genuine, impactful message does more for our customers’ missions than extra time and money focused on search optimization, flashy graphics, animations and other shiny objects. Yes, you need to bring people to your content and keep their attention there long enough for them to hear your message. But the message does the work. Keywords, animated GIFs and views by people who aren’t interested in your cause won’t help your fundraising or community engagement on their own.

After a few years of focusing on data, one of our nonprofit customers, a food pantry, shifted their focus to the stories of their clients in 2016. They had their best annual appeal ever and their year-end appeal appears to also be on track to break records. They still used data to support the stories and show that the story of one is the story of many. They spent more time on the genuine impact of the problem and what they do to help though and, along with an improving economy, translated to increased donations. Feeling like the stories were about their own neighbors drove more giving than infographics full of stats on hunger.

A land trust focused on getting people outdoors and having group leaders tell the story of the land’s history and its future potential — one path leading to habitat, drinking water safety, public recreation and non-motorized commuter access to the rail station and the other path leading to houses, increased demand for public services and higher taxes — with a result of voter turnout at town meeting in support of acquiring the property for open space. Local interest drew people in far more than today’s hot keywords like climate change, environmental impact, sprawl and endangered species.

There are two takeaways in these examples:

  • Data is a supporting piece of the story, not the story.
  • Know your audience. If the audience is local and the impact is local, the story needs to be local.

One other aspect of both of these examples is important to note: In both cases, similar stories and images were consistently used across press, website, email campaigns, newsletters and handouts. In today’s short attention span, soundbite and tweet filled world, frequency and repetition matter more than ever.

The Walden Woods Project

image of the home page of the new website
The Walden Wood Project’s new website.

We launched The Walden Woods Project’s new website today. Their prior site had a nice historical feel and a lot of useful content. It was showing its age though with a dependency on Flash for the home page, search tools that weren’t working and no friendliness for mobile devices.

The new site balances a more modern look with historic elements. It adds a sophisticated system for managing and presenting the many works in their collection of content by, and about, Henry David Thoreau. It works on mobile devices, has useful search facilities and much easier tools for donations and their online store.

The site features beautiful imagery from Walden Pond and the surrounding area and a video introduction by Don Henley, founder of The Eagles and The Walden Woods Project.

The Walden Woods Staff put many hours of work into updating, refining, organizing and creating content for this new site. Our part was creating the home and tools for experiencing their content. Visit the site to see what we created together!

Make your event calendar more effective

calendar_300_300If you are like most trusts and other nonprofits, you publish some form of calendar or event listing on your website. You probably even promote it through your newsletter and social media channels. But it isn’t getting the traffic it should. It may be a case of going where your audience is being a better option than trying to bring them to where you are over and over again.

At Trail Websites, we rely on Events Manager to build event management into our customers’ websites. We like that it allows us the freedom to style the event lists as needed and that it has built-in event registration and even ticket selling abilities when we need them. It also has a less often promoted ability to share its contents through the Internet Calendar protocol also known as iCal.

If your calendar is accessible by iCal, consider promoting the idea of subscribing to your calendar instead of trying to get people to go check your calendar periodically. That way your events will show up as a calendar in their calendar software on their phone, tablet or computer — the place(s) they check their daily schedule all the time!

Here’s an example of instructions that can be shared with your audience on how to subscribe to an iCal calendar:

To subscribe to our calendar using iCal, use this URL:
http://our-example-domain.org/events.ics (replace our-example-domain.org with your real domain and /events.ics with your calendar solution’s ical endpoint)

This will subscribe to the monthly meetings and special events only. We exclude the outside events as there can be many of them on the same day (especially spring plant sales) and we don’t want to fill up your calendar with all of them. (This wording is specific to the calendars of a client’s website. Modify it to suit your approach to calendaring.)

In Apple Mail, choose File | New Calendar Subscription, then paste the above URL into the Calendar URL field and press the Subscribe button. Name it whatever you like then press OK to save it (change the other settings, if you need to, first).

In Google Calendar, click the little down arrow next to “Other Calendars” on the lower left of the screen then choose “Add by URL” and paste the above URL into the URL field and press Add Calendar. Generally you won’t want to check the “Make the calendar publicly accessible box”. It is better to refer people to this page and have them subscribe to the source so they get reliable updates when we change the calendar.

Buttons and anchor targets: Two free plugins

We’ve created two new plugins to make content editing a little easier.

The first is for WordPress sites using Bootstrap based themes or that have had Bootstrap added to them. It adds a button in the Visual Editor that lets you specify destination, label, size, type/color and full-width (or not) then inserts the correct markup for the corresponding Bootstrap button. Learn more about the Trail Websites Bootstrap Button Creator.

The second works with any theme and gives you a button in the Visual Editor that lets you specify text and an ID to use for an in-page anchor. You can then create a link with the normal link tool or manually that jumps to your anchor target. Learn more about the Trail Websites Anchor Target Creator.

Both are free and supported on an as-available basis. They are not officially part of our Trail Data Manager solution. They are some tools we created for a client and are happy to share with others to the extent that they are useful. If there is sufficient use, we’ll look into packaging them up and putting them on WordPress.org’s plugin repository.

Google Maps Javascript API now requires a key

Beginning on June 22, 2016, Google updated their Maps API to require an API Key. If you already use Google Maps to show conservation property locations or use a plugin like Events Manager for your events that uses Google Maps to show event locations, you’ll eventually need to add an API Key.

If you implement a new site after June 22, 2016 that uses the Maps API, you will need to use a key to get it to work. Events Manager has been updated so that you can enter yours API Key in the Maps section of the formatting tab in their settings page in WordPress Admin.

Where do you get a key? You’ll need a free Google Developers account from https://developers.google.com

Then go to the Maps Javascript API page: https://developers.google.com/maps/documentation/javascript/

  • Near the top right, click the “Get A Key” button
  • Create a project for your site. I’ve found it works best to click the down arrow, select “Manage all projects” then use the “Create Project” link at the top of the page.
  • When the list of APIs is presented, add the Maps Javascript API and enable it on the next screen
  • Click “Go to Credentials”
  • Select Google Maps Javascript API if it isn’t already selected then choose Web Browser (Javascript) and click “What credential do I need?”
  • Name your key
  • I’ve had trouble getting the referrer scope to work on some sites but it is a good idea to try it to protect your API quota so put your domain in with wildcards as shown in the example but be prepared to go back and remove this limit if the API doesn’t work for you
  • Click the button
  • Copy the API credentials so you can paste them into your custom javascript or add them to a plugin setting such as for Events Manager
  • Test that everything is working on your site

Salesforce for non-profits, an ongoing tale

We’ve started a project helping a non-profit customer migrate to Salesforce for non-profits, using the Non-Profit Starter Pack (NPSP). NPSP sets up Salesforce for donors and donations instead of the typical customers and leads.

We’ll chronicle the journey here in the blog in case our lessons learned are useful for others.

The first lesson is to test with a small dataset. This is in the documentation but cannot be overemphasized. Salesforce has a mass delete tool in case you need to undo an import but it deletes 250 contacts, opportunities or accounts at a time. If you’ve imported 9,000 accounts, you’ll be repeating the mass delete 36 times if you need to re-do it.

The second lesson is to be thoughtful about rules. A key rule is how to define households. Using last name, first name is not a good idea for most non-profits with lots of donors. Street address + postal code probably makes more sense. With so many ditching home phones and just using cellphones, phone number doesn’t work well either.

The third lesson is to customize Salesforce to have a field for your existing system’s donor record ID and to include this in the export from the old system. It’ll come in handy when subsequently adding donations to donors, for example.

The fourth lesson is to understand the data in your old system well enough that you can re-assemble it into something that is easier to import. In our case there were a lot of 1 to many relationship that needed to be walked to assemble complete records. Addresses came from address tables with just a key to the right record in the donor record, for example. So a lot of simple left joins were needed to include the right data in the export.

The fifth, and last for today, lesson is to use the Salesforce.com / NPSP field names in your header rows in your CSV files. This will save you some time on the field mapping process for the import.

More to come in the next few weeks.

Play in the sandbox, not on your website

Play and explore here.
Play and explore here.
If your website is based on a content management system like WordPress, Drupal, Joomla, etc., you’ve no doubt been intrigued by a plugin that adds functionality to these systems. Stop. Don’t press install!

If you want to try out new plugins to see if they meet your needs, it is really, really important to do so on a separate installation of your CMS that can be deleted and rebuilt from time to time. We call this a sandbox and it is the place for experimenting and testing things before we decide to use them on our live, public websites.

Let’s use WordPress as an example. Plugins extend the WordPress database. They add taxonomies. They install code that may or may not have vulnerabilities. They sometimes prove incompatible with other plugins or themes. None of these are things you want to have littering or impacting your WordPress installation forever more.

In “WordPress for Conservation & Land Trusts“, we outlined the use of a staging server and how AMPSS and MAMP can make it easy to do development and testing on your laptop or desktop computer. Those are good ways to build a sandbox for trying out new themes, plugins and WordPress releases before installing them on your live site.