Google Apps Script Snippets

Gists

• The Modal Dialog works longer than the Prompt.
• Visibility Browser API for sidebars & add-ons
• Set content to file, get content from file
• Sort Active Range of Spreadsheet

Spreadsheet

getSheetByName(spreadsheet, sheetName)

Always returns a sheet

function getSheetByName(spreadsheet, sheetName){
  var sheet = spreadsheet.getSheetByName(sheetName);
  return sheet || spreadsheet.insertSheet(sheetName);
}

Example

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var settingsSheet = getSheetByName(spreadsheet, 'Settings');

cropSheetBySize(sheet, leftRows, topCols)

Prettify the given sheet cropping it

function cropSheetBySize(sheet, leftRows, topCols){
  (leftRows && sheet.getMaxRows() > leftRows) &&
    sheet.deleteRows(leftRows + 1, sheet.getMaxRows() - leftRows);
  (topCols && sheet.getMaxColumns() > topCols) &&
    sheet.deleteColumns(topCols + 1, sheet.getMaxColumns() - topCols);
  return sheet;
}

Example

  cropSheetBySize(sheet, 2, 3);

cropSheetByData(sheet)

Prettify the given sheet cropping it

function cropSheetByData(sheet){
  return cropSheetBySize(sheet, sheet.getLastRow(), sheet.getLastColumn());
}

Example

 sheet.clearContents().getRange(2, 1, values.length, values[0].length).setValues(values);
 cropSheetByData(sheet);

flushAndSleep(milliseconds)

Give your tables to cool down. Don't use with loops

function flushAndSleep(milliseconds){
  SpreadsheetApp.flush();
  Utilities.sleep(milliseconds || 100);
}

onOpen() Move to last cell in specific worksheet [Simple]

  var name = 'Sheet1';
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
  sheet.getRange(sheet.getLastRow(), 1).activate();

onOpen() Move to last cell in specific worksheet [Advanced]

function onOpen(){
  var uid = '792071603';
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var uidsMap = sheets.reduce(function(p,c,i){p[c.getSheetId()]=i;return p;},{});
  var sheet = sheets[uidsMap[uid]];
  sheet.getRange(sheet.getLastRow(), 1).activate();
}