Modify Code - Find & Replace - Google Sheets / Apps Script

Question

The below code will find NAME 1 and replace it with NM1 as per the replaceInSheet line of code

I would like the code to look at a list of data in SHEET 2 to do the Find&Replace IN SHEET 1. So Column A has names and is repalced by what is in Column B

function runReplaceInSheet() {

var spreadsheet = SpreadsheetApp.openById(""); // UPDATE ID
var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]); // UPDATE number in square 
brackets
var range = sheet.getRange("F2:F");
//  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. start at Row 3
var numRows = 2; // Specify what column to look at. 
// Fetch the range of cells 
var dataRange = sheet.getRange(startRow, 150 , 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[2]; // edit: don't need this
var values = range.getValues();  



replaceInSheet(values, 'Name 1', 'N1');
replaceInSheet(values, 'Name 2', 'N2');

//write the updated values to the sheet, again less call;less overhead
range.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;
}}

I have to apologize for my poor English skill. Unfortunately, I cannot understand about I would like the code to look at a list of data in SHEET 2 to do the Find&Replace IN SHEET 1. So Column A has names and is repalced by what is in Column B and your sample image. In order to correctly understand about your current issue and goal, can you provide the sample input and output you expect? — Tanaike
– Tanaike, Commented Sep 6, 2020 at 7:51
Thank you for replying. I thought that when the sample input and output you expect might help to understand about your current situation and goal. — Tanaike
– Tanaike, Commented Sep 6, 2020 at 7:55
here is first spreadsheet docs.google.com/spreadsheets/d/… — Tom Sawkins
– Tom Sawkins, Commented Sep 6, 2020 at 7:56

Tanaike · Accepted Answer · 2020-09-06 08:57:18Z

I believe your goal as follows.

You want to retrieve the values of columns "A" and "B" of "Replace Agency Name" in "Master - Agency Name Change".
You want to change the column "F" of "D2D MACRO" in "D2D Macro" by the column "B" corresponding to the column "A" of "Replace Agency Name".

Modification points:

In your script, getValues() is used in the loop. In this case, the process cost will become high.
The values for replacing are directly used in the script.

From above situation, I would like to propose the following flow.

Retrieve values for replacing are retrieved as the source values from the sheet "Replace Agency Name" in "Master - Agency Name Change".
Replace the values using the retrieved source values.

Sample script 1:

In this sample script, the values are replaced by setValues.

function myFunction() {
  // 1. Retrieve values for replacing are retrieved as the source values from the sheet "Replace Agency Name" in "Master - Agency Name Change".
  var srcSheet = SpreadsheetApp.openById("###").getSheetByName("Replace Agency Name");
  var srcValues = srcSheet.getRange(`A2:B${srcSheet.getLastRow()}`).getValues();
  var srcObj = srcValues.reduce((o, [a, b]) => Object.assign(o, {[a]: b}), {});
  
  // 2. Replace the values using the retrieved source values.
  var dstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("D2D MACRO");
  var dstRange = dstSheet.getRange(`F2:F${dstSheet.getLastRow()}`);
  var dstValues = dstRange.getValues().map(([f]) => [srcObj[f] || f]);
  dstRange.setValues(dstValues);
}

Sample script 2:

In this sample script, the values are replaced by TextFinder.

function myFunction() {
  // 1. Retrieve values for replacing are retrieved as the source values from the sheet "Replace Agency Name" in "Master - Agency Name Change".
  var srcSheet = SpreadsheetApp.openById("###").getSheetByName("Replace Agency Name");
  var srcValues = srcSheet.getRange(`A2:B${srcSheet.getLastRow()}`).getValues();
  var dstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("D2D MACRO");

  // 2. Replace the values using the retrieved source values.
  var dstRange = dstSheet.getRange(`F2:F${dstSheet.getLastRow()}`);
  srcValues.forEach(([a, b]) => {
    if (a.toString() != "") dstRange.createTextFinder(a).matchEntireCell(true).replaceAllWith(b)
  });
}

Note:

In above scripts, srcSheet is "Replace Agency Name" in "Master - Agency Name Change". And dstSheet is "D2D MACRO" of the active Spreadsheet. Please be careful this.

Collectives™ on Stack Overflow

Modify Code - Find & Replace - Google Sheets / Apps Script

1 Answer 1

Modification points:

Sample script 1:

Sample script 2:

Note:

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Sample script 1:

Sample script 2:

Note:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related