How can I limit the columns this script works in?

Question

I have a script for substituting Names to Email Addresses, It works great! Issue is that it does this on the entire sheet and I need it to only work in a single Column.

This is for G-Sheets to Substitute Names for Email Addresses only in a single column (A). I have tried adding variables and in line 5 putting a range in the "sheet.getDataRange().getValues();" but I don't know how to get this to work. I am new to Java Script. Any help would be appreciated. Here is the Script:

    function runReplaceInSheet(){
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
      //  get the current data range values as an array
      //  Fewer calls to access the sheet -> lower overhead 
      var values = sheet.getDataRange().getValues();  


      // Replace Staff Names
      replaceInSheet(values, 'Ted', 'ct@example.com');
      replaceInSheet(values, 'Tilly', 'lt@example.com');
      replaceInSheet(values, 'Tina Zed', 'xyz@example.com');
      replaceInSheet(values, 'Tim Que', 'qt@example.com');

      // Write all updated values to the sheet, at once
      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;
      }
    }

I just need this to work In a range that I can specify, not the entire sheet.

Possible duplicate of [get value in one column in spreadsheet using google apps script](https://stackoverflow.com/questions/14991030/get-value-in-one-column-in-spreadsheet-using-google-apps-script) — , Jun 06 '19 at 18:23

score 0 · Answer 1 · answered Jun 06 '19 at 18:58

You can get the values of a certain range of the sheet by using the getRange method.

For example, if you are interested in the data from first row of column A to second row of column B you could do:

var values = sheet.getRange("A1:B2").getValues();

You can pass the selector as a parameter of your function runReplaceInSheet to make it more reusable:

function runReplaceInSheet(range){
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
    //  get the current data range values as an array
    //  Fewer calls to access the sheet -> lower overhead 
    var values = sheet.getRange(range).getValues();  


    // Replace Staff Names
    replaceInSheet(values, 'Ted', 'ct@example.com');
    replaceInSheet(values, 'Tilly', 'lt@example.com');
    replaceInSheet(values, 'Tina Zed', 'xyz@example.com');
    replaceInSheet(values, 'Tim Que', 'qt@example.com');

    // Write all updated values to the sheet, at once
    sheet.getDataRange().setValues(values);
}

and then call your function with the range:

runReplaceInSheet("A1:B2");

How can I limit the columns this script works in?

1 Answers1