/**  * Returns true if the value is in the array.  *  * @param {Array} arr  * @param {*} val  * @returns {boolean}  */ function checkValIn(arr, val) { return arr.indexOf(val) > -1; } var arr_cvi = [1, 2, 3, 4]; Logger.log(checkValIn(arr_cvi, 5)); // false

/**  * Returns an array with no duplicate values.  *  * @param {Array} arr   * @returns {Array}  */ function rmDuplicatesFrom(arr) { var check = {}; var result = []; var j = 0; for(var i = 0; i < arr.length; i++) { var item = arr[i]; if(check[item] !== 1) { check[item] = 1; result[j++] = item; } } return result; } var arr_rdf = [1, 2, 3, 1, 2, 3, 4,]; Logger.log(rmDuplicatesFrom(arr_rdf)); // [1, 2, 3, 4]

/**  * Returns an array with no empty elements.  *  * @param {*} x  * @returns {Array}  */ function rmEmptyEl(x) { return (x !== (undefined || '')); } var arr_rev = ["a",,"b",,,"c"]; Logger.log(arr_rev.filter(rmEmptyEl)); // ["a", "b", "c"]

/**  * Returns an array of objects. Objects have two properties, count and value.  *  * @param {Array} arr  * @property {value} a value found in the array  * @property {count} count of the value in the array  * @returns {Object[]}  */ function countOfValIn(arr) { var result = []; var copy = arr.slice(0); for (var i = 0; i < arr.length; i++) { var myCount = 0; for (var w = 0; w < copy.length; w++) { if (arr[i] == copy[w]) { myCount++; delete copy[w]; } } if (myCount > 0) { var obj = {}; obj.value = arr[i]; obj.count = myCount; result.push(obj); } } return result; } var arr_covi = ["a", "b", "c", "a", "b", "c", "a"]; Logger.log(countOfValIn(arr_covi)); // [{count=3.0, value=a}, {count=2.0, value=b}, {count=2.0, value=c}]

/**  * Returns an array of the elements in both arrays.  *  * @param {Array} arrA  * @param {Array} arrB  * @returns {Array}  */ function intersectOf(arrA, arrB) { var a = 0; var b = 0; var result = []; while( a < arrA.length && b < arrB.length ) { if (arrA[a] < arrB[b] ) { a++; } else if (arrA[a] > arrB[b] ) { b++; } else { result.push(arrA[a]); a++; b++; } } return result; } var arrA_io = [1, 2, 3]; var arrB_io = [3, 4, 5]; Logger.log(intersectOf(arrA_io, arrB_io)); // [3]

/**  * Returns true if both arrays have the same elements in the same order.  *  * @param {Array} arrA  * @param {Array} arrB  * @returns {boolean}  */ function compareArr(arrA, arrB) { if(arrA.length !== arrB.length) return false; for(var i = arrA.length; i--;) { if(arrA[i] !== arrB[i]) return false; } return true; } var arrA_ca = [1, 2, 3, 4, 5]; var arrB_ca = [1, 2, 3, 4, 5]; var arrC_ca = ["a", "b", "c", "d", "e"]; Logger.log(compareArr(arrA_ca, arrB_ca)); // true Logger.log(compareArr(arrA_ca, arrC_ca)); // false

/**  * Returns a string of array values.   * Elements are separated by a delimiter and a space.  *  * @param {Array} arr  * @param {string} delim  * @returns {string}  */ function delimStrFromArr(arr, delim) { var _arr = rmDuplicatesFrom(arr).sort(); var result = ""; for (var i = 0; i < _arr.length; i++) { result += _arr[i] + delim + " "; } result = result.slice(0, -2); return result; } var arr_da = ["c@example.com", "b@example.com", "a@example.com"]; Logger.log(delimStrFromArr(arr_da, ",")); // "a@example.com, b@example.com, c@example.com"

/**  * Returns a string of array values.  * Elements are separated by a delimiter and a space, each followed by a modification.  *  * @param {Array} arr  * @param {string} delim  * @param {string} mod Modification to append to each item in the array.  * @returns {string}  */ function delimStrFromArrMod(arr, delim, mod) { var _arr = rmDuplicatesFrom(arr).sort(); var result = ""; for (var i = 0; i < _arr.length; i++) { result += _arr[i] + mod + delim + " "; } result = result.slice(0, -2); return result; } var arr_clfd = ["x", "z", "y"]; Logger.log(delimStrFromArrMod(arr_clfd, ",", "@example.com")); // "x@example.com, y@example.com, z@example.com"

