Google Apps Script utility functions for working with 2D arrays in Sheets/tabs, plus a few other miscellanea. The 2D functions depend on the first row of a tab being column headers and each row below the first row having the same number of columns as the first row.

to_util

/*
updated 2018-04-28
source lives here: https://gist.github.com/nicobrx/2ecd6fc9ca733dcd883afebba5cf200e
standalone script ID for use as a library: 1gZ78JObyrYiH0njoZ86fQ2NgMwavUgiXVhRDrIFetPZL256e31PSNiHq

Functions included here:

Sheets data array and object functions
  objectifySheet(sheet,propertyNames,newPropertyNames) - Takes a sheet with a header row and converts it into an array of objects
  arrayFromSheet(sheet,propertyNames,newPropertyNames) - creates an array from a sheet, can specify column headers
  writeArrayToSheet(ar,sheet,replaceFirstRow) - writes an array to a sheet
  symetric2DArray(ar) - ensures that array elements of an array are of equal length
  appendArrayToSheet(ar,sheetName,hasHeader,id) - appends a 2D array to a sheet
  deduplicateArray(ar) - removes duplicate elements from an array
  objectFromSheet(sheet,properties) - Creates an object from a sheet, takes a heirarchy of properties as argument
  function objectFromArray(ar,properties) - 
  arrayFromObject(obj) - turns an enumerable object into an array - converse of objectFromSheet
  updateSheet(ar,columns,sheetName,id) - updates a sheet, keying on supplied column names

Miscellaneous utilities
  getMonthString(offset) - returns a string representing a month in YYYY-MM format
  removeFunctionsFromCells(sheetName) - converts functions to strings, replacing = with '
  runtimeCountStop(start) - for keeping track of script runtime
  recordRuntime() - collects runtimeCountStop script properties and writes them to a sheet

Not recommended - these are here for backwards compatability
  checkArrayForMatches(array,properties) - checks an array of arrays or objects and looks for matches to a properties arg
  fetchSheet(spreadsheetID,sourceSheet) - fetches a sheet from another spreadsheet as an array
  joinArrays(ar1,ar2,params) - similar to an inner join for arrays

*/

  // sample function comment
  /** 
   * Description
   * @param {string} sheet - notes
   * @param {datatype} parameter name - notes
   * @return {datatype}
   */

  
  /**
  * Takes a sheet with a header row and converts it into an array of objects with 
  * property names matching column headers. Particularly useful for working with 
  * some underscore methods. In most other cases, the objectFromSheet function is 
  * easier to use, since you don't have to iterate through an array to find what 
  * you are looking for.
  * @param {string} sheet - the sheet to objectify
  * @param {array} propertyNames - optional column headings to turn into properties. If not 
  * supplied, all columns will be used
  * @param {array} newPropertyNames - optional parameter to overide column names as property names
  * @return {array}
  */
  function objectifySheet(sheet,propertyNames,newPropertyNames){
    if (typeof sheet === 'string'){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(sheet);
    }
    var arSheet = sheet.getDataRange().getValues();
    var objArray = [];
    var namesAndColumns = {};
    var headerData = arSheet[0];
    propertyNames = propertyNames || headerData;
    for (var i = 0, x = propertyNames.length; i<x;i++){
      var propertyName = newPropertyNames ? newPropertyNames[i] : propertyNames[i];
      namesAndColumns[propertyName] = headerData.indexOf(propertyNames[i]);
    }
    for (var j = 1, x = arSheet.length; j<x;j++){
      var rowObj = {};
      for (var k in namesAndColumns){
        rowObj[k] = arSheet[j][namesAndColumns[k]];
      }
      objArray.push(rowObj);
    }
    return objArray;
  }
  
  
  /**
  * Creates an array from a specified sheet with column names in the
  * first row. The output array only includes supplied column names
  * and optionally replaces column names with supplied new names
  * @param {string} sheet - name of sheet or sheet object
  * @param {array} propertyNames - columns to add to array
  * @param {array} newPropertyNames (optional) - new column names
  * @return {array}
  */
  function arrayFromSheet(sheet,propertyNames,newPropertyNames){
    if (typeof sheet === 'string'){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(sheet);
    } 
    var arSheet = sheet.getDataRange().getValues();
    var headerData = arSheet[0];
    propertyNames = propertyNames || headerData;
    newPropertyNames = newPropertyNames || propertyNames;
    var ar = [];
    ar.push(newPropertyNames);
    var namesAndColumns = {};
    for (var i = 0, x = propertyNames.length; i<x;i++){
      namesAndColumns[propertyNames[i]] = headerData.indexOf(propertyNames[i]);
    }
    for (var j = 1, x = arSheet.length; j<x;j++){
      var row = [];
      for (var k in namesAndColumns){
        row.push(arSheet[j][namesAndColumns[k]]);
      }
      ar.push(row);
    }
    return ar;
  }
  
  /**
  * Write an array to a sheet. If the sheet doesn't exist, it is created. 
  * Returns true if it succeeds.
  * @param {array} ar - the two-dimensional array to write
  * @param {string} sheetName - the name of the sheet to which to write
  * @param {boolean} replaceFirstRow - true if array has a header row
  */
  function writeArrayToSheet(ar,sheet,replaceFirstRow){
    if (ar && ar[0] && ar[0][0] && sheet){
      if (typeof sheet === 'string'){
        var sheetName = sheet;
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getSheetByName(sheet);
        if (sheet == null) { 
          sheet = ss.insertSheet();
          sheet.setName(sheetName);
        };
      } 
      var range = sheet.getDataRange();
      range = replaceFirstRow ? range : sheet.getRange(2,1,range.getHeight(),range.getWidth());
      range.clearContent();
      var startRow = replaceFirstRow ? 1 : 2;
      var newRange = sheet.getRange(startRow,1,ar.length,ar[0].length);
      try {
        newRange.setValues(ar);
        return true;
      } catch (e) {
        Logger.log(e);
      }
    } else {Logger.log("Error: writeArrayToSheet: arguments ar and sheet are required")}
  }
  
  /** 
   * Takes a 2D array with element arrays with differing lengths
   * and adds empty string elements as necessary to return 
   * a 2D array with all element arrays of equal length.
   * @param {array} ar
   * @return {array}
   */
  function symetric2DArray(ar){
    var maxLength;
    var symetric = true;
    if (!Array.isArray(ar)) return [['not an array']];
    ar.forEach( function(row){
      if (!Array.isArray(row)) return [['not a 2D array']];
      if (maxLength && maxLength !== row.length) {
        symetric = false;
        maxLength = (maxLength > row.length) ? maxLength : row.length;
      } else { maxLength = row.length }
    });
    if (!symetric) {
      ar.map(function(row){
        while (row.length < maxLength){
          row.push('');
        }
        return row;
      });
    }
    return ar
   }
  
  /**
  * Add an array to the bottom of a sheet. If the sheet doesn't exist, it is created.
  * Returns true if it succeeds.
  * @param {array} ar - the array to write
  * @param {string} sheetName - the name of the sheet to which to write
  * @param {boolean} hasHeader - does the array to append have a header row?
  * @param {string} id - optional, ID of the sheet to write to
  */
  function appendArrayToSheet(ar,sheetName,hasHeader,id){
    if (ar.length !== 0 && ar[0].length !== 0){
      var ss =  (id) ? SpreadsheetApp.openById(id) : SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(sheetName);
      if (sheet == null) { 
        sheet = ss.insertSheet();
        sheet.setName(sheetName);
      };
      var range = sheet.getDataRange();
      Logger.log('Last row = '+ range.getLastRow());
      var row = 1;
      if (range.getLastRow() === 1){ // getLastRow returns 1 on an empty sheet
        row = (hasHeader) ? 1 : 2;
      } else {
        if (hasHeader) { ar.shift() };
        row = range.getLastRow() + 1;
      }
      var newRange = sheet.getRange(row,1,ar.length,ar[0].length);
      try {
        newRange.setValues(ar);
        return true;
      } catch (e) {
        Logger.log(e);
      }
    }
  }
  
  /**
  * Goes through every cell on a sheet, checks if the cell contains a 
  * formula, and if so coverts to text
  * @param {string} sheetName - the sheet to check
  */
  function removeFunctionsFromCells(sheetName) {
    var ss = SpreadsheetApp.getActiveSpreadsheet(),
        sheet = ss.getSheetByName(sheetName),
        range = sheet.getDataRange();
    var formulas = range.getFormulas();
    for (var i = 1; i < formulas.length; i++) {
      for (var j = 1; j < formulas[i].length; j++) {
        var formula = formulas[i][j],
            newValue;
        if (formula){
          newValue = formula.toString().replace("=","'");
          range.getCell(i+1,j+1).setValue(newValue);
        };
      }
    }
  }
  
  /**
  * Function returns yyyy-MM as string, useful for sheet naming and logging
  * @param {number} offset - the month to return. 0 equals this month, -1 equals last month
  * @return {string}
  */
  function getMonthString(offset){
    var d = new Date();
    var timeZone = 'MST';
    var dString;
    d.setDate(1);
    d.setMonth(d.getMonth()+offset);
    dString = Utilities.formatDate(d, timeZone, 'yyyy-MM');
    return dString.toString();
  }
  
  /**
  * Takes an array and returns same with duplicate elements removed
  * @param {array} ar - array to deduplicate
  * @return {array}
  */
  function deduplicateArray(ar) {
    var seen = {};
    var out = [];
    var len = ar.length;
    var j = 0;
    for(var i = 0; i < len; i++) {
      var item = ar[i];
      if(seen[item] !== 1) {
        seen[item] = 1;
        out[j++] = item;
      }
    }
    return out;
  }
  
  
  /**
  * Creates an object from a sheet, starting with
  * a heirarchy of properties based on the properties
  * argument, then setting the bottom property to 
  * an object with property names matching column 
  * headers. Good for efficient lookups between sheets.
  * @param {object} sheet - the google sheet from which to create object
  * @param {array} properties - the header names specifying the columns from which to get property heirarchy
  * @param {string} id (optional) - the sheet ID from which to get data
  * @return {object}
  */
  function objectFromSheet(sheet,properties,id){
    if (typeof sheet === 'string'){
      var ss =  (id) ? SpreadsheetApp.openById(id) : SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(sheet);
    }
    var obj = {};
    if (sheet){
      var arSheet = sheet.getDataRange().getValues();
      var arHeader = arSheet.shift();
      var propertyIndices = [];
      for (var i = 0;i< properties.length;i++){
        if (arHeader.indexOf(properties[i]) > -1){
          propertyIndices.push(arHeader.indexOf(properties[i]));
        }
      }
      for (var j = 0;j < arSheet.length;j++){
        var names = [];
        var value = {};
        var row = arSheet[j];
        for (var k = 0;k < propertyIndices.length;k++){
          names.push(row[propertyIndices[k]]);
        }
        for (var l = 0;l < arHeader.length;l++){
          if (propertyIndices.indexOf(l) == -1){
            value[arHeader[l]] = row[l];
          }
        }
        setObjHeirarchy(obj,names,value);
      }
      return obj;
    }
  }

  function objectFromArray(ar,properties){
    var obj = {};
    if (ar){
      var arHeader = ar.shift();
      var propertyIndices = [];
      for (var i = 0;i< properties.length;i++){
        if (arHeader.indexOf(properties[i]) > -1){
          propertyIndices.push(arHeader.indexOf(properties[i]));
        }
      }
      for (var j = 0;j < ar.length;j++){
        var names = [];
        var value = {};
        var row = ar[j];
        for (var k = 0;k < propertyIndices.length;k++){
          names.push(row[propertyIndices[k]]);
        }
        for (var l = 0;l < arHeader.length;l++){
          if (propertyIndices.indexOf(l) == -1){
            value[arHeader[l]] = row[l];
          }
        }
        setObjHeirarchy(obj,names,value);
      }
      return obj;
    }
  }
  
  /**
  * Takes an enumerable object and returns a two-dimensional array.
  * Properties that have objects as values become cell values
  * If a property has a primitive value, the primitive becomes
  * a cell value. This function complements objectFromSheet
  * @param {object} obj - the object 
  * @return {array}
  */
  function arrayFromObject(obj){
    var ar = (Array.isArray(obj)) ? obj : [[obj]];
    var newArray = [];
    var hasObject = false;
    while (ar.length  > 0){
      var row = ar.shift();
      row.forEach(function(cell,cellIndex){
        if(typeof cell === 'object'){
          var keys = Object.keys(cell);
          if (typeof cell[keys[0]] === 'object'){
            for (var i = 0;i<keys.length;i++){
              var key = keys[i];
              if (typeof cell[key] === 'object'){
                var newRow = row.slice(0);
                hasObject = true;
                newRow[cellIndex] = key;
                newRow.push(cell[key]);
                newArray.push(newRow);
              }
            }
          } else {
            var newRow = row.slice(0,cellIndex);
            for (var j = 0;j<keys.length;j++){
              var key = keys[j];
              newRow.push(cell[key]);
            }
            newArray.push(newRow);
            hasObject = false;
          }
        }
      });
    };
    if (hasObject){
      return arrayFromObject(newArray);
    } else {
      return newArray;
    }
  }
  
   /**
   * Helper function for objectFromSheet. Takes a list of property names and 
   * an optional value parameter and creates or uses a heirarchy try matching 
   * property names and sets them to the value parameter
   * @param {object} obj - the object to modify
   * @param {array} pList - the list of properties, in heirarchical order
   * @param {object or primitive} value - the value to set at the bottom of the heirarchy
   */
   function setObjHeirarchy(obj, pList, value) {
      var schema = obj;  // a moving reference to internal objects within obj
      var len = pList.length;
      for(var i = 0; i < len-1; i++) {
          var elem = pList[i];
          if( !schema[elem] ) schema[elem] = {}
          schema = schema[elem];
      }
  
      schema[pList[len-1]] = value;
  }

