Apps Script - Create a Border Based on Unique Cell Value

Created an Apps Script that interfaces with an active Google Sheet to create a border above a row to improve spreadsheet legibility.

function CreateBorder() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName('Sample Border'); // apply to sheet name only (Change this value for every sheet we want to run it on.)

  var rows = sheet.getRange('a2:d15'); // range to apply formatting to

  var numRows = rows.getNumRows(); // no. of rows in the range named above

  var values = rows.getValues(); // array of values in the range named above

  var testvalues = sheet.getRange('a2:a15').getValues(); // array of values to be tested (Module Descriptor)

  rows.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // remove existing borders before applying rule below

      //Logger.log(numRows);

  for (var i = 0; i <= numRows - 1; i++) {

      var n = i + 1;

      //Logger.log(n);

      //Logger.log(testvalues[i] > 0);

      //Logger.log(testvalues[i]);

      if (testvalues[i] != 0) { // test applied to array of values

        sheet.getRange('a' + (n + 1) + ':d' + (n + 1) ).setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // format if true

      }

  }

};

Obviously, this seems pretty trivial to do manually when looking at my sample data below; but this was used on 1000+ line spreadsheets with multiple products and creating a border for each row manually would take way too long.

Google Sheets - Counting Ranges of Values Based on a Unique String Subset

I was given a data set with a concatenated Module Type & Module Instance that created a unique identifier.  From this, I was tasked with identifying counts of the unique Module Types that needed to be purchased and shipped.  Below is my quick (and pretty elegant) solution for solving that in Google Sheets.

This line identifies the unique string subset I need to quantify:

=ARRAYFORMULA(UNIQUE(LEFT(A3:A20, 9)))

"Check Column A for unique values, then grab the left 9 characters: MOD_FN001.001 --> MOD_FN001."

This line counts the occurrence of this unique string subset within my data range:

=ARRAYFORMULA(COUNTIF(UNIQUE(A3:A20), CONCAT(D3:D10,"*")))

"UNIQUE creates an array of unique values from the given range A3:A20.  CONCAT combines a string from D3:D10 with '*' creating 'MOD_FN001*' which checks that a string contains the beginning substring 'MOD_FN001' in the generated array."

Google Sheets - Conditional Formatting Based on Column's Unique Cell Values

In order to improve a spreadsheet's legibility, I like to conditionally format the background of cells based on groups of data.

Use this line in the conditional formatting section to highlight all rows with different unique values in a given range (this one is A2:A26):

=iseven(match($A2,unique($A$2:$A$26),0))

ME596 Robotics - Denavit-Hartenberg Parameter Generator for a 3-Link Robot

In an attempt to ensure I fully understood the material I was studying, I preferred to create spreadsheet calculators to solve problems in my textbook. One such example was this tool to generate Denavit-Hartenberg Parameters.

Below is the explanation for using it:

"The blue boxes are input sections, so you put in the right inputs.  The link lengths are kind of tricky to understand, so hope that link lengths = 0 (a-value), it works, but you have to have an in-depth understanding of whats going on.  Also, in the example I have shown, 651 = d1, 652 = d2, 653 = d3; Finally, for the show work you need to include all the yellow boxed and green boxed matrices, everything is labelled.

ME596 Robotics - Axis/Angle Representation Generator

Another tool to represent axis angles.

Instructions for using the tool:

"For the Axis/Angle Representation Generator, put your input degrees into the blue boxes. Everything is displayed in radians, so you may need to convert to exact answers. The green boxes are "answers," but include the yellow for show work credit."