brainysmurf

Decorator Pattern in Apps Scripts

Introduction

We'll identify a problem that can be solved with the decorator pattern. This is essentially "decorating" a wrapping function where there is additional implementation "inside". It's an effective way to define a inject some code around before or after a function executes. Here is an example that times how long it took to execute a function:

var timeit, publicFunction;

timeit = function (f) { /* definition below */ };

brainysmurf

/**
 * DataFromSpreadsheet: Read in Spreadsheet info for a Calculated Datasource in AppMaker. Use a spreadsheet to define a datasource.
 *                      Useful for data modeling, simple Apps. 
 *                      Does not support paging; sheets with large number of rows will see performance penalties
 * @param {object} params
 * @param {string} params.spreadsheetId The ID of the source spreadsheet
 * @param {string} params.sheetName The name of the source sheet
 * @param {string} params.datasource The name of the target datasource
 * @param {number} params.numHeaders How many rows are headers (default = 1)
 * @param {number} params.headerRow Which row contains the name of the field (default=params.numHeaders-1)

brainysmurf

GSheets Formula Tipizoid #1

Learn how to collect a series of columns that that contains target text in the header. Link to Example

The data

You have a database-like table in a Google spreadsheet that is the output of a Google Form that asks questions on a variety of topics. The form itself is organized into sections, where the the first question ("What grade are you in?") will then jump to the relevant section with grade-specific questions. Each section has some questions that repeat ("English") but others that do not ("Design" because students don't take Design until 9th Grade). The result is the following:

        (  First section in form   ) (    Second section in form   )

brainysmurf

Learning V8 by re-solving old problems

Here, I'll document some code I found to be much easier or better to do in an appscripts context with modern Javascript.

Breaking A1Notation into constituent parts

You have a string that represents a range speciation using a1Notation. You need to get the starting row number. Or you need to get the last column. Or you need to get all of those things. You'd like to do it in a way that can be reused. And you wonder if some of the new syntax features makes this interesting.

Let's go over the skeleton of the function first:

brainysmurf

This onEdit trigger implements the feature as given by Ben Collins.

I was motivated to see if the pattern as given by Ben could be abstracted away so that changes to the sheet name, or changes to the area of the checkboxes changed, and the onEdit trigger wouldn't need to be modified.

Sheet1 tab:

For example, clicking on D1 should change it to checked (X), and uncheck B1.

| | A | B | C | D | E |

brainysmurf

Timetabling with aSc and ManageBac

This gist will explain how I built a schedule with aSc timetables, and used the exported XML data to turn it into CSVs compatible with ManageBac's bulk imports for classes and timetable uploads.

This is not intended to provide a complete end-to-end solution, but to provide timetablers with general strategies with specific examples. In particular, the use of aSc subjects and divisions, how they should coincide with complex scheduling needs, and then how to get it to a "flat" output for csv upload. There are a variety of ways to specify a timetable with aSc; this solution only uses constraints provided by subject and division specifications.

This example uses a 7-Day Timetable (Day 1, Day 2, … Day 7), where each day has four blocks (Block 1, Block 2, … Block 4), and each block can be divided into two periods. (Block 1 consists of periods 1 and 2, Block 4 consists of periods 7 and 8). There are seven subjects, plus some school-specific subj

brainysmurf

/*

by ClassroomTechTools.com

This is free and unencumbered software released into the public domain.
Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.

brainysmurf

Use Pandas to clean SIS info

Problem Statement

You have a csv export from an SIS that you want to import into another system. There are lots of duplicate accounts, people with the same first, last, and birthdays. You would like to merge the rows. You decided to do this by identifying any duplicates, and choosing the one with the most columns filled out as the winner. If any of the remaining rows have non-null values for columns where the winning row is null, coelesce them across ("merge" them).

But wait, the target system has the following additional requirements:

• You need to add a role column, based on the gender, 'M' = 'Father', etc
• The export column names don't match, need to rename them

brainysmurf

function dailyTrigger() {
  const end = new Date();
  const start = new Date();
  
  // the first of the current month
  start.setDate(1);

  // the last day of the current month
  if (end.getMonth() === 11) {
    end.setDate(31);  // 31 days in Dec, always

brainysmurf

Data Flow mini share: OA > GSheet > GDS

In this tutorial, we'll use OpenApply as the source of truth to store extra information about each student, connect it via automatic updates to a Google Spreadsheet, and then connect it to Google Data Studio as a data source.

This solution if for a use case where there is a data point for each student, such as consent to be photographed, that needs to be retrieved occassionally by practitioners. It uses OA's custom fields, a tool that downloads and incrementally updates to a Google Spreadsheet, which is then used as a data connector to a Data Studio project.

Schools can then embed the graphs with filtering and search capability into a Google Site to surface the tool. They will then be able to look up this information.

Setup the data source spreadsheet