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

{
    "id": 11084391,
    "name": "Harris, Mark",
    "term_grade":
    {
        "comments": "<p>observ comment</p>",
        "rubrics":
        [
            {
                "id": 6484,

brainysmurf

/**
 * Get an overview table of all items returned from the endpoint, useful for when there lists of objects are returned.
 * See comments below for screenshots.
 * 
 * https://gist.github.com/brainysmurf/e45f608b68609f8a0b222ac99b8905d8
 *
 * To use, copy the below code into the "Tests" tab of the endpoint
 * Click blue "Send" button
 * Click on "Visualize" in the Body tile
 * Note that it only displays the returned objects, there's nothing magical that's getting "all" objects via pagination

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

brainysmurf

FEB Virtual Community: Visualizations

Please find attached!

brainysmurf

/**
 * Append a 2d array to an existing sheet or can create a new sheet
 * to match the largest row in the dataset. 
 * davidsottimano.com
 * @param {array} data 2d array of data 
 * @param {string} sheetName (optional) The sheet you want to output to. If the sheet does not exist, this script will auto-create a new sheet with the specified sheet name. If the sheetName is not specified, a new sheet will be created
 */
function outputToSheet(data, sheetName=null) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (sheetName==null) sheetName = 'Default Sheet Name';

brainysmurf

Motivation

After a few months of using my Endpoints library to help solve myriad of problems in production, I've only had anecdotal evidence that it was a "way more" performant way of interacting with API endpoints, compared to more traditional method of using AppsScripts advanced services. So I decided to actually measure it.

The Test

In my domain, there are 26 google groups, a set for each grade in the school, where a set is one for students and one for parents. We have about 30 students per grade, which means membership of the each students group is about 30, but for the parents, it is twice that (as both parents are a member).

With two different code bases, I download all memberships of all these groups.

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

function measure_({func, arr}) {
  const start = new Date().getTime();

  func.call(null, arr);

  const end = new Date().getTime();
  const delta = end - start;
  const report = `${func.name} took ${delta} milliseconds`;
  Logger.log(report);
  return delta;