Sort by Random - How to Randomize the Order of Rows in Google Sheets

You have a workbook in Google Sheets that contains multiple rows of data and you are required to sort the list in a random order. For instance, your sheet may contain the names of your team members and you need to reshuffle the list before assigning tasks to each of the members randomly. Or your Google Sheet may have the email addresses of people who participated in a giveaway and you need to pick any three random entries in an unbiased manner for the prize.

There are multiple ways to randomize the data rows in Google Sheet. You can either use the built-in SORT function of Google Sheets or create a menu-based function that lets you randomize data with a click.

Demo - Make a copy of this Google Sheet to try random sort with your own data in sheets.

Sort Google Sheets in Random Order

Open your Google Sheet that contains the list of data and create a new sheet. Paste the following formula in A1 cell of this empty sheet.

=SORT(Customers!A2:D50, RANDARRAY(ROWS(Customers!A2:A50), 1), FALSE)

The first argument of the SORT function specifies the range of data that needs to be sorted in A1 Notation, the second argument creates a virtual column of same dimension but filled with random numbers and third order specifies the sort order from smallest to largest.

You may also want to replace Customers in the formula with the exact name of your Google Sheet. If the sheet name contains spaces, enclose your sheet name in single as quotes like 'Employee List'!A2:D50. We start with row 2 since the first row is assumed to contain the header (titles).

The advantage with this approach is that it doesn’t alter the source of data as the randomized list of data appears in a new sheet.

Sort a List Randomly in Google Sheets with Apps Script

If you prefer a more automated approach that doesn’t require you to manually add formulas each time you need to perform a random sort, take the Apps Script route.

Open your Google Sheet, go to the Tools menu and choose Script editor. Copy-paste the following code in the editor and save. Reload the Google Sheet and you should see a new menu as shown in the screenshot above.

/** @OnlyCurrentDoc */

// Sort data in random order
const sortRowsInRandomOrder = () => {
  // Get the current sheet that contains the list of data
  const sheet = SpreadsheetApp.getActiveSheet();

  // Get the first non-empty column
  const column = sheet.getLastColumn() + 1;

  // Add the RAND() formula to all rows in the new column
  sheet
    .getRange(1, column)
    .setFormula("=RAND()")
    .autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

  // Sort the entire range of data using the random values
  // Do not include the first row of data (header) for sort
  sheet.getDataRange().offset(1, 0).sort({ column });

  // Remove the temporary column from Google sheet
  sheet.deleteColumn(column);

  // Flush the changes
  SpreadsheetApp.flush();
};

// Add the menu to Google Sheets
const onOpen = () => {
  SpreadsheetApp.getUi()
    .createMenu("Randomize Rows")
    .addItem("Start", "sortRowsInRandomOrder")
    .addToUi();
};

Keep Shuffling Rows

Go to the Randomize Rows menu and choose Start. It creates a temporary column, fill the RAND() formula in the new column for the entire range of cells, sorts the sheet range by this data and then remove the temporary column automatically.

You can click the same menu item multiple times and it will keep shuffling the rows in random order.

from Digital Inspiration https://ift.tt/3wIx0A1

Exceeded maximum execution time Exception in Google Apps Script

Google Apps Script is a serverless environment that makes it easy for you to work with Gmail, Google Drive and other services that are part of the Google Workspace platform.

When you run any code inside the Google Apps Script IDE, in simple English, it spins up a new server with the required environment that are necessary to run your application. This server is allotted a hard timeout limit and the App Script environment will halt the execution of the function if it exceeds the maximum execution time.

Exceeded maximum execution time

The maximum execution time varies based on the type of your Google Account. If you are running your Apps Script code inside a Gmail account, your functions can run for 6 minutes before it will be terminated. For Google Workspace accounts, because you are paying a monthly fee to Google per user, the timeout limit is more generous at 30 minutes.

If your Apps Script function / trigger exceeds the maximum timeout limit, the script will throw an exception like Exceeded maximum execution time or equivalent based on your script’s locale.

Exceeded maximum execution time
Se ha superado el tiempo máximo de ejecución.
Timpul maxim de executare a fost depășit
تجاوز الحد الأقصى لعدد مرات التنفيذ
Vượt quá thời gian thực thi tối đa
Durée d'exécution autorisée dépassée
Przekroczono maksymalny czas wykonywania
Limite massimo del tempo di esecuzione superato
เวลาประมวลผลเกินขีดจำกัดสูงสุด
Melebihi jumlah eksekusi maksimum
Превышено максимально допустимое время выполнения
Lumagpas sa maximum na oras ng execution

Avoid Maximum Execution Time Limit

You can include a simple time check in your Apps Script function, that it likely to take more than a few minutes to execute, and gracefully pause the request if is seen to be exceeding the time limit.

For instance, the Download Gmail add-on saves email messages from Gmail to Google Drive as PDF files. It grabs a bunch of messages from the Inbox, converts them to PDF and runs in a loop. If the execution is taking longer, it breaks from the loop automatically.

