Google Sheets Script find and replace function

Question

I'm trying to write a function in my google sheet script that will look at two columns that I'm basically using as key-value pairs where I have listed a name and a unique number next to it, and then compare the name key to every name in a Names column and when it finds a match, replace the value in the Names column with the corresponding ID (value). I'm totally new to Google Sheets and google scripts, although I do know JavaScript. I've been working on this for hours and have read through many different tutorials, but I can't figure out my problem. There is only one spreadsheet, if that matters. Here is what I have:

//910 is # rows in the Names column, 250 is # rows in the key-value pairs columns

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var searchRange = sheet.getRange(1, 1, 910, 250); 
var rangeValues = searchRange.getValues();

function myFunction(){
  for(i=0; i<910; i++){
    for(j=0; j<250; j++){
      var nameToReplace = rangeValues[i][5]; //(Column E)
      var match = rangeValues[j][9];         //key in the key-value pairs (Column I)
      var id = rangeValues[j][8];            //value in the key-value pairs (Column H)
      if(nameToReplace === match){
        nameToReplace.setValue(id);
      };
    };
  };
};

It's currently giving me an error saying "TypeError: nameToReplace.setValue is not a function (line 13, file "Code")". It's fairly simple code so I'm sure it's a simple beginner's mistake but for the life of me I can't figure out what the problem is. TIA!

@Diego What is nameToReplace then? And is there a method to set the value of it? — MRB
– MRB, Commented Nov 24, 2020 at 18:32

Jason E. · Accepted Answer · 2020-11-24 21:41:41Z

1

I tried replicating your code on my end and it seems that the problem is that the nameToReplace variable is not a Range so you cannot perform a setValue() function on it.

On your code:

var rangeValues = searchRange.getValues();

you are actually getting the value of a cell instead of having a cell range value.

As for a solution, I replicated your code and tried using createTextFinder in replacing the values in the names column. Also, I modified the process of getting the values using getValues to only get the specific values that you want. Please see sample code below(may not be the exact code that you will use):

//910 is # rows in the Names column, 250 is # rows in the key-value pairs columns

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var pairValues = sheet.getRange(1, 8, 250, 2).getValues();
var namesRange = sheet.getRange(1, 5, 910, 1)
function myFunction(){
  for(var i=0; i<250; i++){
    var match = pairValues[i][1];         //key in the key-value pairs (Column I)
    var id = pairValues[i][0];            //value in the key-value pairs (Column H)
    namesRange.createTextFinder(match).replaceAllWith(id);
  };
};

edited Nov 24, 2020 at 21:41

answered Nov 24, 2020 at 18:52

Jason E.

1,2111 gold badge6 silver badges10 bronze badges

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

5 Comments

MRB Over a year ago

I actually tried this originally, but it ended up timing out because each getRange call goes to the server directly and back, so to do it for almost 1,000 rows took quite a long time and google ended up force stopping it. (At least, this is my understanding based on what I read online.) The recommendation was to use getValues() instead because it is only one call to the server. However, I can't get my code with getValues() to work.

Jason E. Over a year ago

I see. Have you tried using getRange after the condition is satisfied inside your loop? Please see my edited answer.

MRB Over a year ago

I just tried it (after changing i and j back to 1) and still got: Exceeded maximum execution time.

Jason E. Over a year ago

I replicated your code and came up with a solution that only uses 1 loop instead of a nested one. Please see my updated answer. :)

MRB Over a year ago

It worked perfectly!!! Thank you so much, you are a lifesaver!!

CMB · Accepted Answer · 2020-11-24 20:37:03Z

0

To have only one call to the server for writing to the spreadsheet, you can use setValues after the nested loop has been completed.

Please see sample code below (this may not be the exact code that you will use):

function myFunction(){
  for(i=1; i<11; i++){
    for(j=1; j<11; j++){
      var nameToReplace = rangeValues[i][5];
      var match = rangeValues[j][9];
      var id = rangeValues[j][8];
      if(nameToReplace === match){
        rangeValues[i][5] = id
      };
    };
  };
  searchRange.setValues(rangeValues);
};

answered Nov 24, 2020 at 20:37

CMB

5,1911 gold badge8 silver badges17 bronze badges

Collectives™ on Stack Overflow

Google Sheets Script find and replace function

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related