Google Apps Script Snippets

Mar 14, 2021

A collection of useful google apps script snippets with some js tossed in.

Cache Service

Use the Cache Service to cache resources between script executions.

function getRssFeed() {
  var cache = CacheService.getScriptCache();
  var cached = cache.get('rss-feed-contents');
  if (cached != null) {
    return cached;
  }
  // This fetch takes 20 seconds:
  var result = UrlFetchApp.fetch('http://example.com/my-slow-rss-feed.xml');
  var contents = result.getContentText();
  cache.put('rss-feed-contents', contents, 1500); // cache for 25 minutes
  return contents;
}

Google Sheets

Create Menu

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Menu Button').addItem('Menu Dropdown', 'Function').addToUi();
}

function getUi() {
  var ui = SpreadsheetApp.getUi();
  return {
    UI: ui,
    TITLE: 'Menu Button',
    BUTTONS: ui.ButtonSet.OK
  };
}

Remove Null Values

cleanArr = arr.filter(function (el) {
  return el != null && el != '';
});

Overwrite Files in Google Drive

By updating file metadata

var file = existing.next();

// Make sure the file name is exactly the same
if (file.getName() === filename) {
  // Updates file metadata and/or content with the Drive API
  Drive.Files.update(
    {
      title: file.getName(),
      mimeType: file.getMimeType()
    },
    file.getId(),
    att.copyBlob()
  );
}

source

By deleting the existing file

folder = DriveApp.getFolderById(FOLDER_ID);
var searchTerm = `title contains "..."`;
var existing = newFileFolder.searchFiles(searchTerm); // returns file iterator
while (existing.hasNext()) {
  var duplicate = existing.next(); // access file;
  var duplicateFileName = duplicate.getName();
  if (duplicateFileName.indexOf(newFileName) > -1) {
    duplicate.setTrashed(true);
  } else {
    duplicate.moveTo(folder);
  }
}

String Formatting

Get and format current date

var timeZone = Session.getScriptTimeZone();
var date = Utilities.formatDate(new Date(), timeZone, 'ddMMMyy');

Text Styling

Element Attributes

BACKGROUND_COLOR, FOREGROUND_COLOR
BORDER_COLOR, BORDER_WIDTH
BOLD, ITALIC, STRIKETHROUGH, UNDERLINE
FONT_FAMILY, FONT_SIZE

HEADING, LINE_SPACING, LEFT_TO_RIGHT, CODE, LINK_URL
INDENT_START, INDENT_END, INDENT_FIRST_LINE
SPACING_AFTER, SPACING_BEFORE
HORIZONTAL_ALIGNMENT, VERTICAL_ALIGNMENT

LIST_ID, NESTING_LEVEL, GLYPH_TYPE

MARGIN_BOTTOM, MARGIN_LEFT, MARGIN_RIGHT, MARGIN_TOP
PADDING_BOTTOM, PADDING_LEFT, PADDING_RIGHT, PADDING_TOP
PAGE_HEIGHT, PAGE_WIDTH
HEIGHT, WIDTH, MINIMUM_HEIGHT

Find and Apply Formatting to Spring

/**
 *
 - @param search string to search for, supports regex
 - @param style user-defined style to apply
 - @param element element to search in (par, body)
 */

const findTextElement = (search, style, element = body) => {
  var foundElement = element.findText(search);

  while (foundElement != null) {
    var foundText = foundElement.getElement().asText();

    // Where in the element is the found text?
    var start = foundElement.getStartOffset();
    var end = foundElement.getEndOffsetInclusive();

    // Set style
    foundText.setAttributes(start, end, style);

    // Find the next match
    foundElement = element.findText(search, foundElement);
  }
};
GAS js

Related