- Folders
- Files
- Files and Folders
- Sheets and Docs
/**
* 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 = ["[email protected]", "[email protected]", "[email protected]"];
Logger.log(delimStrFromArr(arr_da, ",")); // "[email protected], [email protected], [email protected]"/**
* 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")); // "[email protected], [email protected], [email protected]"/**
* 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 the root folder.
* Creates folders at root if they don't exist already.
*
* @param {string[]} names
* @returns {Folder}
*/
function createVerifyFoldersAtRoot(names) {
var rfs = rootFolders();
var _names = folderNames(rfs);
for (i=0; i < names.length; i++) {
if (!(checkValIn(_names, names[i]))) {
DriveApp.createFolder(names[i]);
}
}
return DriveApp.getRootFolder();
}
/**
* 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)); // filesfunction 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 a spreadsheet.
*
* @param {string}
* @returns {Spreadsheet}
*/
function openFileAsSpreadsheet(file) {
var _id = file.getId();
var _ss = SpreadsheetApp.openById(_id);
return _ss;
}
var fldr_ofas = lastFolderIn("google-apps-script-cheat-sheet-demo/sheets")
var file_ofas = findFileIn(fldr_ofas, "example-sheet");
Logger.log(openFileAsSpreadsheet(file_ofas));
/**
* 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 protected], 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, [email protected]], [Elissa, Jules, 6.0, Lale, [email protected]]...]/**
* Returns an array of objects representing the values in a sheet.
*
* @requires numCol()
* @requires headerVal()
* @requires valByRow()
* @param sheet
* @param hRow
* @returns {undefined}
*/
function arrObjFromSheet(sheet, hRow){
var lColNum = sheet.getLastColumn();
var lColABC = numCol(lColNum);
var lRow = sheet.getLastRow();
var hRange = sheet.getRange("A" + hRow + ":" + lColABC + hRow);
var headers = headerVal(hRange);
var vRange = sheet.getRange("A" + (hRow +1) + ":" + lColABC + lRow);
return valByRow(vRange, headers);
}
var sheet_aofs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
Logger.log(arrObjFromSheet(sheet_aofs, 2)); // [{Last=Garret, [email protected], Homeroom=Muhsina, Grade=6.0, First=Arienne}, {Last=Jules, [email protected], Homeroom=Lale, Grade=6.0, First=Elissa}...]/**
* Returns an array of values representing the values in a range.
*
* @requires headerRange()
* @requires valueRange()
* @requires headerVal()
* @requires valByRow()
* @param sheet
* @param a1Notation
* @returns {undefined}
*/
function arrObjFromRange(sheet, a1Notation) {
var hRange = headerRange(sheet, a1Notation);
var vRange = valueRange(sheet, a1Notation);
var headers = headerVal(hRange);
return valByRow(vRange, headers);
}
var sheet_aofr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
Logger.log(arrObjFromRange(sheet_aofr, "A2:E7")); // [{Last=Garret, [email protected], Homeroom=Muhsina, Grade=6.0, First=Arienne}, {Last=Jules, [email protected], Homeroom=Lale, Grade=6.0, First=Elissa}...]/**
* Returns an array containing all values in a column.
*
* @param {Sheet} sheet
* @param {number} hRow
* @param {string} name
* @returns {Array}
*/
function arrForColName(sheet, hRow, name){
var lColNum = sheet.getLastColumn();
var lColABC = numCol(lColNum);
var lRow = sheet.getLastRow();
var hRange = sheet.getRange("A" + hRow + ":" + lColABC + hRow);
var headers = headerVal(hRange);
var tColABC = numCol(headers.indexOf(name) + 1);
var rangeObj = sheet.getRange(tColABC + (hRow +1) + ":" + tColABC + lRow);
var height = rangeObj.getHeight();
var vals = rangeObj.getValues();
var arr = [];
for (var i = 0; i < height; i++) {
var val = vals[i][0];
arr.push(String(val));
}
return arr;
}
var sheet_afcna = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
Logger.log(arrForColName(sheet_afcna, 2, "First")); // [Arienne, Elissa, Nerses, Gülistan, Syed, Isaiah, Stanley, Sára, Kaja, Józef, Radoslava, Sarah, Oluwasegun, Ekundayo, Gina, Sylvia, Cemil]/**
* Returns an array containing all values in a column.
*
* @param {Sheet} sheet
* @param {number} hRow
* @param {number} colIndex
* @returns {Array}
*/
function arrForColNo(sheet, hRow, colIndex){
var lColNum = sheet.getLastColumn();
var lColABC = numCol(lColNum);
var lRow = sheet.getLastRow();
var hRange = sheet.getRange("A" + hRow + ":" + lColABC + hRow);
var tColABC = numCol(colIndex);
var rangeObj = sheet.getRange(tColABC + (hRow +1) + ":" + tColABC + lRow);
var height = rangeObj.getHeight();
var vals = rangeObj.getValues();
var arr = [];
for (var i = 0; i < height; i++) {
var val = vals[i][0];
arr.push(String(val));
}
return arr;
}
var sheet_afcno = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
Logger.log(arrForColNo(sheet_afcno, 2, 2)); // [Garret, Jules, Juda, Armen, Yeong-Suk, Coy, Stevie, Emin, Tiriaq, Dilay, Kirabo, Ariadna, Devrim, Adjoa, Suk, Lyle, Edita]/**
* Returns an array containing all values in the first column of a range.
*
* @param {Range} rangeObj
* @returns {Array}
*/
function arrForColRange(rangeObj){
var height = rangeObj.getHeight();
var vals = rangeObj.getValues();
var arr = [];
for (var i = 0; i < height; i++) {
arr.push(vals[i][0]);
}
return arr;
}
var sheet_vafro = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var range_vafro = sheet_vafro.getRange("A2:F5");
Logger.log(arrForColRange(range_vafro)); // ["A", "B", "C", "D"]// --- Create or Verify Document in a Folder
/**
* Returns a document.
* This creates the document if it does not already exist.
*
* @param {Folder} fldr
* @param {string} name
* @returns {Document}
*/
function createVerifyDocIn(fldr, name) {
var files = filesIn(fldr);
var names = fileNames(files);
if (!(checkValIn(names, name))) {
var doc = DocumentApp.create(name).getId();
var file = DriveApp.getFileById(doc);
moveFile(file, fldr);
}
return openFileAsDocument(findFileIn(fldr, name));
}
var fldr_cvdi = createVerifyPath("google-apps-script-cheat-sheet-demo/docs");
Logger.log(createVerifyDocIn(fldr_cvdi, "example-doc")); // example-doc/**
* Returns a document.
* This creates the document if it does not already exist.
*
* @param {string} name
* @returns {Document}
*/
function createVerifyDocAtRoot(name) {
var files = rootFiles();
var names = fileNames(files);
if (!(checkValIn(names, name))) {
var ss = DocumentApp.create(name);
}
return findFileAtRoot(name);
}/**
* Returns the Id of the active document.
*
* @returns {string}
*/
function docId() {
var _id = DocumentApp.getActiveDocument().getId();
return _id;
}/**
* Returns a file as a document.
*
* @param {File} file
* @returns {Document}
*/
function openFileAsDocument(file) {
var _id = file.getId();
var _doc = DocumentApp.openById(_id);
return _doc;
}
var fldr_ofad = lastFolderIn("google-apps-script-cheat-sheet-demo/docs");
var file_ofad = findFileIn(fldr_ofad, "example-doc");
Logger.log(openFileAsDocument(file_ofad));var fldr_adb = lastFolderIn("google-apps-script-cheat-sheet-demo/docs");
var file_adb = findFileIn(fldr_adb, "example-doc");
var doc_adb = openFileAsDocument(file_adb);
doc_adb.appendParagraph("Hello, world!");var fldr_cdb = lastFolderIn("google-apps-script-cheat-sheet-demo/docs");
var file_cdb = findFileIn(fldr_cdb, "example-doc");
var doc_cdb = openFileAsDocument(file_cdb);
var body_cdb = doc_cdb.getBody();
body_cdb.clear();var ex_obj = {
name: "Jon",
state: "MN",
job: "IT Administrator"
};/**
* Returns a string.
* Words wrapped by the delimiter are replaced with the matching property value.
*
* @param {Object} obj
* @param {string} str
* @param {string} delim
* @returns {string}
*/
function strFromProp(obj, str, delim) {
var split = str.split(" ");
var result = [];
for (var i = 0; i < split.length; i++) {
var _str = split[i];
for (var prop in obj){
var first = _str.slice().charAt(0);
var last = _str.slice().substr(-1);
var mod = _str.substr(0, _str.length-1).substr(1);
if ((obj.hasOwnProperty(mod)) && (first === delim) && (last === delim)) {
result.push(obj[mod]);
} else {
result.push(_str);
}
break;
}
}
return result.join(" ");
}
Logger.log(strFromProp(ex_obj, "name: %name% - state: %state% - job: %job%", "%")); // "name: Jon - state: MN - job: IT Administrator"/**
* Words wrapped by the delimiter are replaced with the matching property value.
*
* @param {Object} obj
* @param {Document} doc
* @param {string} delim
*/
function findReplaceInDoc(obj, doc, delim) {
var body = doc.getBody();
for (var prop in obj) {
var query = delim + prop + delim;
var val = obj[prop];
body.replaceText(query, val);
}
}
var fldr_frid = createVerifyPath("google-apps-script-cheat-sheet-demo/merges");
var doc_frid = createVerifyDocIn(fldr_frid, "find-replace-doc");
var body_frid = doc_frid.getBody();
body_frid.clear();
doc_frid.appendParagraph("name: %name%");
doc_frid.appendParagraph("state: %state%");
doc_frid.appendParagraph("job: %job%");
findReplaceInDoc(ex_obj, doc_frid, "%");/**
* Words wrapped by the delimiter are replaced with the matching property value.
*
* @param {Object} obj
* @param {Spreadsheet} ss
* @param {string} delim
*/
function findReplaceInSpreadsheet(obj, ss, delim) {
var numSheets = ss.getNumSheets();
var sheets = ss.getSheets();
for (var i = 0; i < numSheets; i++) {
var sheet = sheets[i];
var values = sheet.getDataRange().getValues();
for (var row in values){
var update = values[row].map(function(original) {
var text = original.toString();
for (var prop in obj) {
var query = delim + prop+ delim;
if (text.indexOf(query) !== -1) {
text = text.replace(query, obj[prop]);
}
}
return text;
});
values[row] = update;
}
sheet.getDataRange().setValues(values);
}
}
var fldr_fris = createVerifyPath("google-apps-script-cheat-sheet-demo/merges");
var ss_frid = createVerifySSIn(fldr_fris, "find-replace-sheet");
var sheet_frid = ss_frid.getSheets()[0];
sheet_frid.clear();
var val_frid = [
[ "name", "state", "job" ],
[ "%name%", "%state%", "%job%"]
];
var range_frid = sheet_frid.getRange("A1:C2");
range_frid.setValues(val_frid);
findReplaceInSpreadsheet(ex_obj, ss_frid, "%");/**
* Words wrapped by the delimiter are replaced with the matching property value.
*
* @param {Object} obj
* @param {Sheet} sheet
* @param {string} delim
*/
function findReplaceinSheet(obj, sheet, delim) {
var values = sheet.getDataRange().getValues();
for(var row in values){
var update = values[row].map(function(original) {
var text = original.toString();
for (var prop in obj) {
var query = delim + prop + delim;
if (text.indexOf(query) !== -1) {
text = text.replace(query, obj[prop]);
}
}
return text;
});
values[row] = update;
}
sheet.getDataRange().setValues(values);
}
var fldr_fris = createVerifyPath("google-apps-script-cheat-sheet-demo/merges");
var ss_fris = createVerifySSIn(fldr_fris, "find-replace-sheet");
var sheet_fris = ss_fris.getSheets()[0];
sheet_fris.clear();
var val_fris = [
[ "name", "state", "job" ],
[ "<<name>>", "<<state>>", "<<job>>"]
];
var range_fris = sheet_fris.getRange("A1:C2");
range_fris.setValues(val_fris);
findReplaceinSheet(ex_obj, sheet_fris, "%");/**
* For each object, create a new template document and merge in object values.
*
* @requires strFromProp()
* @requires copyFile()
* @requires findReplaceInDoc()
* @param {Object[]} arrObj
* @param {Document} templateDoc
* @param {string} naming
* @param {Folder} fldr
* @param {boolean} ts
* @param {string} delim
*/
function createDocsFromTemplateArrObj(arrObj, templateDoc, naming, fldr, ts, delim) {
for (var i = 0; i < arrObj.length; i++) {
var obj = arrObj[i];
var name = strFromProp(obj, naming, delim);
if (ts === true) name += " - " + fmat12DT();
var file = DriveApp.getFileById(templateDoc.getId());
var docId = copyFile(file, fldr).setName(name).getId();
var doc = DocumentApp.openById(docId);
findReplaceInDoc(obj, doc, delim);
}
}
var sheet_cdftao = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var arrObj_cdftao = arrObjFromSheet(sheet_cdftao, 2);
var fldr1_cdftao = createVerifyPath("google-apps-script-cheat-sheet-demo/merges");
var fldr2_cdftao = createVerifyPath("google-apps-script-cheat-sheet-demo/merges/arrObj-docs");
var doc_cdftao = createVerifyDocIn(fldr1_cdftao, "template-doc");
var body_cdftao = doc_cdftao.getBody();
body_cdftao.clear();
doc_cdftao.appendParagraph("First: %First%");
doc_cdftao.appendParagraph("Last: %Last%");
doc_cdftao.appendParagraph("Grade: %Grade%");
doc_cdftao.appendParagraph("Homeroom: %Homeroom%");
doc_cdftao.appendParagraph("Email: %Email%");
createDocsFromTemplateArrObj(arrObj_cdftao, doc_cdftao, "Name: %Last% %First%", fldr2_cdftao, true, "%");/**
* For each object, create a new template spreadsheet and merge in object values.
*
* @requires strFromProp()
* @requires copyFile()
* @requires findReplaceInSpreadsheet()
* @param {Object[]} arrObj
* @param {Spreadsheet} templateDoc
* @param {string} naming
* @param {Folder} fldr
* @param {boolean} ts
* @param {string} delim
*/
function createSpreadsheetsFromTemplateArrObj(arrObj, templateSS, naming, fldr, ts, delim) {
for (var i = 0; i < arrObj.length; i++) {
var obj = arrObj[i];
var name = strFromProp(obj, naming, delim);
if (ts === true) name += " - " + fmat12DT();
var file = DriveApp.getFileById(templateSS.getId());
var ssId = copyFile(file, fldr).setName(name).getId();
var ss = SpreadsheetApp.openById(ssId);
findReplaceInSpreadsheet(obj, ss, delim);
}
}
var ss1_csftao = SpreadsheetApp.getActiveSpreadsheet();
var sheet1_csftao = ss1_csftao.getSheetByName("Sheet2");
var arrObj_csftao = arrObjFromSheet(sheet1_csftao, 2);
var fldr1_csftao = createVerifyPath("google-apps-script-cheat-sheet-demo/merges");
var fldr2_csftao = createVerifyPath("google-apps-script-cheat-sheet-demo/merges/arrObj-sheets");
var file_csftao = createVerifySSIn(fldr1_csftao, "template-sheet");
var ss2_csftao = openFileAsSpreadsheet(file_csftao);
var sheet2_csftao = ss2_csftao.getSheets()[0];
var val_csftao = [
[ "First", "Last", "Grade", "Homeroom", "Email" ],
[ "%First%", "%Last%", "%Grade%", "%Homeroom%", "%Email%"]
];
var range_csftao = sheet2_csftao.getRange("A1:E2");
range_csftao.setValues(val_csftao);
createSpreadsheetsFromTemplateArrObj(arrObj_csftao, file_csftao, "Name: %Last% %First%", fldr2_csftao, true, "%");var sheet_sdl = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var arrObj_sdl = arrObjFromSheet(sheet_sdl, 2);
var fldr_sdl = createVerifyPath("google-apps-script-cheat-sheet-demo/docs");
var file_sdl = createVerifyDocIn(fldr_sdl, "example-doc");
var doc_sdl = openFileAsDocument(file_sdl);
var body_sdl = doc_sdl.getBody();
(function(){
arrObj_sdl.sort(dynSortM("Last", "First"));
var sectionHeader = body_sdl.appendParagraph("Students");
sectionHeader.setHeading(DocumentApp.ParagraphHeading.HEADING1);
for (var i in arrObj_sdl) {
body_sdl.appendListItem(arrObj_sdl[i]["Last"] + ", " + arrObj_sdl[i]["First"]);
}
})();var sheet_mdl = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var arrObj_mdl = arrObjFromSheet(sheet_mdl, 2);
var fldr_mdl = createVerifyPath("google-apps-script-cheat-sheet-demo/docs");
var file_mdl = createVerifyDocIn(fldr_mdl, "example-doc");
var doc_mdl = openFileAsDocument(file_mdl);
var body_mdl = doc_mdl.getBody();
(function(){
arrObj_mdl.sort(dynSortM("Homeroom", "Last", "First"));
var sectionHeader = body_mdl.appendParagraph("Homerooms and Students");
sectionHeader.setHeading(DocumentApp.ParagraphHeading.HEADING1);
var homeroom = arrObj_mdl[0]["Homeroom"];
body_mdl.appendListItem(homeroom);
for (var i in arrObj_mdl) {
if (arrObj_mdl[i]["Homeroom"] === homeroom) {
body_mdl.appendListItem(arrObj_mdl[i]["First"] + " " + arrObj_mdl[i]["Last"])
.setNestingLevel(1).setIndentStart(10)
.setGlyphType(DocumentApp.GlyphType.HOLLOW_BULLET);
} else {
homeroom = arrObj_mdl[i]["Homeroom"];
body_mdl.appendListItem(homeroom);
body_mdl.appendListItem(arrObj_mdl[i]["First"] + " " + arrObj_mdl[i]["Last"])
.setNestingLevel(1).setIndentStart(10)
.setGlyphType(DocumentApp.GlyphType.HOLLOW_BULLET);
}
}
})();/**
* Returns an array of objects. Subject and Body properties are appended to each object.
*
* @param {Object[]} arrObj
* @param {string} subj
* @param {string} body
* @param {string} delim
* @returns {Object[]}
*/
function appendSubjBodyForArrObj(arrObj, subj, body, delim) {
for (var i = 0; i < arrObj.length; i++) {
var obj = arrObj[i];
for (var prop in obj) {
var search = delim + prop + delim;
if (body.indexOf(search) !== -1) {
body = body.replace(search, obj[prop]);
}
if (subj.indexOf(search) !== -1) {
subj = subj.replace(search, obj[prop]);
}
}
obj.Subject = subj;
obj.Body = body;
}
return arrObj;
}
var sheet_aasbfao = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var arrObj_asbfao = arrObjFromSheet(sheet_aasbfao, 2);
var subj_asbfao = "Classroom update for %First% %Last%";
var body_asbfao = "<p>%First% %Last% is in %Homeroom%'s this fall!</p>";
Logger.log(appendSubjBodyForArrObj(arrObj_asbfao, subj_asbfao, body_asbfao, "%")); // [{Last=Garret, [email protected], Homeroom=Muhsina, Grade=6.0, First=Arienne, Body=<p>Arienne Garret is in Muhsina's this fall!</p>, Subject=Classroom update for Arienne Garret}...]/**
* Sends and email for each object in an array of objects.
* Properties Email, Subject and Body are used.
*
* @requires appendSubjBodyForArrObj()
* @param {Object[]} arrObj
*/
function runMailMergeForArrObj(arrObj) {
for (var i = 0; i < arrObj.length; i++) {
var obj = arrObj[i];
MailApp.sendEmail({
to: obj.Email,
subject: obj.Subject,
htmlBody: obj.Body
});
}
}
var sheet_rmmfao = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var arrObj_rmmfao = arrObjFromSheet(sheet_rmmfao, 2);
var subj_rmmfao = "Classroom update for %First% %Last%";
var body_rmmfao = "<p>%First% %Last% is in %Homeroom%'s this fall!</p>";
arrObj_rmmfao = appendSubjBodyForArrObj(arrObj_rmmfao, subj_rmmfao, body_rmmfao);
runMailMergeForArrObj(arrObj_rmmfao);