Google Apps Script Helper - Spreadsheet App

Helper:SpreadsheetApp.gs

/**
 * Converts a rich text value to HTML markup.
 * 
 * Only honours "markdown-supported" HTML tags, disregarding any other cell formatting value (such as color, background color, etc.). In other words, keeps the plain text value of the text in the cell, adding only the following HTML tags when necessary:
 * 
 * - <strong>
 * - <em>
 * - <del>
 * - <span style="text-decoration: underline">
 * - <a href="">
 */
function richText2MarkdownHtml(richTextValue) {
  var formattedText = '';

  for (let run of richTextValue.getRuns()) {
    const runLink = run.getLinkUrl();
    const runStyle = run.getTextStyle();
    const runText = run.getText();

    // Crude way to escape possible HTML entities in the text.
    let runTextHtml = runText
      .replaceAll('&', '&amp;')
      .replaceAll('<', '&lt;')
      .replaceAll('>', '&gt;')
    ;

    runTextHtml = runStyle.isStrikethrough()
      ? `<del>${runTextHtml}</del>`
      : runTextHtml;

    runTextHtml = runStyle.isUnderline() && !runLink
      ? `<span style="text-decoration: underline">${runTextHtml}</span>`
      : runTextHtml;

    runTextHtml = runStyle.isItalic()
      ? `<em>${runTextHtml}</em>`
      : runTextHtml;

    runTextHtml = runStyle.isBold()
      ? `<strong>${runTextHtml}</strong>`
      : runTextHtml;

    runTextHtml = runLink
      ? `<a href="${runLink}">${runTextHtml}</a>`
      : runTextHtml;

    runTextHtml = runTextHtml.replace(/[\r\n]/g, '<br>');

    formattedText += runTextHtml;
  }

  return formattedText;
}


/**
 * Returns an ENUM string meant to represent the "type" of edit which occured:
 * 
 * - MULTIPLE_CELLS: Multiple cells were edited simultaneously.
 * - PASTE_OR_UNDO: The single cell edited was from a "paste" or "undo" action.
 * - BLANK_OLD: The single cell edited was previously blank.
 * - BLANK_NEW: The single cell edited was cleared out.
 * - STANDARD: A "regular" edit, usually meaning a single cell change from one value to another.
 * 
 * We infer the type of edit by checking 1 of 3 possible values of the `oldValue` and `value`
 * properties on original event object, all combinations of which are provided in the table below:
 * 
 * +-------------------+------------+-----------+----------------+
 * | Example           | e.oldValue |  e.value  |      Name      |
 * +-------------------+------------+-----------+----------------+
 * | "test1" > "test2" |   "test1"  |  "test2"  | STANDARD       |
 * | (blank) > "test2" |     --     |  "test2"  | BLANK_OLD      |
 * | "test1" > (blank) |   "test1"  |     --    | BLANK_NEW      |
 * | [paste]           |     --     |     --    | PASTE_OR_UNDO  |
 * | [undo]            |     --     |     --    | PASTE_OR_UNDO  |
 * | [multiple range]  |     --     |     --    | MULTIPLE_CELLS |
 * +-------------------+------------+-----------+----------------+
 * 
 * Documentation source: https://developers.google.com/apps-script/guides/triggers/events#edit
 */
function getEditTypeFromEventObject(editEvent) {
  const editEventProps = Object.keys(editEvent);

  var editType = "STANDARD";
  if (editEvent.range.rowStart !== editEvent.range.rowEnd || editEvent.range.columnStart !== editEvent.range.columnEnd) {
    editType = "MULTIPLE_CELLS";
  } else if (!editEventProps.includes("value") && !editEventProps.includes("oldValue")) {
    editType = "PASTE_OR_UNDO";
  } else if (!editEventProps.includes("oldValue") && editEvent.value !== undefined) {
    editType = "BLANK_OLD";
  } else if (!editEventProps.includes("value")    && editEvent.oldValue !== undefined) {
    editType = "BLANK_NEW";
  }

  return editType;
}


