Google Sheets snippets

format.md

Format values in k/M/B format (source)

Use the following custom format:

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

formula.md

This formula calculates the difference between a given date (H11 in the example) and today, and formats it as Y years, M months, or just M months if Y=0

CONCAT(
  IF(DATEDIF(H11;today();"Y") > 0;
    DATEDIF(H11;today();"Y") & " years, ";
    ""
);DATEDIF(H11;today();"YM") & " months")

Google Sheets

Misc. Google Sheets snippets

reset_filters.gs

// From Spice Up Your Sheet Life Edition 2 - 100 Tips For Google Sheets
function resetFilter() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  range.getFilter().remove();
  range.createFilter();
}

toggle_zero_columns.gs

/*
 * Usage: Tools > Script Editor
 *        replace all code with below
 *        Click Run
 *
 * Source: https://stackoverflow.com/a/13591754/31493
 */

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [{name: "Hide columns", functionName: "hideColumns"},
    {name: "Show all columns", functionName: "showColumns"}];

  // add to menu
  ss.addMenu("Show/Hide Columns", menu);
}

function hideColumns() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn()+1;

  Logger.log(lastCol);

  // iterate through columns
  for (var i = 1; i < lastCol; i++) {
     if (data.getCell(3, i).getValue() == 0) {
        sheet.hideColumns(i);
     }
  }
}

function showColumns() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get first sheet
  var sheet = ss.getSheets()[0];

  // get data
  var data = sheet.getDataRange();

  // get number of columns
  var lastCol = data.getLastColumn();

  // show all columns
  sheet.showColumns(1, lastCol);
}