Date Countdown in Sheets for Triggers

I have a Google Sheet which displays all upcoming PD in the district. It also tracks registrations for people through a web app. I’ve documented that in other places, so I want to focus on an easy method of calculating days until an event to use as a script trigger.

This started because teachers were looking for an automated email reminder a few days before the workshop so they didn’t forget to come. I’d rather they get a Calendar invitation when they register for the event, but I ran into some authentication snags, so that aspect is back burner for the time being. Currently, the sheet is using today’s date and the date of the workshop to trigger an email four days in advance.

Calculating the “days remaining” is pretty easy. The cell formula is:

=ARRAYFORMULA(IF(ISBLANK(B2:B),””,ROUNDDOWN(B2:B – NOW())))

There are several components of this:

ARRAYFORMULA applies formulas to a range of cells rather than a single cell. Saves me from having to copy the formula down to each new entry.

ISBLANK checks for data in a cell. Because it’s inside ARRAYFORMULA, it looks at the cell in the matching row. If it is blank, TRUE is returned.

ROUNDDOWN rounds a result to a whole integer. This is useful because the subtraction taking place inside the formula returns a large decimal. This makes it easier to test in the script.

NOW gives the date and time when the sheet is updated. Any time you make a change, NOW is calculated.

– The IF conditional keeps the sheet clean and wraps everything up. The syntax is, IF(_logical test_, _value if true_, _value if false_). So, this reads, “If the cell column B for this row is blank, show nothing. If it’s false (is not blank), calculate the difference between the PD date in column B and NOW.

The core of the function is the count down calculation. For instance, today is Friday, September 8. Subtracting it from a date in the future like Monday, September 11, returns a whole integer: 3. I can test for that integer (or any integer) in a simple script.

This is particularly helpful with timed triggers in scripts. I have a utility script wrapped in a conditional:

“`

if(date === 3) {

// do something here

}

“`

If the condition isn’t met in the script, nothing happens and I don’t get a failure email notification. This is also nice because if I want to adjust the timing, the trigger can stay the same (daily, for instance) without changing the codebase.

Link Two Google Docs for Translating

An interesting Stack Overflow question popped up about auto-translating one document into another when edits are made. Most Google services have things called “triggers” which run functions after some kind of event – opening the doc, making a change, etc.

A Google Doc does not have access to the onEdit trigger, so there is no way to automatically run the translation unless you put it on a timer trigger, and that’s a waste of resources, especially if you’re not constantly updating the document. But, you can link two documents together via the ID and push changes made using a custom menu.

Grab a copy of the template with instructions.

Source:

var ui = DocumentApp.getUi();
var docProps = PropertiesService.getDocumentProperties();

function getText() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();
  var array = [];

  array.push(body.getText());

  sendTheText(array);
}

function sendTheText(e) {
  if (docProps.getProperty("childId") == "") {
    var prompt = ui.prompt("Child doc ID", ui.ButtonSet.OK);
    var response = prompt.getResponseText()
    docProps.setProperty("childId", response);
  }
  var childDoc = DocumentApp.openById(docProps.getProperty("childId"));
  var es = LanguageApp.translate(e, 'en', 'es');
  childDoc.getBody().clear();
  childDoc.getBody().appendParagraph(es);
}

function clearProps() {
  docProps.setProperty("childId", "");
}

function onOpen() {
  ui.createMenu("Custom Translate").addItem("Run", "getText").addItem("Clear IDs","clearProps").addToUi();
}