Google Script, how can use variables with regex search?

Question

Very inexperienced coder here, I have recently gotten a script working that uses regex to search for two different words occurring within a certain word limit. So I can search for "the" and "account" occurring within 10 words of each other, then my script prints the sentence it occurs in. However, my work requires me to search for lots of different work combinations and it has become a pain having to enter each word manually into the string /\W*(the)\W*\s+(\w+\s+){0,10}(account)|(account)\s+(\w+\s+){0,10}(the)/i; for example.

I would like to have something in the script where I can enter the words I want to search for just once, and they will be used in the string above. I have tried, what I think is, declaring variables like this:

var word1 = the
var word2 = account
/\W*(word1)\W*\s+(\w+\s+){0,10}(word2)|(word2)\s+(\w+\s+){0,10}(word1)/i;

But, again, very experienced coder so I'm a little out of my depth. Would really like something like the script snippet above to work in my full script listed below.

Here is my full working script without my attempt at declaring variables mentioned above:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var historySheet = ss.getSheetByName('master');  
var resultsSheet = ss.getSheetByName('results');
var totalRowsWithData = historySheet.getDataRange().getNumRows();
var data = historySheet.getRange(1, 1, totalRowsWithData, 3).getValues(); 
var regexp = /\W*(the)\W*\s+(\w+\s+){0,10}(account)|(account)\s+(\w+\s+){0,10}(the)/i;
var result = []; 
for (var i = 0; i < data.length; i += 1) {
     var row = data[i];
     var column = row[0];
     if (regexp.exec(column) !== null) {
        result.push(row); }}
if (result.length > 0) {
    var resultsSheetDataRows = resultsSheet.getDataRange().getNumRows();
    resultsSheetDataRows = resultsSheetDataRows === 1 ? resultsSheetDataRows : resultsSheetDataRows + 1; 
    var resultsSheetRange = resultsSheet.getRange(resultsSheetDataRows, 1, result.length, 3);
  resultsSheetRange.setValues(result);}}

I tried this solution but not sure I have done it correctly as it only enters results in logs and not printing in the "results" sheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var historySheet = ss.getSheetByName('Sheet1');  
var resultsSheet = ss.getSheetByName('Results1');
var totalRowsWithData = historySheet.getDataRange().getNumRows();
var data = historySheet.getRange(1, 1, totalRowsWithData, 3).getValues(); 
const regexpTemplate = '\W*(word1)\W*\s+(\w+\s+){0,10}(word2)|(word2)\s+(\w+\s+){0,10}(word1)';
var word1 = 'test1';
var word2 = 'test2';
var regexpString = regexpTemplate.replace(/word1/g, word1).replace(/word2/g, word2);
var regexp =  new RegExp(regexpString, 'i');
Logger.log(regexp); // /W*(the)W*s+(w+s+){0,10}(account)|(account)s+(w+s+){0,10}(the)/i
var result = []; 
for (var i = 0; i < data.length; i += 1) {
     var row = data[i];
     var column = row[0];
     if (regexp.exec(column) !== null) {
        result.push(row); }}
if (result.length > 0) {
    var resultsSheetDataRows = resultsSheet.getDataRange().getNumRows();
    resultsSheetDataRows = resultsSheetDataRows === 1 ? resultsSheetDataRows : resultsSheetDataRows + 1; 
    var resultsSheetRange = resultsSheet.getRange(resultsSheetDataRows, 1, result.length, 3);
  resultsSheetRange.setValues(result);}}

Diego · Accepted Answer · 2019-12-18 19:41:28Z

3

Use the RegExp contructor.

const regexpTemplate = '\\W*(word1)\\W*\\s+(\\w+\\s+){0,10}(word2)|(word2)\\s+(\\w+\\s+){0,10}(word1)';
var word1 = 'the';
var word2 = 'account';
var regexpString = regexpTemplate.replace(/word1/g, word1).replace(/word2/g, word2);
var regexp =  new RegExp(regexpString, 'i');
Logger.log(regexp); // /\W*(the)\W*\s+(\w+\s+){0,10}(account)|(account)\s+(\w+\s+){0,10}(the)/i

You can put this into a function to easily generate your new regular expression whenever you want to update the words.

/**
 * Generate the regular expression with the provided words.
 * @param {String} word1
 * @param {String} word2
 * @returns {RegExp}
 */
function generateRegExp(word1, word2) {
  const regexpTemplate = '\\W*(word1)\\W*\\s+(\\w+\\s+){0,10}(word2)|(word2)\\s+(\\w+\\s+){0,10}(word1)';
  var regexpString = regexpTemplate.replace(/word1/g, word1).replace(/word2/g, word2);
  return new RegExp(regexpString, 'i');
}

/**
 * Test the generateRegExp() function.
 */
function test_generateRegExp() {
  var word1 = 'the';
  var word2 = 'account';
  var regexp = generateRegExp(word1, word2); // /\W*(the)\W*\s+(\w+\s+){0,10}(account)|(account)\s+(\w+\s+){0,10}(the)/i

  // Use regexp just as you do in your script
  // i.e. if (regexp.exec(column) !== null) { result.push(row); }
}

Your final script could look something like this.

function printSentences() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var historySheet = ss.getSheetByName('Sheet1');  
  var resultsSheet = ss.getSheetByName('Results1');
  var totalRowsWithData = historySheet.getDataRange().getNumRows();
  var data = historySheet.getRange(1, 1, totalRowsWithData, 3).getValues(); 
  var result = []; 
  var regexp = generateRegExp("the", "account");
  for (var i = 0; i < data.length; i += 1) {
    var row = data[i];
    var column = row[0];
    if (regexp.exec(column) !== null) {
      result.push(row);
    }
  }
  if (result.length > 0) {
    var resultsSheetDataRows = resultsSheet.getDataRange().getNumRows();
    resultsSheetDataRows = resultsSheetDataRows === 1 ? resultsSheetDataRows : resultsSheetDataRows + 1; 
    var resultsSheetRange = resultsSheet.getRange(resultsSheetDataRows, 1, result.length, 3);
    resultsSheetRange.setValues(result);
  }
}

/**
 * Generate the regular expression with the provided words.
 * @param {String} word1
 * @param {String} word2
 * @returns {RegExp}
 */
function generateRegExp(word1, word2) {
  const regexpTemplate = '\\W*(word1)\\W*\\s+(\\w+\\s+){0,10}(word2)|(word2)\\s+(\\w+\\s+){0,10}(word1)';
  var regexpString = regexpTemplate.replace(/word1/g, word1).replace(/word2/g, word2);
  return new RegExp(regexpString, 'i');
}

edited Dec 18, 2019 at 19:41

answered Dec 17, 2019 at 14:36

Diego

9,5812 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joseph Davitt Over a year ago

Hi, thanks for the response! I tried this solution but not sure I am implementing it right, the script runs but only puts the results in the logs seems to not run the last part of the script ,i.e., it doesn't print the results into the "results" spreadsheet. I have edited my question with how I implemented your solution.

Diego Over a year ago

@JosephDavitt Sorry. I didn't properly escape the backslashes in regexpTemplate. You can see the exact change in the edit to my answer. I also included how I might use the generateRegExp() function in your script.

Collectives™ on Stack Overflow

Google Script, how can use variables with regex search?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related