LinkedIn
Twitter
SCRIPT NUGGETS </
>
Sure, massive, complex Apps Scripts are cool, but have you tried Script Nuggets?!
After several custom Apps Scripts integrations, I’ve found myself rewriting similar functions in almost every script. So I decided to create a library of portable functions, or Script Nuggets that can easily be used in any app.
I love you people, and I want to share my nuggets with you.
Just paste these delicious nuggets into Tools>Script Editor and Save at room temperature for 35 milliseconds.
| Ingredient | Purpose |
|---|---|
| clearSheetFormat() | clears formatting in all sheets |
| formatSheet() | formats all sheets: top row bold & frozen |
| clearSheet(‘shName’) | clear all but header row |
| colNumFromName(‘shName’,‘colName’) | returns column number for colName in shName |
| maxIdFromColNum(‘shName’,‘colName’) | returns max value from colNum of shName |
| maxIdFromColName(‘shName’, ‘colName’) | returns max value from colName of shName |
| colNumFromName(‘shName’,‘colName’) | returns column number of colName in shName |
| rowToObj(‘shName’,‘rowNum’) | returns JSON object of row data |
| latestFile(‘folderId’) | returns file Id for newest file in folderId |
| imgToBase64(‘fileId’) | returns Base64encoded string from fileId |
| getFileURL(‘fileId’) | returns URL for fileId |
Secret Sauce
/*
NAME: SCRIPT NUGGETS
DESCRIPTION: Apps Script functions for use with AppSheet
SETUP: Replace YOUR_SHEET_ID in first line with the sheet Id from the sheet URL
BY: GreenFlux, LLC
*//////////////////////////////////////////////////////////////////////////////////////////////////////
const ss = SpreadsheetApp.openById('YOUR_SHEET_ID');//(id from sheetURL)
// ----- TEST WRAPPERS() { sourceFunction(parameters); (parameter names)
// ====================================================================================================
function maxIdFromColNameTEST() {maxIdFromColName('invoice', 'number')}; // (shName, colName)
function maxIdFromColNumTEST() { maxIdFromColNum('invoice', '2')}; // (shName, colNum )
function colNumFromNameTEST() { colNumFromName('invoice', 'created_at')}; // (shName, colName)
function clearSheetTEST() { clearSheet('Test')}; // (shName, colName)
function rowToObjTEST() { rowToObj('customer', '2')}; // (shName, colName)
function latestFileTEST() { latestFile('FOLDER_ID')}; // (folderId)
function imgToBase64TEST() { imgToBase64('FILE_ID')}; // (fileId)
function getFileURLTEST() { getFileURL('FILE_ID')}; // (fileId)
// ====================================================================================================
///////////////////////////////////////////////////////////////////////////////////////////////////////
function clearSheetFormat() {
var allSheets = ss.getSheets();
for (var s in allSheets){
var lastCol = allSheets[s].getLastColumn();
allSheets[s].clearFormats();
allSheets[s].setFrozenRows(0);
}
}
////////////////////////////////////////////////////////////////////////////////
function formatSheets() {
var allSheets = ss.getSheets();
for (var s in allSheets){
var lastCol = allSheets[s].getLastColumn();
allSheets[s].setFrozenRows(1);
allSheets[s].getRange(1, 1, 1, lastCol).setFontWeight("bold");
}
}
////////////////////////////////////////////////////////////////////////////////
function colNumFromName(shName, colName){
var sh = ss.getSheetByName(shName);
var headerArr = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
for (var i = 0; i < headerArr.length; i++) {
if (headerArr[i] == colName) {
i = i + 1; // array loop starts at zero, ColNum starts at one
Logger.log(i);
return i ; // the column number for colName in shName
}
}
}
////////////////////////////////////////////////////////////////////////////////
function maxIdFromColNum(shName, colNum) {
var sh = ss.getSheetByName(shName);
if (sh.getLastRow() > 1) {
var colArr = sh.getRange(2, colNum, sh.getLastRow(), 1).getValues();
var maxInColumn = colArr.sort(function(a, b){return b-a})[0][0] ;
}
else maxInColumn = "";
var maxId = 0;
if (maxInColumn != "") {maxId = maxInColumn};
Logger.log(maxId);
return maxId
}
////////////////////////////////////////////////////////////////////////////////
function maxIdFromColName(shName, colName) {
var colNum = colNumFromName(shName, colName);
var maxId = maxIdFromColNum(shName, colNum);
Logger.log(maxId);
return maxId
}
////////////////////////////////////////////////////////////////////////////////
function clearSheet(sheetName) {
var cSheet = ss.getSheetByName(sheetName);
var lastRow = cSheet.getLastRow();
var lastCol = cSheet.getLastColumn();
cSheet.getRange(2, 1, lastRow, lastCol).clear();
}
////////////////////////////////////////////////////////////////////////////////
function sheetsToObj() {
var sheets = ss.getSheets();
var body = {};
for(i=0;i<sheets.length;i++){
var shName = sheets[i].getName();
var sh_i = ss.getSheetByName(shName);
body[shName] = sh_i.getRange(1, 1, 1, sh_i.getLastColumn()).getValues();
};
Logger.log(JSON.stringify(body, null, 2));
return body;
};
////////////////////////////////////////////////////////////////////////////////
function rowToObj(shName, rowNum) {
var sh = ss.getSheetByName(shName);
var headers = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
var rowData = sh.getRange(rowNum, 1, 1, sh.getLastColumn()).getValues()[0];
var body = {};
for(var i = 0;i<headers.length;i++){
body[headers[i]] = rowData[i];
};
Logger.log(JSON.stringify(body, null, 2));
return body
}
////////////////////////////////////////////////////////////////////////////////
function latestFile(folderId) {
var gFolder = DriveApp.getFolderById(folderId); //id string from URL of image folder
var files = gFolder.getFiles();
var result = [];
do {
var file = files.next();
result.push([file.getDateCreated(), file.getId()]);
} while (files.hasNext()) ;
result.sort();
result.reverse();
var id = result[0][1];
Logger.log(id);
Logger.log(result);
return id;// return most recent file ID in gFolder
}
////////////////////////////////////////////////////////////////////////////////
function imgToBase64(fileId){
try{
var file = DriveApp.getFileById(fileId);
var data = Utilities.base64Encode(file.getBlob().getBytes());
}catch(err){Logger.log(err)}
finally{
Logger.log(data);
return data;
}
}
////////////////////////////////////////////////////////////////////////////////
function getFileURL(fileId){
var fileURL = 'https://drive.google.com/uc?export=view&id=' + fileId;
var html = '<img src = "' + fileURL + '">';
Logger.log(fileURL);
return fileURL;
};