/**  * Returns an array containing all values in a two-dimensional array.  *  * @param {Array[]} twoDArr  * @returns {Array}   */ function flattenTwoDArr(twoDArr) { var result = twoDArr.reduce(function(a, b) { return a.concat(b); }); return result; } var sheet_fma = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var val_fma = sheet_fma.getRange("G2:H5").getValues(); Logger.log(flattenTwoDArr(val_fma).sort()); // [1, 2, 3, 4, 5, 6, 7, 8]

var ex_arrObj = [ {a: 1000, b: 1, c: 5}, {a: 10000, b: 2, c: 5000}, {a: 10, b: 2, c: 500}, {a: 1, b: 1, c: 50} ]

/**  * Returns an array of objects sorted by a single property value.  *  * @param {string} prop  * @returns {Object[]}  */ function dynSort(prop) { var sortOrder = 1; if(prop[0] === "-") { sortOrder = -1; prop = prop.substr(1); } return function (a,b) { var result = (a[prop] < b[prop]) ? -1 : (a[prop] > b[prop]) ? 1 : 0; return result * sortOrder; }; } Logger.log(ex_arrObj.sort(dynSort("a"))); // [{a=1.0, b=1.0, c=50.0}, {a=10.0, b=2.0, c=500.0}, {a=1000.0, b=1.0, c=5.0}, {a=10000.0, b=2.0, c=5000.0}] /**  * Returns an array of objects sorted by multiple property values.  * @param {...string}  * @returns {Object[]}  */ function dynSortM() { var props = arguments; return function (obj1, obj2) { var i = 0, result = 0, numberOfProperties = props.length; while(result === 0 && i < numberOfProperties) { result = dynSort(props[i])(obj1, obj2); i++; } return result; }; } Logger.log(ex_/rrObj.sort(dynSortM("b", "c"))); // [{a=1000.0, b=1.0, c=5.0}, {a=1.0, b=1.0, c=50.0}, {a=10.0, b=2.0, c=500.0}, {a=10000.0, b=2.0, c=5000.0}]

/**  * Returns the first object in an array of objects with the key value pair.  *  * @param {Object[]} arrObj  * @param {string} pQuery  * @param {string} val  * @returns {Object}  */ function findObjIn(arrObj, pQuery, val) { for (var i = 0; i < arrObj.length; i++) { var obj = arrObj[i]; for (var prop in obj) { if (obj.hasOwnProperty(pQuery) && prop == pQuery && obj[prop] == val) { return obj; } } } } Logger.log(findObjIn(ex_arrObj,"a",1000)); // {a=1000.0, b=1.0, c=5.0} /**  * Returns a value from the first matching object in the array.  *  * @param {Object[]} arrObj  * @param {string} pQuery  * @param {string} val  * @param {string} pReturn  * @returns {*}  */ function findObjValIn(arrObj, pQuery, val, pReturn) { for (var i = 0; i < arrObj.length; i++) { var obj = arrObj[i]; for (var prop in obj) { if (obj.hasOwnProperty(pQuery) && prop == pQuery && obj[prop] == val) { return obj[pReturn]; } } } } Logger.log(findObjValIn(ex_arrObj, "c", 500, "a")); // 10

/**  * Returns the object with the oldest Timestamp value.  *  * @param {Object[]} arrObj  * @returns {Object}  */ function earliestTS(arrObj){ if (arrObj.length >= 2) { var sorted = arrObj.sort(function(a,b){ return new Date(a.Timestamp) - new Date(b.Timestamp); }); return sorted[0]; } else { return arrObj[0]; } } var sheet_fe = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var arrObj_fe = arrObjFromRange(sheet_fe, "J1:K4"); Logger.log(earliestTS(arrObj_fe)); // {Timestamp=Sun Feb 19 19:43:40 GMT-06:00 2017, Multiple Choice=A} /**  * Returns the object with the latest Timestamp value.  *  * @param {Object[]} arrObj  * @returns {Object}  */ function latestTS(arrObj) { if (arrObj.length >= 2) { var sorted = arrObj.sort(function(a,b){ return new Date(b.Timestamp) - new Date(a.Timestamp); }); return sorted[0]; } else { return arrObj[0]; } } var sheet_le = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); var arrObj_le = arrObjFromRange(sheet_le, "J1:K4"); Logger.log(latestTS(arrObj_le)); // {Timestamp=Wed Feb 22 19:45:07 GMT-06:00 2017, Multiple Choice=C}

