Google Sheets

From Torben's Wiki

Functions

get cell location of last value

=ADDRESS(COUNTA(A:A);1)
-> $A$5
# use in other formular
sum(A2:indirect(ADDRESS(counta(A:A);1)))
# perfrom a calc for all cells of B where A is not empty (and has no gaps)
=arrayformula(2*B2:indirect(ADDRESS(counta(A:A);2))


ArrayFormula

apply a formula to all rows

=ARRAYFORMULA( IF (A2:A; WEEKNUM(A2:A) ;"" ))

Calc day, weekstart, month from date/datetime column

day
=ARRAYFORMULA( DATE(year(A2:A);month(A2:A);day(A2:A)) )
date start of week
=ARRAYFORMULA( C2:C-WEEKDAY(C2:C;3) )
date start of month
=ARRAYFORMULA( DATE(year(C2:C);month(C2:C);1))

Shortcuts

Keys Result
CMD ALT Shift : (Mac) Insert Date and Time

Macros

Add Menu Item

function onOpen() {
  var ui = SpreadsheetApp.getUi();  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Übertragen', 'Uebertragen')
      .addToUi();
}

copy range of cells to other sheet

function Uebertragen() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getCurrentCell().offset(1, 0).activate();  
  spreadsheet.getRange('Sheet1!A5:F5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
};