/** 
 * Takes an array with a header row and updates
 * a sheet with a header row, replacing existing rows
 * when the columns specified in the columns argument
 * match.
 * @param {array} ar - a two-dimensional array with which to update a sheet
 * @param {array} columns - a one-dimensional array with the column names to match when updating
 * @param {string} sheetName - the sheet to update
 * @param {string} id - optional, the ID of the sheet to update, if different from active sheet
 * @return {boolean} - returns true if the update succeeds
 */
function updateSheet(ar,columns,sheetName,id){
  var ss =  (id) ? SpreadsheetApp.openById(id) : SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  if (sheet == null) { 
    sheet = ss.insertSheet();
    sheet.setName(sheetName);
    return writeArrayToSheet(ar,sheet,false);
  } else {
    var arSheet = arrayFromSheet(sheet);
    var sheetHeaders = arSheet.shift();
    var arHeaders = ar.shift();
    if (sheetHeaders.length === arHeaders.length){ //shouldn't proceed if the arrays don't match
      if (arSheet.length > 0){
        for (var i=0;i<ar.length;i++){
          var properties = {};
          for (var j = 0;j < columns.length;j++){
             var index = sheetHeaders.indexOf(columns[j]);
             properties[index] = ar[i][arHeaders.indexOf(columns[j])];
          }
          Logger.log(properties);
          var matches = checkArrayForMatches(arSheet,properties);
          for (var k = matches.length-1;k>=0;k--){ //need to start at the end so indices don't change
            arSheet.splice(matches[k],1);
          };
        };
      }
      arSheet = arSheet.concat(ar);
      arSheet.sort(sortFunction2D);
      return writeArrayToSheet(arSheet,sheet,false);
    } else {
      Logger.log('updateSheet() failed, arrays dont match');
    }
  }
}