/**
 * Returns the value of a cell under a column with title {colTitle}
 * on the currently active row number. Empty string if cell isn't found.
 *
 * This is a pass-through function to `getCellValueByColumnTitleOfRowNumber()`
 * after the active row index is found.
 *
 * @param {string} colTitle
 * @return {string}
 */
function getCellValueByColumnTitleOfActiveRow(colTitle) {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var currentCell = activeSheet.getCurrentCell();
  var currentRowNumber = currentCell.getRow();

  return getCellValueByColumnTitleOfRowNumber(colTitle, currentRowNumber);
}


/**
 * Returns the value of a cell under a column with title {colTitle}
 * on row number {rowNumber}. Empty string if cell isn't found.
 * Optionally specify the name of the respective sheet as {sheetName}.
 *
 * @param {string} colTitle
 * @param {integer} rowNumber
 * @param {string} sheetName
 * @return {string}
 */
function getCellValueByColumnTitleOfRowNumber(colTitle, rowNumber, sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var dataSheet = sheetName
    ? ss.getSheetByName(sheetName)
    : ss.getActiveSheet();

  var rowRange = dataSheet.getRange(rowNumber+':'+rowNumber);
  var colIndex = getColumnIndexByHeaderTitle(colTitle);

  return colIndex !== -1
    ? rowRange.getValues()[0][colIndex]
    : '';
}


/**
 * Gets the column index who's value on row 1 is identical to {title}.
 * Optionally specify the name of the respective sheet as {sheetName}.
 * If no match, returns -1.
 *
 * @param {string} title
 * @param {string} sheetName
 * @return {integer}
 */
function getColumnIndexByHeaderTitle(title, sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var dataSheet = sheetName
    ? ss.getSheetByName(sheetName)
    : ss.getActiveSheet();

  var headerRowTitles = dataSheet.getRange('1:1').getValues()[0]; // ['Title1', 'Title2', ...]

  return headerRowTitles.indexOf(title);
}
function getColumnNumberByHeaderTitle(title, sheetName) {
  return 1 + getColumnIndexByHeaderTitle(title, sheetName);
}


/**
 * Returns the numbers of rows which have active cells (i.e. selected rows).
 *
 * @return {Array} An array of integers or an empty array.
 */
function getActiveRowNumbers() {
  var activeSheet = SpreadsheetApp.getActiveSheet();

  var selection = activeSheet.getSelection();
  var currentCell = selection.getCurrentCell();
  var ranges =  selection.getActiveRangeList().getRanges();

  var selectedRows = [];
  for (var i = 0; i < ranges.length; i++) {
    var rowNumber = ranges[i].getRow();
    selectedRows.push(rowNumber);
  }

  return selectedRows;
}


/**
 * Returns a dictionary of column titles to their indexes. Uses the first row
 * row of the active sheet. This is useful as a performant way to lookup
 * the column index by its title.
 *
 * {
 *   "ID": 0,
 *   "Employee first name": 1,
 *   "Email Address": 2
 * }
 *
 * @return {object} An object whos key-value pairs are column titles and index values.
 */
function getColIndexByTitleLookup(colTitles) {
  if (!colTitles) {
    colTitles = SpreadsheetApp
      .getActiveSheet()
      .getSheetValues(1, 1, 1, -1)[0] // Entire 1st row (column titles).
    ;
  }

  var colIndexByTitle = {};

  colTitles.forEach(function (title, index) {
    colIndexByTitle[title] = index;
  });

  return colIndexByTitle;
}


