Find and replace text only while they are inside parentheses with Google Apps Script-CodePudding

I recently started using Apps Script to automate some processes in Google Sheets.

I need to add "/" between numbers only while they are inside parentheses.

example "123(123)" to "123(1/2/3)"

I've played around with textFinder but no luck.

CodePudding user response：

I'm not very good at Regex. Some experts can probably do it one line, But this will work.

function testSlash() {
  addSlash("123(123)456(456)");
  addSlash("(123)123(456)456");
  addSlash("(123)(123)456(456)(789)");
}

function addSlash(a) {
  try {
    let b = a.match(/(\(\d{1,})/g);
    let d = [];
    b.forEach( c => {
        let e = c.match(/\d/g);
        let f = "";
        e.forEach( g => f = f g "/" );
        f = "(" f.slice(0,-1);
        d.push(f);
      }
    );
    // now let rebuild the string
    b.forEach( (c,i) => a = a.replace(c,d[i]) );
    console.log(a);
  }
  catch(err) {
    console.log(err);
  }
}

6:49:56 AM  Notice  Execution started
6:49:58 AM  Info    123(1/2/3)456(4/5/6)
6:49:58 AM  Info    (1/2/3)123(4/5/6)456
6:49:58 AM  Info    (1/2/3)(1/2/3)456(4/5/6)(7/8/9)
6:49:57 AM  Notice  Execution completed

CodePudding user response：

Try String.prototype.replace() function with a function as the replacement

var str      = '123(123)';
var replaced = str.replace( /\((. ?)\)/g, function( match, group1 ){
    return '('   group1.split( '' ).join( '/' )   ')';
} );

console.log( replaced );

Regex explanation:

\( Match open bracket

(. ?) Match as little non-line-breaks characters as possible in a group

\) Match closing bracket

g Global flag, allow multiple matches

group1.split( '' ).join( '/' ) splits all characters in the first group in an array and joins the array with /, so "123" will become "1/2/3"

CodePudding user response：

You can use an Apps Script function to insert slashes between bracketed numbers in selected cells and assign that function to a menu item so that you can easily format values in the spreadsheet.

Paste the following code in Extensions > Apps Script, completely replacing the boilerplate code there, save and close. Then reload the spreadsheet. There will be a new menu item that lets you insert slashes. You will be asked to authorize the function the first time you run it.

'use strict';
/* @OnlyCurrentDoc */

/**
* Simple trigger that runs each time the user opens
* the spreadsheet.
*
* @param {Object} e The onOpen() event object.
*/
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Format numbers')
    .addItem('Add slashes between bracketed numbers', 'insertSlashes')
    .addToUi();
}

function insertSlashes() {
  const regex = /\((\d )\)/g;
  const ss = SpreadsheetApp.getActive();
  const range = ss.getActiveRange();
  const values = range.getValues();
  if (!values.flat().some(value => String(value).match(regex))) {
    ss.toast('The selected cells do not contain bracketed numbers. Nothing to do.');
    return;
  }
  const result = values.map(row => row.map(value =>
    typeof value === 'string'
      ? insertSeparators_(value, regex, '/', '()')
      : value
  ));
  range.setValues(result);
  ss.toast('Done.');
}

function insertSeparators_(string, regex, separator, brackets = ['', '']) {
  return string.replace(regex, (_, string) =>
    brackets[0]   [...string].join(separator)   brackets[1]
  );
}