//helper function to sort 2d arrays
function sortFunction2D(a, b) {
    if (a[0] === b[0]) {
        return 0;
    }
    else {
        return (a[0] > b[0]) ? -1 : 1;
    }
}

/**
* from https://medium.com/@dkodr/how-to-keep-track-of-google-apps-scripts-total-execution-time-c46e9d1dfdef
* This function sets the new execution time as the 'runtimeCount' script property.
*/
	
function runtimeCountStop(start) {

  var props = PropertiesService.getScriptProperties();
  var currentRuntime = props.getProperty("runtimeCount");
  var stop = new Date();
  var newRuntime = Number(stop) - Number(start) + Number(currentRuntime);
  var setRuntime = {
    runtimeCount: newRuntime,
  }
  props.setProperties(setRuntime);

}

/**
* Here's how you can record the project's total execution time in a sheet.
* Set a daily time-based trigger for this function.
* After being recorder in the sheet, the property is being reset.
*/

function recordRuntime() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "Runtime";
  try {
    ss.setActiveSheet(ss.getSheetByName("Runtime"));
  } catch (e) {
    ss.insertSheet(sheetName);
  }
  var sheet = ss.getSheetByName("Runtime");
  var props = PropertiesService.getScriptProperties();
  var runtimeCount = props.getProperty("runtimeCount");
  var recordTime = new Date();

  sheet.appendRow([recordTime, runtimeCount]);
  props.deleteProperty("runtimeCount");

}
  