/**
 * Returns a two-dimensional array for filtered values of the active sheet.
 *
 * Effectively, this is identical to Sheet.getDataRange().getValues(), except this
 * honours any applied data filters and returns only rows visible in the UI. E.g.:
 *
 * [
 *   ["ID", "Employee first name", "Email Address"]
 *   ["30", "Jack", "jack@company.com"],
 *   ["33", "Sara", "sara@company.com"],
 *   ["72", "Jack", "jack@company.com"],
 *   ["89", "Phil", "phil@company.com"],
 * ]
 *
 * Note:
 *
 * The "native" way to get filtered values of a range is by checking each row
 * individually via `Sheet.isRowHiddenByFilter()` which is an expensive operation: O(N).
 * The next tempting approach is via the Sheets API, but that requires enabling
 * API support which is a bit messy and requires more dependencies for the user/developer.
 * An alternative is to copy/paste the sheet contents into a new tab (since that
 * operation honours the applied filter) and deleting the sheet after -- this performs
 * well (O(1)) but it's quirky to automate things in the UI.
 *
 * Instead, this function uses the Google Charts Query Language
 * (https://developers.google.com/chart/interactive/docs/querylanguage) to fetch a CSV
 * version of the sheet (which does honour applied filters) and parses it back into
 * the expected format of a two-dimensional array. It's hacky and may seem extraneous,
 * but it's faster than any alternative: O(1). The only downside is it requires one additional
 * permission for the script (https://www.googleapis.com/auth/script.external_request),
 * but it's safe since this is a server-initiated call to Google's own servers.
 *
 * Original inspiration: https://stackoverflow.com/questions/57697431/filter-using-setcolumnfiltercriteria-not-filtering
 *
 * @return {array} A rectangular grid of non-filtered values
 */
function fetchFilteredDataValues() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  var fetchUrl = `https://docs.google.com/spreadsheets/d/${ss.getId()}/gviz/tq?`;
  fetchUrl += [
    `tqx=out:csv`,
    `gid=${sheet.getSheetId()}`,
    `access_token=${ScriptApp.getOAuthToken()}`
  ].join('&');

  var csvFile = UrlFetchApp.fetch(fetchUrl);
  var values = Utilities.parseCsv(csvFile.getContentText());

  return values;
}

// --------------------------- DEPRECATED ---------------------------

/**
 * Returns a two-dimensional array for filtered values of the active sheet.
 *
 * Effectively, this is identical to Sheet.getDataRange().getValues(), except this
 * honours any applied data filters and returns only rows visible in the UI. E.g.:
 *
 * [
 *   ["ID", "Employee first name", "Email Address"]
 *   ["30", "Jack", "jack@company.com"],
 *   ["33", "Sara", "sara@company.com"],
 *   ["72", "Jack", "jack@company.com"],
 *   ["89", "Phil", "phil@company.com"],
 * ]
 *
 * Note:
 *
 * Uses a workaround by copy/pasting the values of the active sheet into a
 * temporary sheet and then quickly removes it. The reason is because the
 * copy/paste operation honours any active filter.
 *
 * @return {array} A rectangular grid of non-filtered values
 */
function getFilteredDataValues() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  var tempSheet = ss.insertSheet();

  var tempSheetRange = tempSheet.getRange("1:1");
  tempSheet.deleteColumns(2, tempSheetRange.getNumColumns()-1);

  var tempSheetRange = tempSheet.getRange("A:A");
  tempSheet.deleteRows(2, tempSheetRange.getNumRows()-1);

  sheet.getDataRange().copyTo(tempSheet.getDataRange());
  //sheet.getDataRange().copyTo(tempSheet.getDataRange(), { contentsOnly: true });

  filteredValues = tempSheet.getSheetValues(1, 1, -1, -1);
  ss.deleteSheet(tempSheet);
  ss.setActiveSheet(sheet);

  return filteredValues;
}


/**
 * Returns a two-dimensional array for all the visible values of the active sheet.
 * Assumes the sheet's first row is column titles.
 *
 * Effectively, this is identical to Sheet.getDataRange().getValues(), except this
 * honours any applied data filters and returns only rows visible in the UI. E.g.:
 *
 * [
 *   ["30", "Jack", "jack@company.com"],
 *   ["33", "Sara", "sara@company.com"],
 *   ["72", "Jack", "jack@company.com"],
 *   ["89", "Phil", "phil@company.com"],
 * ]
 *
 * @return {array} A rectangular grid of non-filtered values
 */
function getFilteredRowsValues() {
  const sheet = SpreadsheetApp.getActiveSheet();
  var rowsValues = sheet.getSheetValues(2, 1, -1, -1); // All data rows.
  
  // Honour any data filter applied on the data range.
  // NOTE: isRowHiddenByFilter() is an expensive operation.
  var filteredRows = rowsValues.filter(function (row, index) {
    return !sheet.isRowHiddenByFilter(index+2);
  });

  return filteredRows;
}