0

Referencing: Google Apps Script for Multiple Find and Replace in Google Sheets

I'd like to use this same code for my purposes but only within 1 column... Any advice on how to limit this script to only one column? The reason is that when I run this to change Department Names in Column J, it works perfectly, except it also changes the 'Data Type' in my Date (Columns L and M) to include timezones, and that messes up other Sheets referencing those dates... Thanks!

Corey Pomkoski
  • 11
  • 1
  • Welcome. The key to your problem is `var values = sheet.getDataRange().getValues();`. **`getDataRange()`** pulls data from **ALL** the columns. You need something like `var values = sheet.getRange(1,10,rows).getValues();` to limit the data to just Column J (column 10). Use/adapt the "trick" described [here](https://stackoverflow.com/a/17637159/1330560) by Mogsdad to get the last row for column J, which will give you the "rows" value. Then when you've finished, you use something like sheet.getRange(1,10,rows).setValues(values); – Tedinoz Feb 22 '19 at 05:02
  • Thanks @Tedinoz! I took your advice and messed around, tripping & stumbling along the way. I ended up switching out `function replaceInSheet(sheet, to_replace, replace_with) { //get the current data range values as an array var values = **sheet.getDataRange()**.getValues();` with `function replaceInSheet(sheet, to_replace, replace_with) { //get the current data range values as an array var values = **sheet.getRange('J:J')**.getValues();` – Corey Pomkoski Feb 28 '19 at 19:57

1 Answers1

0

I ended up switching out 

function replaceInSheet(sheet, to_replace, replace_with) {
//get the current data range values as an array 
var values = sheet.getDataRange().getValues();

with 

function replaceInSheet(sheet, to_replace, replace_with) {
//get the current data range values as an array 
var values = sheet.getRange('J:J').getValues();
Tedinoz
  • 5,911
  • 3
  • 25
  • 35
Corey Pomkoski
  • 11
  • 1
  • Depending on the number of rows in the sheet vs the number of rows of data, looping through "J:J" could be inefficient. Mogsdad's trick works when you have data in every cell; but if there are any blank cells between the cells with content, it's a disaster. A reliable alternative would be `var sheetLastRow = sheet.getLastRow();` [(Doc Reference)](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getlastrow). This works at a Sheet level returning "the position of the last row that has content"; you could use: `var values = sheet.getRange(1,10,sheetLastRow).getValues();` – Tedinoz Mar 01 '19 at 05:58
  • On a tangent, I am pleased for you that you "messed around, tripping & stumbling along the way." So many people are looking for "quick" answers (which do not exist, BTW), but the only way to learn is to get your hands dirty with the code. Good on you. – Tedinoz Mar 01 '19 at 06:03