Find and replace script (Google-apps) for Content grouping

Question

Some of you may be familiar with content grouping in Google Analytics, which basically lets you group any number of URL's in user-specified groups (this is useful for analyzing pages that belong together all at the same time). I'm working on a script to take that to the next level and use it in Google Sheets as well.

Goal: have a working script that rewrites URL's and gives them another name, regardless of whether it uses upper or lower cases in the URL.

So far I have this:

function onOpen() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getRange("a1:a10000");
    var to_replace = /.*example.*/;
    var replace_with = "TEST";
    var to_replace2 = /.*another-example.*/;
    var replace_with2 = "TEST-Nr2";

    replaceInSheet(sheet,range, to_replace, replace_with);
    replaceInSheet(sheet,range, to_replace2, replace_with2);
 }

This script works in the sense that it rewrites URL's with 'Example' in it to 'Test' and it rewrites 'Another-example' into TEST-Nr2.

However, the final script will probably have thousands of URL's that will need to be rewritten. Furthermore, some URL's have uppercases in them, which I want to ignore and just rewrite.

All of the above leads me to two questions:

1. How can I write the script in such a way (with regular expressions for example?) that I won't have a Googleplex number of To_replace's and replace_with's?

2. How can I make my to_replace variables case-incensitive?

If any more information is needed on this matter I will gladly provide so.

Kind regards,

JNeu

Answer 1

Somehow you know the patterns and the replacement values, yes? You need to impart that knowledge to your script.

The simplest way is to read it from a spreadsheet, e.g. on some sheet in some workbook, you have 1 column with the pattern, and another column with the replacement. Then you just read that data in (Range#getValues()), and then iterate that array to process your data range. Note that the pattern you store in the sheet should not include the literal constructor slashes, i.e. you'd want \d{1,3} and not /\d{1,3}/ in the cell.

Example:

function processAll() {
  const source = SpreadsheetApp.openById("id of the spreadsheet with pattern - replacement data"),
        info = source.getSheetByName("some sheet name")
                 .getDataRange().getValues();

  const databook = SpreadsheetApp.getActive(),
        sheet = databook.getSheetByName("name of the sheet with data to process");
  if (!sheet) return; // sheet with that name doesn't exist.
  const range = sheet.getRange(1, 1, sheet.getLastRow(), 1);

  info.forEach(function (row) {
    // Create case-insensitive pattern from the string in Column A, e.g. \d{1,3} and NOT /\d{1,3}/
    var pattern = new RegExp(row[0], "i"); 
    var repl = row[1];  // replacement text from Column B
    replaceInSheet(sheet, range, pattern, repl);
  });
}

Additional Reading:

• RegExp
• Array#forEach