/**  * Returns an array of objects containing matching objects.  *  * @param {Object} arrObj  * @param {string} pQuery  * @param {string[]} arrVal  * @returns {Object[]}  */ function filterObjIn(arrObj, pQuery, arrVal) { var result = []; for (var i = 0; i < arrVal.length; i++) { var val = arrVal[i]; for (var j = 0; j < arrObj.length; j++) { if (arrObj[j][pQuery] == val) result.push(arrObj[j]); } } return result; } Logger.log(filterObjIn(ex_arrObj, "a", [10])); // [{a=10.0, b=2.0, c=500.0}] Logger.log(filterObjIn(ex_arrObj, "c", [5, 500])); // [{a=1000.0, b=1.0, c=5.0}, {a=10.0, b=2.0, c=500.0}]

/**  * Returns an array of objects, with an additional property value added to each matching object.  *  * @param {Object[]} arrObj  * @param {string[]} arrProp  * @param {string} newProp   * @returns {Object[]}  */ function unifyPropForArrObj(arrObj, arrProp, newProp){ for (var i = 0; i < arrObj.length; i++){ var obj = arrObj[i]; for (var h = 0; h < arrProp.length; h++) { for (var prop in obj) { if (obj.hasOwnProperty(prop) && prop == arrProp[h] && obj[prop] !== ""){ obj[newProp] = obj[prop]; } } } } return arrObj; } var arrObj_upfao = [ {x: 123}, {y: 234}, {z: 345}, ]; Logger.log(unifyPropForArrObj(arrObj_upfao, ["x","y","z"], "new")); // [{new=123.0, x=123.0}, {new=234.0, y=234.0}, {new=345.0, z=345.0}]

/**  * Returns an array of matching properties.   *  * @requires intersectOf()   * @param {Object} obj  * @param {string[]} props  * @returns {Array}  */ function filterValIn(obj, props) { var result = []; var keys = intersectOf(Object.keys(obj), props); for (var i = 0; i < keys.length; i++) { var key = keys[i]; for (var prop in obj) { if (obj.hasOwnProperty(key)) { result.push(obj[key]); break; } } } return result; } var obj_fvi = { a: 1, b: 2, c: 3 }; var arr_fvi = ["a", "b", "d"]; Logger.log(filterValIn(obj_fvi, arr_fvi)); // [1, 2]

/**  * Returns an object with the values of the argument objects.  * If multiple objects have the same property value, the last value set is retained.   * @param {...Object}  * @returns {Object}  */ function mergeObjs() { var obj = arguments[0]; for (i = 1; i < arguments.length; i++) { var src = arguments[i]; for (var key in src) { if (src.hasOwnProperty(key)) obj[key] = src[key]; } } return obj; } var objA_mo = { a: 1, b: 2, c: 3 }; var objB_mo = { c: 4, d: 5, e: 6, f: 7 }; Logger.log(mergeObjs(objA_mo, objB_mo)); // {a=1.0, b=2.0, c=4.0, d=5.0, e=6.0, f=7.0}

/**  * Returns a string of today's date formatted "month-day-year".  *  * @returns {string}  */ function fmatD() { var n = new Date(); var d = [ n.getMonth() + 1, n.getDate(), n.getYear() ]; return d.join("-"); } Logger.log(fmatD()); // "4-24-2017" /**  * Returns a string of the current time formatted "hour:minute:second".  *  * @returns {string}  */ function fmat24T(){ var n = new Date(); var t = [ n.getHours(), n.getMinutes(), n.getSeconds() ]; for ( var i = 1; i < 3; i++ ) { if ( t[i] < 10 ) { t[i] = "0" + t[i]; } return t.join(":"); } } Logger.log(fmat24T()); // "20:43:40" /**  * Returns a string of today's date and the current time formatted "month-day-year hour:minute:second AM/PM"  *  * @returns {string}  */ function fmat12DT() { var n = new Date(); var d = [ n.getMonth() + 1, n.getDate(), n.getYear() ]; var t = [ n.getHours(), n.getMinutes(), n.getSeconds() ]; var s = ( t[0] < 12 ) ? "AM" : "PM"; t[0] = ( t[0] <= 12 ) ? t[0] : t[0] - 12; for ( var i = 1; i < 3; i++ ) { if ( t[i] < 10 ) { t[i] = "0" + t[i]; } } return d.join("/") + " " + t.join(":") + " " + s; } Logger.log(fmat12DT()); // "4-24-2017 8:43:40 PM"

