Google-sheets – Change case of all text in a column

As discussed in the thread you linked to in comments, the formula solution is quite convoluted and potentially very slow, if there are multiple instances to be replaced in the string. However with your qualification:

And finally in my real case I'm lucky enough to be able to split the text into parts which contain no more than one value to be replaced (and possibly some other character data!). The result could be then joined back into a single string. If that would make it any easier you could use that as well.

I believe the solution becomes a little more straight forward (and reasonably efficient, I would say):

=IFERROR(REGEXREPLACE(A2;JOIN("|";Lookup!$A$2:$A$5);VLOOKUP(REGEXEXTRACT(A2;JOIN("|";Lookup!$A$2:$A$5));Lookup!$A$2:$B$5;2;0)&"");A2)

which has the added advantage of being able to be easily converted into an array formula that will populate down the column:

=ArrayFormula(IFERROR(REGEXREPLACE(A2:A;JOIN("|";Lookup!A2:A5);VLOOKUP(REGEXEXTRACT(A2:A;JOIN("|";Lookup!A2:A5));Lookup!A2:B5;2*SIGN(ROW(A2:A));0)&"");A2:A))

I have also entered a custom function solution:

function MSUBSTITUTE(text, subTable)
{
  var searchArray = [], subArray = [];
  for (var i = 0, length = subTable.length; i < length; i++)
  {
    if (subTable[i][0])
    {
      searchArray.push(subTable[i][0]);
      subArray.push(subTable[i][1]);
    }
  }
  var re = new RegExp(searchArray.join('|'), 'g');
  return text.replace(re, function (match) {return subArray[searchArray.indexOf(match)];});
}

=MSUBSTITUTE(A2;Lookup!$A$2:$B$5)

It would be fairly trivial to convert this to an auto-populating array formula, as well.

Here is a script that pulls all file names and last-updated from timestamps from a given folder, sorting files by their names.

The folder is specified by its Id, which is what you see at the end of folder URL after drive.google.com/drive/folders/

The logic of the script is simple: files is an iterator for all files in the folder, from which the files are pulled out. Then the output array is sorted and placed in the current sheet, starting with the cell A2 (that is, row 2 column 1 in getRange method).

function files() {
  var folderId = 'enter folder Id here';
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFiles();
  var output = []; 
  while (files.hasNext()) {
    var file = files.next();
    output.push([file.getName(), file.getLastUpdated()]);
  }
  output.sort(function(a, b) {
    return a[0] == b[0] ? 0 : a[0] < b[0] ? -1 : 1;
  });
  SpreadsheetApp.getActiveSheet().getRange(2, 1, output.length, output[0].length).setValues(output);
}