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("rrrrrAnnual Land Stewardship Reports for " + reportYear + "rrrrrrr");
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.
