Creating a button to find replace using Macros-CodePudding

I want to create a button to perform a find replace for a specific set of data in my sheet. I've had a look at other questions, but can't seem to get it to work!

This is a replica of my sheet: https://docs.google.com/spreadsheets/d/1KX4xBzIPq468WN57HKRelkCwN4sr_9Nd3v36m4jj4jI/edit#gid=0

The whole of column B is the data set I want to perform the F&R in. The values I want to use:

Find: " "
Replace " "

Essentially, replacing the spaces between words in column B with .

This, with some concatenation will then create some URLs for me.

CodePudding user response：

function myFunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const sr = 3;//data start row
  const vs = sh.getRange(sr, 2, sh.getLastRow() - sr   1).getDisplayValues().map(r => [r[0].replace(/ /g, ' ')]);
  sh.getRange(sr, 2, vs.length, vs[0].length).setValues(vs);
}

If you wish to trim off spaces from beginning and end you can add trim();

function myFunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const sr = 3;//data start row
  const vs = sh.getRange(sr, 2, sh.getLastRow() - sr   1).getDisplayValues().map(r => [r[0].trim().replace(/ /g, ' ')]);
  sh.getRange(sr, 2, vs.length, vs[0].length).setValues(vs);
}

To insert a button you can use insert drawing and draw the button save and close. Click in the upper right corner and select the option to attach to script. That's it. Personally I like using the sidebar and creating buttons in html.

CodePudding user response：

In your sample Spreadsheet, it seems that the cells "A2, "B2" are the values of https://www.harrods.com/en-gb/shopping/women-clothing/brands and 4 Moncler Simone Rocha, respectively. And, at the output column, the cell "C2" is the formula of =CONCATENATE($A$2,"#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA^",B2). In this case, how about modifying the formula as follows?

From:

=CONCATENATE($A$2,"#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA^",B2)

To:

=CONCATENATE($A$2,"#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA^",SUBSTITUTE(B2," "," "))

By this modification, https://www.harrods.com/en-gb/shopping/women-clothing/brands#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA^4 Moncler Simone Rocha is obtained at the cell "C2".

If you want to achieve this using Google Apps Script, how about the following script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save it. And when you want to run this script with an image button, please put an image to the Spreadsheet and assign the function name of myFunction to the button. By this, when you click the button, the script works. And, when the script is run, a single space of the values of column "B" are replaced with .

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Filter URLs");
  sheet.getRange("B2:B"   sheet.getLastRow()).createTextFinder(" ").replaceAllWith(" ");
}

Note:

As additional information, when you want to convert your current formula with Google Apps Script, you can also use the following script.

  function myFunction2() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Filter URLs");
    const url = sheet.getRange("A2").getValue();
    const values = sheet.getRange("B2:B"   sheet.getLastRow()).getValues().map(([b]) => [url   "#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA^"   b.replace(/ /g," ")]);
    sheet.getRange(2, 4, values.length, 1).setValues(values);
  }

When this script is run, the URLs are put to the column "D". When you want to put the column "C", please modify getRange(2, 4, values.length, 1) to getRange(2, 3, values.length, 1).

References:

CodePudding user response：

I've added a new sheet ("Filter URLs - Erik") to your sample spreadsheet. There, I deleted everything in Column C (including the header) and placed the following single formula in cell C1:

=ArrayFormula({"Output";IF(B2:B="",,$A$2&"#dcp=1&dppp=100&OrderBy=rank&Filter=ABRA^"&SUBSTITUTE(TRIM(B2:B)," ","%"))})

Expanding on one of @Tanaike's points, this one formula will create the header and all results for all rows without the need for dragging the formula, using scripts or using other Find/Replace methods.

CodePudding user response：

If you're only looking to create urls, use

=ENCODEURL(B2)

instead. This will automatically convert all non url characters to it's percent encoding.