/**  * Returns a new date object from a string formatted year-month-date.   *  * @param {string} str  * @returns {Date}  */ function dateObjectFromString(str) { var split = str.split("-"); var months = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" ]; return new Date (months[(split[1] - 1)] + " " + split[2] + ", " + split[0]); } Logger.log(dateObjectFromString("2017-04-24")); // Mon Apr 24 00:00:00 GMT-05:00 2017

/**  * Returns a value associated with a date range.  *  * @param {Object[]} arrObj  * @param {string=new Date()} optDate - Date to match.  * @namespace  * @property {string} start - Starting date.  * @property {string} end - Ending date.  * @property {*} value - The value to return for a matching date.  * @returns {*}  */ function matchDateRange(arrObj, optDate) { var date = new Date(); if (optDate !== undefined) { date = new Date(optDate); } for (i = 0; i < arrObj.length; i++) { var start = new Date(arrObj[i].start); var end = new Date(arrObj[i].end); if (date >= start && date <= end ) { return arrObj[i].value; } } } var quarterDates = [ {start: "08/01/2016", end: "10/28/2016", value: 1}, {start: "11/02/2016", end: "01/09/2017", value: 2}, {start: "01/15/2017", end: "03/19/2017", value: 3}, {start: "03/21/2017", end: "06/15/2017", value: 4}, {start: "06/16/2017", end: "07/30/2017", value: "summer vacation"} ]; Logger.log(matchDateRange(quarterDates)); // "summer vacation" (06/25/2017) Logger.log(matchDateRange(quarterDates, "08/02/2016")); // 1 

// -- Create or Verify Folder Path /**  * Returns a folder at the end of a folder path.  * The folder is created if it does not exist already.  *  * @param {string} path  * @returns {Folder}  */ function createVerifyPath(path) { var split = path.split('/'); var fldr; for (i = 0; i < split.length; i++) { var fi = DriveApp.getRootFolder().getFoldersByName(split[i]); if (i === 0) { if(!(fi.hasNext())) { DriveApp.createFolder(split[i]); fi = DriveApp.getFoldersByName(split[i]); } fldr = fi.next(); } else if (i >= 1) { fi = fldr.getFoldersByName(split[i]); if(!(fi.hasNext())) { fldr.createFolder(split[i]); fi = DriveApp.getFoldersByName(split[i]); } fldr = fi.next(); } } return fldr; } Logger.log(createVerifyPath("google-apps-script-cheat-sheet-demo/folders/A/B/C")); // C

/**  * Returns the last folder in a folder path.  *  * @param path  * @returns {Folder}  */ function lastFolderIn(path) { var fi; var split = path.split('/'); var fldr; for (i = 0; i < split.length; i++) { if (i === 0) { fi = DriveApp.getRootFolder().getFoldersByName(split[i]); if (fi.hasNext()) { fldr = fi.next(); } } else if (i >= 1) { fi = fldr.getFoldersByName(split[i]); if (fi.hasNext()) { fldr = fi.next(); } } } return fldr; } // Logger.log(lastFolderIn("google-apps-script-cheat-sheet-demo/folders/A/B")); // B // Logger.log(lastFolderIn("google-apps-script-cheat-sheet-demo/folders/A/B/C/D/E/F/G")); // C

/**  * Returns an array of all folders in a folder.  *  * @param {Folder} fldr  * @returns {Folder[]}  */ function foldersIn(fldr) { var fi = fldr.getFolders(); var arr = []; while (fi.hasNext()) { var _fldr = fi.next(); arr.push(_fldr); } return arr; } Logger.log(foldersIn(lastFolderIn("google-apps-script-cheat-sheet-demo/folders/"))); // [A]

/**  * Returns an array of all folders in the root of the user's Drive.  *  * @returns {Folder[]}  */ function rootFolders() { var rf = DriveApp.getRootFolder(); var fi = rf.getFolders(); var arr = []; while (fi.hasNext()) { var fldr = fi.next(); arr.push(fldr); } return arr; } Logger.log(rootFolders());

/**  * Returns an array of all folders in the user's Drive.  *  * @returns {Folder[]}  */ function allFolders() { var fi = DriveApp.getFolders(); var arr = []; while (fi.hasNext()) { var fldr = fi.next(); arr.push(fldr); } return arr; } Logger.log(allFolders());

/**  * Returns an array of folder names.  *  * @param {Folders[]}  * @returns {string[]}  */ function folderNames(fldrs) { var arr = []; for (var i = 0; i < fldrs.length; i++) { var name = fldrs[i].getName(); arr.push(name); } return arr; } var arr_fn = foldersIn(lastFolderIn("google-apps-script-cheat-sheet-demo/folders/A/B")); Logger.log(folderNames(arr_fn)); // [C]

/**  * Returns a folder.  *  * @requires foldersIn()   * @requires folderNames()   * @requires checkValIn()  * @param {Folder} fldr  * @param {string} name  * @returns {Folder}  */ function findFolderIn(fldr, name) { var fldrs = foldersIn(fldr); var names = folderNames(fldrs); if (checkValIn(names, name)) { var _fldr = fldr.getFoldersByName(name).next(); return _fldr; } } var fldr_ffi = lastFolderIn("google-apps-script-cheat-sheet-demo/folders"); Logger.log(findFolderIn(fldr_ffi, "A")); // A

/**  * Returns a folder at the root of the user's Drive.  *  * @requires rootFolders()  * @requires folderNames()  * @requires checkValIn()  * @param {string} name  * @returns {Folder}  */ function findFolderAtRoot(name) { var rf = DriveApp.getRootFolder(); var fldrs = rootFolders(); var names = folderNames(fldrs); if (checkValIn(names, name)) { var fldr = rf.getFoldersByName(name).next(); return fldr; } } Logger.log(findFolderAtRoot("google-apps-script-cheat-sheet-demo")); // google-apps-script-cheat-sheet-demo

/**  * Returns the first matching folder in Drive.  *  * @param {string} name  * @returns {Folder}  */ function findFolderInDrive(name) { var fi = DriveApp.getFoldersByName(name); while (fi.hasNext()){ var fldr = fi.next(); return fldr; } } Logger.log(findFolderInDrive("folders")); // folders

/**  * Returns a folder.   * Creates folders within a folder if they don't already exist.  *  * @requires foldersIn()  * @requires folderNames()  * @requires checkValIn()  * @param {Folder} fldr  * @param {string[]} names  * @returns {Folder}  */ function createVerifyFoldersIn(fldr, names) { var fldrs = foldersIn(fldr); var _names = folderNames(fldrs); for (i = 0; i < names.length; i++) { if (!(checkValIn(_names, names[i]))) { fldr.createFolder(names[i]); } } return fldr; } var fldr_cvfi = lastFolderIn("google-apps-script-cheat-sheet-demo/folders"); Logger.log(createVerifyFoldersIn(fldr_cvfi, ["X", "Y", "Z"])); // folders Logger.log(foldersIn(fldr_cvfi)); // [A,X,Y,Z]

/**  * Returns an array of files found at the top level of a folder.  *  * @param {Folder} fldr  * @returns {File[]}  */ function filesIn(fldr) { var fi = fldr.getFiles(); var arr = []; while (fi.hasNext()) { var file = fi.next(); arr.push(file); } return arr; } var fldr_fin = lastFolderIn("google-apps-script-cheat-sheet-demo/files"); Logger.log(filesIn(fldr_fin)); // [example-file]

/**  * Returns an array of all files at the root of a user's Drive.  *  * @returns {File[]}  */ function rootFiles() { var rf = DriveApp.getRootFolder(); var fi = rf.getFiles(); var arr = []; while (fi.hasNext()) { var file = fi.next(); arr.push(file); } return arr; } Logger.log(rootFiles());

/**  * Returns an array of all files in the user's Drive.  *  * @returns {File[]}  */ function allFiles() { var fi = DriveApp.getFiles(); var arr = []; while (fi.hasNext()) { var file = fi.next(); arr.push(file); } return arr; } Logger.log(allFiles());

/**  * Returns an array of file names.  *  * @param {File[]} files  * @returns {string[]}  */ function fileNames(files) { var arr = []; for (var i = 0; i < files.length; i++) { var name = files[i].getName(); arr.push(name); } return arr; } var fldr_fnam = lastFolderIn("google-apps-script-cheat-sheet-demo/files"); var arr_fnam = filesIn(fldr_fnam); Logger.log(fileNames(arr_fnam)); // [example-file]

/**  * Returns a file found at the top level of a folder.   *  * @requires filesIn()  * @requires fileNames()  * @requires checkValIn()  * @param {Folder} fldr  * @param {string} name  * @returns {File}  */ function findFileIn(fldr, name) { var files = filesIn(fldr); var names = fileNames(files); if (checkValIn(names, name)) { var file = fldr.getFilesByName(name).next(); return file; } } var fldr_ffi = lastFolderIn("google-apps-script-cheat-sheet-demo/files"); Logger.log(findFileIn(fldr_ffi, "example-file")); // example-file

/**  * Returns a file found at the root of a user's Drive.  *  * @requires rootFiles()  * @requires fileNames()  * @requires checkValIn()  * @param {string} name  * @returns {File}  */ function findFileAtRoot(name) { var rf = DriveApp.getRootFolder(); var files = rootFiles(); var names = fileNames(files); if (checkValIn(names, name)) { var file = rf.getFilesByName(name).next(); return file; } }

/**  * Returns the first matching file found in the user's Drive.  *  * @param {string} name  * @returns {File}  */ function findFileInDrive(name) { var fi = DriveApp.getFilesByName(name); while (fi.hasNext()){ var file = fi.next(); return file; } } Logger.log(findFileInDrive("example-file")); // example-file

/**  * Returns the file found at the end of a path.  *  * @param {string} path  * @returns {File}  */ function findFileAtPath(path) { var fi; var split = path.split('/'); var file = split[split.length -1]; var fldr; for (i = 0; i < split.length - 1; i++) { if (i === 0) { fi = DriveApp.getRootFolder().getFoldersByName(split[i]); if (fi.hasNext()) { fldr = fi.next(); } else { return null; } } else if (i >= 1) { fi = fldr.getFoldersByName(split[i]); if (fi.hasNext()) { fldr = fi.next(); } else { return null; } } } return findFileIn(fldr, file); } Logger.log(findFileAtPath("google-apps-script-cheat-sheet-demo/files/example-file"));

/**  * Returns the copied file.  *  * @requires findFileIn()  * @param {File} file  * @param {Folder} fldr  * @returns {File}  */ function copyFile(file, fldr) { var name = file.getName(); var dest = findFileIn(fldr, name); if (dest === undefined) file.makeCopy(name, fldr); return findFileIn(fldr, name); } var fldr_cf = createVerifyPath("google-apps-script-cheat-sheet-demo/files/copied"); var file_cf = findFileInDrive("example-file"); Logger.log(copyFile(file_cf, fldr_cf)); // example-file

/**  * Returns the copied file from its new destination.  *  * @requires findFileIn()  * @param {File} file  * @param {Folder} fldr  * @returns {File}  */ function moveFile(file, fldr) { var name = file.getName(); var dest = findFileIn(fldr, name); if (dest === undefined) file.makeCopy(name, fldr); var _file = findFileIn(fldr, name); if (_file !== undefined) file.setTrashed(true); return _file; } var fldr_mf1 = lastFolderIn("google-apps-script-cheat-sheet-demo/files/copied"); var file_mf = findFileIn(fldr_mf1, "example-file"); var fldr_mf2 = createVerifyPath("google-apps-script-cheat-sheet-demo/files/moved"); Logger.log(moveFile(file_mf, fldr_mf2)); // example-file

/**  * Returns a renamed file or a folder.  *  * @param {File || Folder} file_fldr  * @param {string} name  * @returns {File || Folder}  */ function renameFileFldr(file_fldr, name) { file_fldr.setName(name); return file_fldr; } var fldr_rf = lastFolderIn("google-apps-script-cheat-sheet-demo/files/moved"); var file_rf = findFileIn(fldr_rf, "example-file"); Logger.log(renameFileFldr(file_rf, "modified-example-file")); // modified-example-file

/**  * Returns the parent folder or a file or a folder.  *  * @param {File || Folder} file_fldr  * @returns {Folder}  */ function parentFolderOf(file_fldr) { var fi = file_fldr.getParents(); return fi.next(); } var file_pfo = findFileInDrive("example-file"); Logger.log(parentFolderOf(file_pfo)); // files

function jsonExFile() { var fldr = createVerifyPath("google-apps-script-cheat-sheet-demo/json"); var file = findFileIn(fldr, "example-json"); var json = jsonFromUrl("https://raw.githubusercontent.com/jcodesmn/google-apps-script-cheat-sheet/dev/example.json"); var text = JSON.stringify(json); if (!(file)){fldr.createFile("example-json");} file.setContent(text); return findFileIn(fldr, "example-json"); } jsonExFile();

/**  * Returns an object from a URL.  *  * @param {string} url  * @returns {Object}  */ function objFromUrl(url) { var rsp = UrlFetchApp.fetch(url); var data = rsp.getContentText(); return JSON.parse(data); } var obj_ofu = objFromUrl("https://raw.githubusercontent.com/jcodesmn/google-apps-script-cheat-sheet/dev/example.json"); Logger.log(JSON.stringify(obj_ofu));

/**  * Returns an object from a file in Drive.  *  * @param {File} file  * @returns {Object}  */ function objFromFile(file) { var data = file.getBlob().getDataAsString(); return JSON.parse(data); } var file_off = findFileAtPath("google-apps-script-cheat-sheet-demo/json/example-json"); var obj_off = objFromFile(file_off); Logger.log(JSON.stringify(obj_off));

/**  * Returns an object from a URL or from a file in Drive.  *  * @param {string || File} input  * @returns {Object}  */ function objFromUrlOrFile(input) { var regExp = new RegExp("^(http|https)://"); var test = regExp.test(input); if (test) { return objFromUrl(input); } else { var file = findFileAtPath(input); return objFromFile(file); } } Logger.log(JSON.stringify(objFromUrlOrFile("https://raw.githubusercontent.com/jcodesmn/google-apps-script-cheat-sheet/dev/example.json"))); Logger.log(JSON.stringify(objFromUrlOrFile("google-apps-script-cheat-sheet-demo/json/example-json")));

/**  * Returns a spreadsheet.   * This creates the spreadsheet if it does not already exist.  *  * @requires filesIn()  * @requires fileNames()  * @requires checkValIn  * @requires moveFile()  * @requires findFileIn()  * @requires openFileAsSpreadsheet()  * @param {Folder} fldr  * @param {string} name  * @returns {Spreadsheet}  */ function createVerifySSIn(fldr, name) { var files = filesIn(fldr); var names = fileNames(files); if (!(checkValIn(names, name))) { var ss = SpreadsheetApp.create(name).getId(); var file = DriveApp.getFileById(ss); moveFile(file, fldr); } return openFileAsSpreadsheet(findFileIn(fldr, name)); } var fldr_cvssi = createVerifyPath("google-apps-script-cheat-sheet-demo/sheets"); Logger.log(createVerifySSIn(fldr_cvssi, "example-sheet")); // example-sheet

/**  * Returns a spreadsheet.   * This creates the spreadsheet if it does not already exist.  *  * @requires rootFiles()  * @requires fileNames()  * @requires checkValIn()   * @requires findFileAtRoot()   * @requires openFileAsSpreadsheet()   * @param {string} name  * @returns {Spreadsheet}  */ function createVerifySSAtRoot(name) { var files = rootFiles(); var names = fileNames(files); if (!(checkValIn(names, name))) { var ss = SpreadsheetApp.create(name); } return openFileAsSpreadsheet(findFileAtRoot(name)); }

/**  * Returns the Id of the active spreadsheet.  *  * @returns {string}  */ function ssId() { var _id = SpreadsheetApp.getActiveSpreadsheet().getId(); return _id; } Logger.log(ssId());

/**  * Returns the column number as a alphabetical column value.  * Columns are indexed from 1, not from 0.  * "CZ" (104) is the highest supported value.  *  * @param {number} number  * @returns {string}  */ function numCol(number) { var num = number - 1, chr; if (num <= 25) { chr = String.fromCharCode(97 + num).toUpperCase(); return chr; } else if (num >= 26 && num <= 51) { num -= 26; chr = String.fromCharCode(97 + num).toUpperCase(); return "A" + chr; } else if (num >= 52 && num <= 77) { num -= 52; chr = String.fromCharCode(97 + num).toUpperCase(); return "B" + chr; } else if (num >= 78 && num <= 103) { num -= 78; chr = String.fromCharCode(97 + num).toUpperCase(); return "C" + chr; } } function ex_nc() { for (var i = 1; i <= 104; i++) { var j = numCol(i); Logger.log(i + " - " + j); } } ex_nc(); // 1 - A ... CZ - 104

/**  * Returns an alphabetical column value as a number.  *  * @param {string} column  * @returns {number}  */ function colNum(column) { var col = column.toUpperCase(), chr0, chr1; if (col.length === 1) { chr0 = col.charCodeAt(0) - 64; return chr0; } else if (col.length === 2) { chr0 = (col.charCodeAt(0) - 64) * 26; chr1 = col.charCodeAt(1) - 64; return chr0 + chr1; } } function ex_cn() { for (var i = 0; i <= 25; i++) { var abc = String.fromCharCode(97 + i).toUpperCase(); Logger.log(abc + " - " + colNum(abc)); } for (var i = 26; i <= 51; i++) { var abc = "A" + String.fromCharCode(97 - 26 + i).toUpperCase(); Logger.log(abc + " - " + colNum(abc)); } } ex_cn(); // A - 1 ... AZ - 52

/**  * Replicating import range in Google Apps Script.  * Requires a trigger to function.  * importRange : From spreadsheet : On edit  *  */ function importRange(){ var get = sheet_gs.getRange("A2:A5").getValues(); var set = sheet_gs.getRange("B2:B5").setValues(get); }

/**  * Returns true or false given truthy or falsy values.  * true: 1, t*, T*, y*, Y*  * false: 0, !t, || !y  *  * @param {string} input  * @returns {boolean}  */ function checkTF(input) { if (isNaN(input)) { var first_letter = input.charAt(0).toLowerCase(); if (first_letter === 't' || first_letter === 'y') { return true; } else { return false; } } else { if (input === 1) { return true; } else { return false; } } } Logger.log(checkTF("No")); // false Logger.log(checkTF("Yes")); // true

/**  * Returns an array of the sheet names for a spreadsheet.  *  * @param {Spreadsheet} ss  * @returns {string[]}  */ function arrSheetNames(ss) { var sheets = ss.getSheets(); var arr = []; for (var i = 0; i < sheets.length; i++) { arr.push(sheets[i].getName()); } return arr; } var ss_asn = SpreadsheetApp.getActiveSpreadsheet(); Logger.log(arrSheetNames(ss_asn)); // ["Sheet1", "Sheet2", "Sheet3"]

/**  * Returns an object from a range.  * The top row of the range is assumed to be the header row.  * Values in the header row become the object properties.  *  * @param {Sheet} sheet  * @param {string} a1Notation  * @returns {Object}  */ function objFromRange(sheet, a1Notation) { var range = sheet.getRange(a1Notation); var height = range.getHeight(); var width = range.getWidth(); var values = range.getValues(); var obj = {}; for (var i = 0; i < values.length; i++) { obj[values[i][0]] = values[i][1]; } return obj; } var sheet_ofr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); Logger.log(objFromRange(sheet_ofr, "D2:E5")); // {A=Alpha, B=Bravo, C=Charlie, D=Delta}