const GMAIL_USER = /(gmail|googlemail)/.test(
  Session.getActiveUser().getEmail()
);
const ONE_SECOND = 1000;
const ONE_MINUTE = ONE_SECOND * 60;
const MAX_EXECUTION_TIME = ONE_MINUTE * (GMAIL_USER ? 6 : 30);
const NOW = Date.now();

const isTimeLeft = () => {
  return MAX_EXECUTION_TIME > Date.now() - NOW;
};

const thisFunctionTakesTimeToExecution = () => {
  const threads = GmailApp.getInboxThreads(0, 100);
  for (let t = 0; t < threads.length && isTimeLeft(); t += 1) {
    // Save email to Google Drive
    Logger.log("Saving email...");
  }
};

from Digital Inspiration https://ift.tt/3jZML33

How to Replace Accented Characters (diacritics) with English letters in Google Sheets

The REMOVE_ACCENTED function for Google Sheets will replace all accented characters in the referenced cell, like the letters è, õ, ā, ĝ and so on with their normal Latin equivalents.

To get started, make a copy of the Google Sheet, go to the Tools menu, choose Script Editor and copy the entire code to your clipboard.

Now open your own Google Sheet and paste the same code inside the Script editor of your sheet. Save and you should be able to use the REMOVE_ACCENTED function in your own sheets.

Input String	Output string
A História de Malú e João Miguel	A Historia de Malu e Joao Miguel
Símbolo de su unidad y permanencia	Simbolo de su unidad y permanencia
Tomás Gutiérrez Alea	Tomas Gutierrez Alea
Miguel Ángel Félix Gallardo	Miguel Angel Felix Gallardo

Internally, this function uses the deburr function of the popular lodash library that converts Latin-1 Supplement and Latin Extended-A letters to basic Latin letters and also removes any combining diacritical marks.

Find and Replace Accented Letters in Spreadsheets

const latinRegEx = /[\xc0-\xd6\xd8-\xf6\xf8-\xff\u0100-\u017f]/g;
const comboRegEx = `[\\u0300-\\u036f\\ufe20-\\ufe2f\\u20d0-\\u20ff]`;

/** Used to map Latin Unicode letters to basic Latin letters. */
const latinUnicodeLetters = {
  // Latin-1 Supplement block.
  '\xc0': 'A', '\xc1': 'A', '\xc2': 'A', '\xc3': 'A', '\xc4': 'A', '\xc5': 'A',
  '\xe0': 'a', '\xe1': 'a', '\xe2': 'a', '\xe3': 'a', '\xe4': 'a', '\xe5': 'a',
  '\xc7': 'C', '\xe7': 'c',
  '\xd0': 'D', '\xf0': 'd',
  '\xc8': 'E', '\xc9': 'E', '\xca': 'E', '\xcb': 'E',
  '\xe8': 'e', '\xe9': 'e', '\xea': 'e', '\xeb': 'e',
  '\xcc': 'I', '\xcd': 'I', '\xce': 'I', '\xcf': 'I',
  '\xec': 'i', '\xed': 'i', '\xee': 'i', '\xef': 'i',
  '\xd1': 'N', '\xf1': 'n',
  '\xd2': 'O', '\xd3': 'O', '\xd4': 'O', '\xd5': 'O', '\xd6': 'O', '\xd8': 'O',
  '\xf2': 'o', '\xf3': 'o', '\xf4': 'o', '\xf5': 'o', '\xf6': 'o', '\xf8': 'o',
  '\xd9': 'U', '\xda': 'U', '\xdb': 'U', '\xdc': 'U',
  '\xf9': 'u', '\xfa': 'u', '\xfb': 'u', '\xfc': 'u',
  '\xdd': 'Y', '\xfd': 'y', '\xff': 'y',
  '\xc6': 'Ae', '\xe6': 'ae',
  '\xde': 'Th', '\xfe': 'th',
  '\xdf': 'ss',
  // Latin Extended-A block.
  '\u0100': 'A', '\u0102': 'A', '\u0104': 'A',
  '\u0101': 'a', '\u0103': 'a', '\u0105': 'a',
  '\u0106': 'C', '\u0108': 'C', '\u010a': 'C', '\u010c': 'C',
  '\u0107': 'c', '\u0109': 'c', '\u010b': 'c', '\u010d': 'c',
  '\u010e': 'D', '\u0110': 'D', '\u010f': 'd', '\u0111': 'd',
  '\u0112': 'E', '\u0114': 'E', '\u0116': 'E', '\u0118': 'E', '\u011a': 'E',
  '\u0113': 'e', '\u0115': 'e', '\u0117': 'e', '\u0119': 'e', '\u011b': 'e',
  '\u011c': 'G', '\u011e': 'G', '\u0120': 'G', '\u0122': 'G',
  '\u011d': 'g', '\u011f': 'g', '\u0121': 'g', '\u0123': 'g',
  '\u0124': 'H', '\u0126': 'H', '\u0125': 'h', '\u0127': 'h',
  '\u0128': 'I', '\u012a': 'I', '\u012c': 'I', '\u012e': 'I', '\u0130': 'I',
  '\u0129': 'i', '\u012b': 'i', '\u012d': 'i', '\u012f': 'i', '\u0131': 'i',
  '\u0134': 'J', '\u0135': 'j',
  '\u0136': 'K', '\u0137': 'k', '\u0138': 'k',
  '\u0139': 'L', '\u013b': 'L', '\u013d': 'L', '\u013f': 'L', '\u0141': 'L',
  '\u013a': 'l', '\u013c': 'l', '\u013e': 'l', '\u0140': 'l', '\u0142': 'l',
  '\u0143': 'N', '\u0145': 'N', '\u0147': 'N', '\u014a': 'N',
  '\u0144': 'n', '\u0146': 'n', '\u0148': 'n', '\u014b': 'n',
  '\u014c': 'O', '\u014e': 'O', '\u0150': 'O',
  '\u014d': 'o', '\u014f': 'o', '\u0151': 'o',
  '\u0154': 'R', '\u0156': 'R', '\u0158': 'R',
  '\u0155': 'r', '\u0157': 'r', '\u0159': 'r',
  '\u015a': 'S', '\u015c': 'S', '\u015e': 'S', '\u0160': 'S',
  '\u015b': 's', '\u015d': 's', '\u015f': 's', '\u0161': 's',
  '\u0162': 'T', '\u0164': 'T', '\u0166': 'T',
  '\u0163': 't', '\u0165': 't', '\u0167': 't',
  '\u0168': 'U', '\u016a': 'U', '\u016c': 'U', '\u016e': 'U', '\u0170': 'U', '\u0172': 'U',
  '\u0169': 'u', '\u016b': 'u', '\u016d': 'u', '\u016f': 'u', '\u0171': 'u', '\u0173': 'u',
  '\u0174': 'W', '\u0175': 'w',
  '\u0176': 'Y', '\u0177': 'y', '\u0178': 'Y',
  '\u0179': 'Z', '\u017b': 'Z', '\u017d': 'Z',
  '\u017a': 'z', '\u017c': 'z', '\u017e': 'z',
  '\u0132': 'IJ', '\u0133': 'ij',
  '\u0152': 'Oe', '\u0153': 'oe',
  '\u0149': "'n", '\u017f': 's'
};

