Torben at 20:50, 30 October 2024

2024-10-30T20:50:24Z

New page

[[Category:Software]]
===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===
{| class="wikitable"
|-
! 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)
};

Google Sheets - Revision history

Torben at 20:50, 30 October 2024