/**
  * Takes an array of objects or arrays, and looks for matching 
  * properties in the objects/rows
  * If objects, properties are formatted {[name]:[value],...}
  * If arrays, properties are formatted {[index]:[value],...}
  * Warning: this function is a bit of a performance hog
  * @param {array} objectArray - the array to check
  * @param {object} properties - the properties to match 
  * @return {array} - the indices of matched objects
  */
  function checkArrayForMatches(array,properties){
    var returnArray = [];
    if (Array.isArray(array[0])){
      for (var i = 0,x = array.length;i<x;i++){
        var row = array[i];
        var match = true;
        for (var j in properties){
          if (properties[j] !== row[j]){
            match = false;
          }
        }
        if (match) {returnArray.push(i)};
      }
    } else if (typeof array[0] == 'object'){
      for (var i = 0,x = array.length;i<x;i++){
        var obj = array[i];
        var match = true;
        for (var j in properties){
          if (obj[j] !== properties[j]){
            match = false;
          }
        }
        if (match) {returnArray.push(i)};
      }
    }
    return returnArray;
  }
  
  
  /**
  * Joins two arrays on specified column names. Behaves similarly
  * to a left outer join, returning rows joined on the column names,
  * but also keeping a record if there is no match
  * in the right array. If a element in the left array has the
  * value '*', it is assigned the value of the matching property 
  * in the right array.
  * @todo - support other join types
  * @param {array} ar1 - the first array
  * @param {array} ar2 - the second array
  * @param {array} params - array with the column names on which to join
  * @return {array} - the joined array
  */
  function joinArrays(ar1,ar2,params){
    var returnArray = [];
    var paramIndices = [];
    var ar1header = ar1.shift();
    var ar2header = ar2.shift();
    var returnArrayHeader = ar1header.slice(0); //this returns a copy of ar1header
    for (g = 0;g< ar2header.length;g++){
      if (ar1header.indexOf(ar2header[g]) == -1){
        returnArrayHeader.push(ar2header[g]);
      }
    }
    returnArray.push(returnArrayHeader);
    //find columns
    for (var h = 0;h<params.length;h++){
      var paramIndex = [];
      paramIndex[0] = ar1header.indexOf(params[h]);
      paramIndex[1] = ar2header.indexOf(params[h]);
      paramIndices[h] = paramIndex;
    }
    for (var i = 0,x = ar1.length;i<x;i++){
      var rowAr1 = ar1[i];
      var noMatches = true;
      var j = ar2.length; // counting down allows joined rows to be spliced
      while (j--){
        var rowAr2 = ar2[j];
        var match = true;
        for (var k = 0;k<paramIndices.length;k++){
          if (rowAr1[paramIndices[k][0]] === '*'){
            continue;
          } else if (rowAr1[paramIndices[k][0]] !== rowAr2[paramIndices[k][1]]) {
            match = false;
            break;
          }
        };
        if (match) {
          noMatches = false;
          var newRow = rowAr1.slice(0);
          for (var k = 0;k<paramIndices.length;k++){
            newRow[paramIndices[k][0]] = rowAr2[paramIndices[k][1]];
          };
          for (var l = rowAr1.length;l<returnArrayHeader.length;l++){
            newRow.push(rowAr2[ar2header.indexOf(returnArrayHeader[l])]);
          }
          ar2.splice(j,1);
          returnArray.push(newRow);
        };
      }
      if (noMatches) {
        var newRowNoMatch = rowAr1.slice(0);
        for (m = newRowNoMatch.length;m<returnArrayHeader.length;m++){
          newRowNoMatch.push('');
        };
        returnArray.push(newRowNoMatch)
      }
    }
    return returnArray;
  }
  
  /**
  * Fetches a sheet from another spreadsheet as an array
  * @param {string} spreadsheetID - ID of the spreadsheet from which to fetch data
  * @param {string} sourceSheet - name of the sheet from which to fetch data
  * @return {array} 
  */
  function fetchSheet(spreadsheetID,sourceSheet) {
    var ss =  SpreadsheetApp.getActiveSpreadsheet();
    var ssSource = SpreadsheetApp.openById(spreadsheetID);
    var sourceSheet = ssSource.getSheetByName(sourceSheet);
    var ar = [];
    if (sourceSheet) ar = sourceSheet.getDataRange().getValues();
    return ar;
  }