const basePropertyOf = (object) => (key) => object[key];
const characterMap = basePropertyOf(latinUnicodeLetters);

/**
 * Replace accented characters in Google Sheets with English letters.
 *
 * @param {string} input The input string with accented characters.
 * @return The input without accented characters.
 * @customfunction
 */
function REPLACE_ACCENTED(input) {
  if (input && typeof input === "string") {
    return input.replace(latinRegEx, characterMap).replace(comboRegEx, "");
  }
  return input;
}

from Digital Inspiration https://ift.tt/3hVwgT2

How to Find and Replace Text in Google Docs with RegEx Search Patterns

It is easy to search and replace text in Google Documents with the DocumentApp service of Google Apps Script. You can use use findText method with simple regular expressions to find text elements in the document that match a pattern and replace them with the specified text.

Here’s a simple code sample that replaces the first occurrence of the “GSuite” with “Google Workspace” in the active Google Document.

const searchAndReplaceInGoogleDocs = () => {
  const searchText = "GSuite";
  const replaceText = "Google Workspace";

  const document = DocumentApp.getActiveDocument();
  const documentBody = document.getBody();

  const searchResult = documentBody.findText(searchText);

  if (searchResult !== null) {
    const startIndex = searchResult.getStartOffset();
    const endIndex = searchResult.getEndOffsetInclusive();
    const textElement = searchResult.getElement().asText();
    textElement.deleteText(startIndex, endIndex);
    textElement.insertText(startIndex, replaceText);
  }

  document.saveAndClose();
};

All well and good but in some cases, this simple search and replace function may fail if the search text does not transform into a valid regular expression.

For instance, if you have a text block like Hello (World in the document (notice the extra open bracket) that you would like to replace with Hello World, the above snippet will fail with an error message that says Exception: Invalid regular expression pattern.

To get around the problem, it is a good idea to replace all the special characters in the search pattern that have a special meaning in the RegEx world. These include characters like hyphen, brackets, question marks or the plus symbol.

Our modified search and replace function would then become:

const escapeRegex = (str) => str.replace(/[-[\]/{}()*+?.\\^$|#]/g, "\\$&");

const searchAndReplaceInGoogleDocs = () => {
  const searchText = "Hello (World";
  const replaceText = "Hello World";

  const document = DocumentApp.getActiveDocument();
  const documentBody = document.getBody();

  const searchResult = documentBody.findText(escapeRegex(searchText));

  if (searchResult !== null) {
    const startIndex = searchResult.getStartOffset();
    const endIndex = searchResult.getEndOffsetInclusive();
    const textElement = searchResult.getElement().asText();
    textElement.deleteText(startIndex, endIndex);
    textElement.insertText(startIndex, replaceText);
  }

  document.saveAndClose();
};

from Digital Inspiration https://ift.tt/3AC0mDH