Create a gist now

Instantly share code, notes, and snippets.

@nicobrx /to_util
Last active Sep 7, 2017

What would you like to do?
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.
/*
updated 2017-09-02
source lives here: https://gist.github.com/nicobrx/d67b774ac86136170415/edit
standalone script ID for use as a library: 1gZ78JObyrYiH0njoZ86fQ2NgMwavUgiXVhRDrIFetPZL256e31PSNiHq
to do: add symetric array function from order mappings
make 2D array functions more consistent - for example, some can take a sheet name or sheet object as an argument, and some can only take a sheet name
Functions included here:
checkArrayForMatches(array,properties) - checks an array of arrays or objects and looks for matches to a properties arg
objectifySheet(sheet,propertyNames,newPropertyNames) - Takes a sheet with a header row and converts it into an array of objects
joinArrays(ar1,ar2,params) - similar to an inner join for arrays
arrayFromSheet(sheet,propertyNames,newPropertyNames) - creates an array from a sheet, can specify column headers
removeSlashes(arRange) - hinky function for removing slashes from a sheet
writeArrayToSheet(ar,sheet,replaceFirstRow) - writes an array to a sheet
appendArrayToSheet(ar,sheetName,id)
removeFunctionsFromCells(sheetName) - converts functions to strings, replacing = with '
getMonthString(offset) - returns a string representing a month in YYYY-MM format
deduplicateArray - removes duplicate elements from an array
fetchSheet(spreadsheetID,sourceSheet) - fetches a sheet from another spreadsheet as an array
objectFromSheet(sheet,properties) - Creates an object from a sheet, takes a heirarchy of properties as argument
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
runtimeCountStop(start) - for keeping track of script runtime
recordRuntime() - collects runtimeCountStop script properties and writes them to a sheet
*/
// sample function comment
/**
* Description
* @param {string} sheet - notes
* @param {datatype} parameter name - notes
* @return {datatype}
*/
/**
* 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;
}
/**
* Takes a sheet with a header row and converts it into an array of objects
* with property names matching column headers
* @param {string} sheet - the sheet to objectify
* @param {array} propertyNames - optional column headings to turn into properties. If not supplied,
* properties will be set to header text.
* @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;
}
/**
* 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;
}
/**
* 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 array to write
* @param {string} sheetName - the name of the sheet to which to write
*/
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")}
}
/**
* 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
*/
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;
}
/**
* 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;
}
/**
* 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(arSheet,properties){
var obj = {};
if (arSheet){
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;
}
}
/**
* Takes and 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 to_util.writeArrayToSheet(ar,sheet,false);
} else {
var arSheet = to_util.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 = to_util.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(this.sortFunction2D);
return to_util.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");
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment