Google Sheets

From Torben's Wiki
Jump to navigationJump to search

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



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