/**  * Returns an array of values for the top row of a range object.  *  * @param {Range} rangeObj  * @returns {Array}  */ function headerVal(rangeObj){ var vals = rangeObj.getValues(); var arr = []; for (var i = 0; i < vals[0].length; i++) { var val = vals[0][i]; arr.push(val); } return arr; } var sheet_hv = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2"); var range_hv = sheet_hv.getRange("A2:E19"); Logger.log(headerVal(range_hv)); // ["First", "Last", "Grade", "Homeroom", "Email"]

/**  * Returns an array of objects representing a range.  *  * @param {Range} rangeObj  * @param {Array} headers  * @returns {Object[]}  */ function valByRow(rangeObj, headers){ var height = rangeObj.getHeight(); var width = rangeObj.getWidth(); var vals = rangeObj.getValues(); var arr = []; for (var i = 0; i < height; i++) { var row = {}; for (var j = 0; j < width; j++) { var prop = headers[j]; var val = vals[i][j]; if (val !== "") { row[prop] = val; } } arr.push(row); } return arr; } var sheet_vbr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2"); var range_vbr = sheet_hv.getRange("A2:E19"); var headers_vbr = headerVal(range_vbr); Logger.log(valByRow(range_vbr, headers_vbr)); // [{Last=Last, Email=Email, Homeroom=Homeroom, Grade=Grade, First=First}, {Last=Garret, Email=agarret@example.com, Homeroom=Muhsina, Grade=6.0, First=Arienne}...]

