Google Apps Script: Find and replace in specific column only

Question

I'm using the code below to run a search and replace in a Google spreadsheet. It's based on an answer I found on stackoverflow.

My issue is that I can't make it run for only one specific column in the spreadsheet.

I want to search for ABC in column D, and then replace ABC with Subject: ABC in column D only.

I have tried defining var values = row[3]; but that throws up an error (Cannot find method setValues(string). (line 27, file "replace")). I've tried various modifications like that but that doesn't work.

How can I search and replace within a specific column only?

function run() {
runReplaceInSheet();
replaceInSheet();
}

function runReplaceInSheet() {

var spreadsheet = SpreadsheetApp.openById("ID"); 
var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]); 
var startRow = 2; // First row of data to process
var numRows = 2; //  number of rows to process
// Fetch the range of cells 
var dataRange = sheet.getRange(startRow, 1, numRows, 22) // Numbers of rows to process
// Fetch values for each row in the Range
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var values = sheet.getDataRange().getValues();  

    // Replace Names

    replaceInSheet(values, 'ABC', 'Subject: ABC');

    //write the updated values to the sheet, again less call;less overhead
    sheet.getDataRange().setValues(values);        

}
}

function replaceInSheet(values, to_replace, replace_with) {

//loop over the rows in the array
for (var row in values) {

    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value) {
        return original_value.toString().replace(to_replace, replace_with);
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;


}
}

EDIT

This works but it's adding Subject twice, i.e. Subject: Subject: ABC

function runReplaceInSheet() {

var spreadsheet = SpreadsheetApp.openById("ID"); // UPDATE ID
var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);     // UPDATE number in square brackets
var range = sheet.getRange("D2:D4");
//  get the current data range values as an array
//  Lesser calls to access the sheet, lower overhead 
var startRow = 2; // First row of data to process
var numRows = 2; // UPDATE number of rows to process
// Fetch the range of cells 
var dataRange = sheet.getRange(startRow, 4, numRows, 1) // Numbers of rows to process
    // Fetch values for each row in the Range
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var v = row[3]; // edit: don't need this
    var values = range.getValues();  

    // Replace Names

    replaceInSheet(values, 'ABC', 'Subject: ABC');

    //write the updated values to the sheet, again less call;less overhead
    range.setValues(values);        

}
}

Maybe you should replace your var dataRange = sheet.getRange(startRow, 1, numRows, 22) to look only in the D column ? like var dataRange = sheet.getRange(startRow, 4, numRows, 1) ? And use it to replace your values, cause you're replacing ABC in all your data (sheet.getDataRange().getValues();). — Pierre-Marie Richard
At the moment I can't tie var values to a specific range, it's searching the whole sheet I think. Tried stuff like var values = row[3].getDataRange().getValues(); - but it doesn't like that, get "Cannot call method "getDataRange" of undefined" — touto
If you want to get the values from column D, maybe try to get row[i][3], to get all the rows, but only the values from D — Pierre-Marie Richard
I managed to specify the column, but it's adding Subject twice in the column. Will adjust the code in the original post - what's the issue there? — touto
Maybe your code pass twice on a element and detect "ABC" in "Subject: ABC", so he replace it which give "Subject: Subjetc: ABC" — Pierre-Marie Richard

Cooper Cooper · Accepted Answer · 2017-05-11T04:46:15

Here's some code that works for me.

function findingReplacing()
{
  var s = SpreadsheetApp.getUi().prompt("Search String", "Please enter desired search string", SpreadsheetApp.getUi().ButtonSet.OK).getResponseText();
  var rng = SpreadsheetApp.getActiveSheet().getRange('D:D');
  var rngA = rng.getValues();
  for(var i=1;i<rngA.length;i++)
  {
    if(rngA[i][0]==s)
    {
      rngA[i][0]='Subject: ' + s;
    }
  }
  rng.setValues(rngA);
}

Here's what my data looks.

I searched for David Stew in column D and it replaced that string with Subject: David Stew for each occurrence of David Stew in Column D only. This is just some data that I already had.

Google Apps Script: Find and replace in specific column only

1 Answers