Skip to content

Instantly share code, notes, and snippets.

View brainysmurf's full-sized avatar

Adam Morris brainysmurf

View GitHub Profile

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
function dailyTrigger() {
const end = new Date();
const start = new Date();
// the first of the current month
// the last day of the current month
if (end.getMonth() === 11) {
end.setDate(31); // 31 days in Dec, always
brainysmurf / example.json
Last active January 11, 2022 22:39
PYP term grade response object
"id": 11084391,
"name": "Harris, Mark",
"comments": "<p>observ comment</p>",
"id": 6484,
brainysmurf / tests.js
Last active November 16, 2021 02:18
Use from within Postman as visualizer
* 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.
* 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 /
Last active February 19, 2022 22:44
Data flow mini share: OpenApply -> Google Sheets -> Google Data Studio

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 /
Last active September 12, 2021 12:03

FEB Virtual Community: Visualizations

Please find attached!

brainysmurf / snippet.js
Last active July 9, 2021 06:33
outputSheet snippet
* Append a 2d array to an existing sheet or can create a new sheet
* to match the largest row in the dataset.
* @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 /
Last active June 22, 2021 12:53
Performance of Endpoins library using fetch requests compared to advanced service.


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 /
Last active September 18, 2023 05:31
An `onEdit` trigger that ensures that only one checkbox is selected in a row. Set up once and will work even with changes to name of the sheet, or where it is in terms of rows and columns. Motivated from

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 /
Last active June 6, 2021 08:46
Performance of various iterations in AppsScripts
function measure_({func, arr}) {
const start = new Date().getTime();, arr);
const end = new Date().getTime();
const delta = end - start;
const report = `${} took ${delta} milliseconds`;
return delta;