INTRODUCING: Script Nuggets!

[GreenFlux]

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;
};

[MultiTech]

I love it!

GreenFlux:

Save at room temperature for 35 milliseconds.