/**  * Returns the header range for a targeted range.  *  * @param {Sheet} sheet  * @param {string} a1Notation  * @returns {Range}  */ function headerRange(sheet, a1Notation) { var split = a1Notation.split(":"); var col0 = split[0].match(/\D/g,''); var col1 = split[1].match(/\D/g,''); var row = split[0].match(/\d+/g); var a1 = col0 + row + ":" + col1 + row; return sheet.getRange(a1); } var sheet_hr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2"); Logger.log(headerRange(sheet_hr, "A2:E19").getA1Notation()); // "A2:E2" Logger.log(headerRange(sheet_hr, "A2:E19").getValues()); // [[First, Last, Grade, Homeroom, Email]]

/**  * Returns the value range for a targeted range.   *  * @param {Sheet} sheet  * @param {string} a1Notation  * @returns {Range}  */ function valueRange(sheet, a1Notation) { var split = a1Notation.split(":"); var col0 = split[0].match(/\D/g,''); var row0 = split[0].match(/\d+/g); var col1 = split[1].match(/\D/g,''); var row1 = split[1].match(/\d+/g); var a1 = col0 + (Number(row0) + 1) + ":" + col1 + row1; return sheet.getRange(a1); } var sheet_vr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2"); Logger.log(valueRange(sheet_vr, "A2:E19").getA1Notation()); // "A3:E19" Logger.log(valueRange(sheet_vr, "A2:E19").getValues()); // [[Arienne, Garret, 6.0, Muhsina, agarret@example.com], [Elissa, Jules, 6.0, Lale, ejules@example.com]...]