Google Sheets

From Torben's Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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