0

For some reason, the solution described here doesn't work for me. Here is my whole code that should replace the 'PPP' with 'nothing'

function tests() {
  // I need to replace more occurrences of different strings, so this is just an example..
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange("F22:F24").getValues();
  var test = [];
  // this is a loop, to go through multiple cells that may contain the text, that needs to be replaced.
  for (var i = 0; i < range.length; i++) {
    var le = range.length;
    var stri = range[i].toString().replace("PPP", "");
    test[i] = stri;
  }

  //range.setValues(test);
  ss.getRange("F22:F24").setValues(test);
  
  var msg = ui.alert("Replaced?");
  return msg;

I tried a couple of things with setValue, setValues and simply with = and nothing... With the code above I get this error

Cannot convert Array to Object[][]

There must be a simple way to replace strings with App Script, considering that in Google Spreadsheets you can simply CTRL + H and replace any text with any text you want...

(I know I must learn Javascript, but time is now very tight...)

Community
  • 1
  • 1
Alex T
  • 35
  • 7
  • Forgot to mention - I need to replace multiple strings in cells from a column... every replacement is the same and the cells are from the same column. This means that I will use the same code a couple of times in one script. – Alex T Jul 10 '18 at 12:31
  • Whats the output of range[i]? – Joelgullander Jul 10 '18 at 12:44
  • The output depends on the [i] variable, but mostly the every cell contains a text, similar to this: >ProducătoPPPr:BlackBerry:1;Model:Z30:2;Generația conexiunii:4G:3;Diagonala display (inch):5.0:5;Multitactil:Da:6;Rezoluție (pixeli):720 x 1280:8;Tip ecran:Super AMOLED capacitive touchscreen, 16M colors:9;SIM:Single SIM: – Alex T Jul 10 '18 at 12:56

1 Answers1

0
  ss.getRange("F22:F24").setValues(test);

array test in your example is one-dimensional array but should be two-dimensional. So you just need simply to change the line: test[i] = stri; with test[i] = [stri]; and you code will work.

edward
  • 243
  • 3
  • 13
  • Thank you! This really does work, but it replaces only one found instance, but if the string, that needs to be replaced, is found a couple of times in the same string, this code will replace only the first instance. Is there a way to replace all the instances without drastically complicating this code? Or is it easier to run the same code 3-4 times? – Alex T Jul 11 '18 at 07:44
  • Yes, you can use Regular Expression to fnd all matches of string in another string globally: `for (var i = 0; i < range.length; i++) { var find = "PPP"; var regex = new RegExp(find, "g"); var stri = range[i].toString().replace(regex,""); test[i] = [stri]; }` – edward Jul 11 '18 at 18:10
  • Works perfectly! Thank you very much! – Alex T Jul 12 '18 at 07:28