Public records requests

_README.md

Track and send reminders about public records requests using Google Sheets

Setup

Use this example spreadsheet for a starting point. Under the "File" tab, select "Make a copy".

https://docs.google.com/spreadsheets/d/1jbkGnok85Q9qT0O4hRAn0uVDA9deWW10kMwreoBpzKg/.

Spreadsheet

The spreadsheet has the form shown in public-records-request-log.csv. Columns may be renamed if desired. Additional columns can be added as needed. The only important thing is that the provided columns stay in their current locations.

Conditional formatting

The provided conditional formatting makes it easier to scan the spreadsheet at a glance. It will show one of three statuses for a records request.

1. Complete (green). You have your records and have moved on.

=EQ($A:$A, "Complete")  // If equal to "Complete"

2. Pending (yellow). If action date is still in future. You are patiently awaiting your records.

=GT($F:$F, TODAY())  // If date is later than today

3. Stalled (red). If action date is today or in past, but you have not received your records. You are getting less patient.

// The last condition is to avoid red fill as default for blank rows.
=AND(LTE($F:$F, TODAY()), NE($A:$A, "Complete"), NE($A:$A, ""))  // If date is today or older, but not complete

Data validation

The status can be either "Pending" or "Complete". There are data validation measures in place under the "Status" column.

List of items: "Pending,Complete"

The "Action date" must be a weekday. Data validation custom formula is:

=LTE(WEEKDAY($F:$F, 2), 5)  // If less than or equal to 5. Option=2 follows format of 1=Monday, 2=Tuesday, ... , 5=Friday

Script

While viewing the spreadsheet, click the "Tools" tab and then select "Script editor" to open the script that powers email notifications. The provided code is the same as that in script.gs.

Before using the script, you will need to update the spreadsheetDetails function with your intended email recipients as well as your new spreadsheet's ID. It is located in your spreadsheet's URL as shown here: https://docs.google.com/spreadsheets/d/DOC_ID/

The email will look similar to email.html.

Set a trigger by clicking "Resources" and then selecting "Current project's triggers". Adjust it to your liking.

Test it out by selecting main in the dropdown menu at the top of the page. Then click on the play button ▶ to trigger the script to run the main function. You might need to first authorize the script so it can access your email account.

email.html

You set today as the action date for PRR "School vendor contracts" to "Dept. of Education". It was sent on 1/27/2016.

These are your comments:
"For education reporter."

Here is the spreadsheet: https://docs.google.com/spreadsheets/d/1jbkGnok85Q9qT0O4hRAn0uVDA9deWW10kMwreoBpzKg/

public-records-request-log.csv

script.gs

// Setup:
// Add your sheet's ID and any email recipients to the spreadsheetDetails function.

// Run daily. Set the following trigger:
// Click "Resources" tab, then select "All your triggers"
// Set: main, Time-driven, Day timer, 9am to 10am

// Pending emails are sent Monday-Friday.
// Stalled emails are sent as weekly reminders and only sent on Mondays.

// Workflow: When sending initial request, fill in new row in spreadsheet. Ex.
// Status , Subject, Agency, "Date sent"   , "Last response", "Action date" , Contact , Comments
// Pending, PRR    , Agency, "Wed, 1/27/16", "Thu, 1/28/16" , "Mon, 2/15/16", "Person", "For big exposé."

// Set "Action Date" for when you should send reminder if not yet contacted by that date.
// An email will go out on that date reminding you to contact the agency for an update on the status of your PRR.
// Once contacted, update with new action date. You will receive another email on the new date. You can then
// either continue updating with new action dates, or else you will receive weekly reminders on Mondays to remind
// you that your PRR is stalled and that you need to send regular reminders to the agency handling the request.

function spreadsheetDetails() {
  var sheet_id = '';  // Ex. '1jbkGnok85Q9qT0O4hRAn0uVDA9deWW10kMwreoBpzKg'
  var url = 'https://docs.google.com/spreadsheets/d/' + sheet_id;
  var recipients = [''];  // Ex. 'First Last <contact@domain.com>'. Multiple addresses accepted 
  
  return {
    sheet_id: sheet_id,
    url: url,
    recipients: recipients
  } 
}

function sendEmail(subject, message) {  
  var recipients = spreadsheetDetails().recipients;
  MailApp.sendEmail(recipients, subject, message);
}

function sendPendingEmail(row) {
  var subject,
      message,
      topic = row[1],
      agency = row[2],
      date_sent = row[3],
      comments = row[7];
    
  subject = "Pending PRR: Send a reminder to " + agency + " about PRR " + topic;
  message = 'You set today as the action date for PRR "' + topic + '" to "' + agency + '". ' +
    "It was sent on " + (date_sent.getMonth() + 1) + "/" + date_sent.getDate() + "/" + date_sent.getFullYear() + ".\n\n";
  
  if (comments.length > 0) { message += 'These are your comments:\n"' + comments + '"\n\n'; }
  
  message += "Here is the spreadsheet: " + spreadsheetDetails().url;
  
  sendEmail(subject, message);
}

function checkPendingRecord(row) {
  var action_date = row[5].setHours(0,0,0,0);
  var d = new Date();
  var today = d.setHours(0,0,0,0);
  
  if (today === action_date) {
    sendPendingEmail(row);
  }
}

function sendStalledEmail(row) {  
  var subject,
      message,
      topic = row[1],
      agency = row[2],
      date_sent = row[3],
      comments = row[7];
    
  subject = "Stalled PRR: Check with " + agency + " about PRR " + topic;
  message = 'Here is your weekly reminder to check on PRR "' + topic + '" for "' + agency + '". ' +
    "It was sent on " + (date_sent.getMonth() + 1) + "/" + date_sent.getDate() + "/" + date_sent.getFullYear() + ".\n\n" +
    'These are your comments:\n"' + comments + '"\n\n' +
    "Here is the spreadsheet: " + spreadsheetDetails().url;
 
  sendEmail(subject, message);
}

function checkStalledRecord(row) {
  var d = new Date();
  var day = d.getDay();
    
  // Send on Mondays only. 1=Monday, 2=Tuesday, etc.
  if (day === 1) {
    sendStalledEmail(row);
  }
}

function getData() {
  var sheet_id = spreadsheetDetails().sheet_id;
  var ss = SpreadsheetApp.openById(sheet_id);
  var sheet = ss.getSheets()[0];

  var data = sheet.getDataRange().getValues();
  data.splice(0, 1);  // Remove header row (index, number_to_remove).
  
  return data;
}

function checkStatus() {
  var i,
      row,
      status,
      data = getData();
  
  var d = new Date(),
      today = d.setHours(0,0,0,0);
    
  for (i in data) {
    row = data[i];
    status = row[0];
    action_date = row[5].setHours(0,0,0,0);
    
    if (status === "Pending") { 
      if (action_date >= today) {  // Pending
        checkPendingRecord(row);
      } else {  // Stalled
        checkStalledRecord(row);
      }
    }  // else Complete
  }
}

function checkMetaData() {
  if (spreadsheetDetails().recipients.length === 1 && spreadsheetDetails().recipients[0] === '') {
    throw new Error("You must specify recipients.");
  }
  if (spreadsheetDetails().sheet_id === '') {
    throw new Error("You must specify a sheet ID.");
  }
}

function main() {
  checkMetaData();
  
  var d = new Date();
  var day = d.getDay();
  
  // Check if today is a weekday. 1=Monday, 2=Tuesday, etc.
  if (day >= 1 && day <=5) {
    checkStatus();
  }
}