-1

I have a script pulling information from a webpage into column C. The information contains xxx,MKE . I would like to replace the value of 'MKE' to Milwaukee, WI. in column C.

Is this possible?

I have UI code that will do this; however, I would like the script to auto replace once the value is entered.

UI CODE:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("c1:c10");
  var to_replace = "MKE";
  var replace_with = "Milwaukee, WI";
  replaceInSheet(sheet,range, to_replace, replace_with);
}

function replaceInSheet(sheet, range, to_replace, replace_with) {
  //Confirm
  var ui = SpreadsheetApp.getUi(); 
  var spread = SpreadsheetApp.getActiveSpreadsheet();

  var result = ui.alert(
     "Will update " + to_replace + " to " + replace_with + " ",
     'Are you sure you want to continue?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {

    // User clicked "Yes".
    spread.toast("Will update " + to_replace + " to " + replace_with + " ", "ALERT");

    var data  = range.getValues();

    var oldValue="";
    var newValue="";
    var cellsChanged = 0;

    for (var row=0; row<data.length; row++) {
      for (var item=0; item<data[row].length; item++) {
        oldValue = data[row][item];
        newValue = data[row][item].replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[row][item] = newValue;
        }
      }
    }
    range.setValues(data);
    spread.toast(cellsChanged + " cells changed", "STATUS");
  }
  else {
    // User clicked "No" or X in the title bar.
    spread.toast("No action taken", "ABANDONED");
  }
}
  • If you are using a script to import the data and want to change the values as soon as they are available, you could include that on the import script, anyway, this looks to be more a question about javascript than a question about Google Sheets / Google Apps Script and in such case it could be more appropriate for Stack Overflow, but the question isn't clear enough to vote to migrate it to that site. – Rubén May 13 '17 at 0:44
  • Please don't vandalize your question. It may be of use to someone else in the future. – jonsca May 29 '17 at 19:45
1

It always amazes me to see such complex iterative solutions where a simple formula would suffice. Assuming your imported information is in Sheet1, make another sheet and put the following formula in A1 of that new sheet:

={Sheet1!A:B,
  ARRAYFORMULA(REGEXREPLACE(Sheet1!C:C, "MKE", "Milwaukee, WI")),
  Sheet1!D:Z
 }

Then use the results in the new sheet for whatever ends you want.

As a bonus, the solution does not modify any data in place, so you don't need to ask the user for confirmation. It is purely functional, operating on immutable results of the original import.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.