Google Sheet Script : To set value to lowercase and replace some special chars

[Manuel Krögler]

Hi there,

I'm new to Google App-Scripting and I'm not very familiar with coding at all. 

I thought what I want would be simple, but I have some problems :-) 

All I want is to change the value of a specific row to lower case and replace some chars. 

Here is what I have so far.

function onEdit(e) {

  var range = e.range;

  var value = range.getValue();

  var sheet = range.getSheet();

  var sheetName = sheet.getName();

  var columnOfCellEdited = range.getColumn();

 

// only column 3 (title' is affected of this

  if (columnOfCellEdited === 3) {

    if (sheetName === 'Shortener' && 

        

        range.getRow() > 1 && 

        range.getColumn() > 1 && 

        

        typeof value == 'string') {   

        

        if(!range.getFormula()) {

          range.setValue(value.toLowerCase());

        } else {

        

            if(range.getFormula().substring(0,6).toLowerCase() == '=LOWER') {

              return;

            } else {

              range.setFormula('=LOWER(' + range.getFormula().substring(1) + ')');

            }        

         }

         

      var value = range.getValue();

      var range = e.range;

      var value = range.getValue();

      var sheet = range.getSheet();

      var sheetName = sheet.getName();

      

            Utilities.sleep(8000);

        // chars to replace

      var replace_char_1 = e.value.split('è').join('e');

      var replace_char_2 = e.value.split('é').join('e');

      var replace_char_3 = e.value.split(' ').join('_');

      var replace_char_4 = e.value.split('.').join('_');

      var replace_char_5 = e.value.split('ä').join('ae');

      var replace_char_6 = e.value.split('ö').join('oe');

      var replace_char_7 = e.value.split('ü').join('ue');

      var replace_char_8 = e.value.split('+').join('_');

          

//////////// replace chars section ////////////

      if (replace_char_1 != e.value) {

        e.range.setValue(replace_char_1);

      }

      

      if (replace_char_2 != e.value) {

        e.range.setValue(replace_char_2);

      }

      

      if (replace_char_3 != e.value) {

        e.range.setValue(replace_char_3);

      }

      

      if (replace_char_4 != e.value) {

        e.range.setValue(replace_char_4);

      }

      

      if (replace_char_5 != e.value) {

        e.range.setValue(replace_char_5);

      }

      

      if (replace_char_6 != e.value) {

        e.range.setValue(replace_char_6);

      }

            

      if (replace_char_7 != e.value) {

        e.range.setValue(replace_char_7);

      }

      

      if (replace_char_8 != e.value) {

        e.range.setValue(replace_char_8);

      }      

      

        }

  

  }

}

The issues are:

• After the lower-case part is done, as soon as the replace-part comes, the chars changes back to the original case.
• Only 1 of the 8 replace case will be done. 
• Is it possible instead of onEdit that these changes will be made, by clicking on a new menu item? 

Thank you very much for your help

Kind regards

Manuel

[Clark Lind]

Just looking at it quickly, try commenting out the second set of variable declarations. That is likely changing the content of your values.

[Manuel Krögler]

Hi 

Thank you for your quick respond.

Unfortunately, removing the second set of variable declaraitons didn't solve the problem. 

Any other ideas?

[Jean-Luc Vanhulst]

You will need to use the value from your first replace to next replace and so on..?

And then do just one setvalue() at the end... right now you will save a separea string with only one of the characters replaced for each 

So just:

  var char =  e.value.split('è').join('e');
        char = char.split('é').join('e');
         char = char.split(' ').join('_');
          char = char.split('.').join('_');
           char = char.split('ä').join('ae');
            char = char.split('ö').join('oe');
             char = char.split('ü').join('ue');
             char = char.split('+').join('_');

          range.setValue(char);

Also why sleep(8000)?

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/040dad04-5fcc-47f0-aba4-87ef8bb6da4eo%40googlegroups.com.

[kle...@ritenourschools.org]

Problem with replace. I have an email body in a single cell on the second sheet of my spreadsheet. I am getting data from a form and saving it on the first sheet. I can see that I have all the data in the logger. I can see my email body. I can only make one substitution in the email body. It does not matter which of the three values is first. that is the only one substituted.

var templateSheet = ss.getSheetByName("EmailTemplate");

  var mergeTemplate = templateSheet.getRange('A1').getValue();  

  var tempstring = mergeTemplate.toString().replace("$$timeIn$$", timeIn);

  console.log(tempstring);

  tempstring = tempstring.replace("$$whereIn$$", whereIn);

  console.log(tempstring);

  tempstring = tempstring.replace("$$whenIn$$}", whenIn);

  console.log(tempstring);

[Jean-Luc Vanhulst]

Hard to say without seeing the value of A1. Your third one has a } it seems added so that probably explains that one not replacing?

CONFIDENTIALITY NOTICE: Any information contained in or attached to this message is intended solely for the use of the intended recipient(s). Any review, retransmission, dissemination of, or taking of any action in reliance upon, this communication by persons other than the intended recipient(s) is prohibited.  If you are not the intended recipient or a person responsible for delivering this message to the intended recipient(s), reply to sender to inform us that you have received this message in error and destroy all copies of the original message. Please do not copy, distribute or use this email or the information contained in it for any purpose without permission of the sender.

--


You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.


To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0695008b-be45-4b62-a6af-8287517995ddn%40googlegroups.com.

--

[Lissa Klein]

I compared my template and code and corrected the discrepancies. Now none of the values are being substituted. Any help you can give would be welcome. Thank you. 

// DO NOT CHANGE COLUMN NAMES - You can add more columns without breaking this code
function formResponse(e){
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var logSheet= SpreadsheetApp.getActiveSheet();
   
  //var timeIn = e.values[1];
  var d = new Date();
  var timeIn = d.toLocaleTimeString();           // console.log(e.values[0]);
  // console.log(timeIn);
   
  var emailNum  = getColumnByName(logSheet, "Email Address");
  var emailIn = e.values[emailNum - 1];          //var emailIn = e.values[1];
  // console.log(emailIn);
   
  var whereNum = getColumnByName(logSheet, "Where do you want to go?");
  var whereIn = e.values[whereNum - 1];           //var whereIn = e.values[2];
  console.log(whereIn);
   
  var whenNum = getColumnByName(logSheet, "When do you want to go?");
  var whenIn = e.values[whenNum - 1];          //var whenIn = e.values[3];
  console.log(whenIn);
   
  var subjectLine = "Your form was received";

 
  var templateSheet = ss.getSheetByName("EmailTemplate");
  var mergeTemplate = templateSheet.getRange('A1').getValue();  

  var tempstring = mergeTemplate.toString().replace("${timeIn}", timeIn);
  console.log(tempstring);
  tempstring == tempstring.replace("${whereIn}", whereIn);
  console.log(tempstring);
  tempstring == tempstring.replace("${whenIn}", whenIn);
  console.log(tempstring);
 
  MailApp.sendEmail( emailIn, subjectLine, tempstring );
 
  //         function getColumnByName
  function getColumnByName(sheet, name) {
    var range = sheet.getRange(1, 1, 1, sheet.getMaxColumns());
    var values = range.getValues();
    for (var row in values) {
      for (var col in values[row]) {
        if (values[row][col] == name) {
          return parseInt(col)+1;
        }
      }
    }
  }
}

My template:

Greetings:

Your form was received at ${"timeIn"}. You indicated you wanted to go to ${"whereIn"} ${"whenIn"}. We will get back to you when polling is complete.

The Staff  

The log:

Oct 4, 2020, 7:32:26 AM

Debug

the beach

Oct 4, 2020, 7:32:26 AM

Debug

today

Oct 4, 2020, 7:32:26 AM

Debug

Greetings: Your form was received at ${"timeIn"}. You indicated you wanted to go to ${"whereIn"} ${"whenIn"}. We will get back to you when polling is complete. The Staff

Oct 4, 2020, 7:32:26 AM

Debug

Greetings: Your form was received at ${"timeIn"}. You indicated you wanted to go to ${"whereIn"} ${"whenIn"}. We will get back to you when polling is complete. The Staff

Oct 4, 2020, 7:32:26 AM

Debug

Greetings: Your form was received at ${"timeIn"}. You indicated you wanted to go to ${"whereIn"} ${"whenIn"}. We will get back to you when polling is complete. The Staff

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/PX83Te55oqE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKTLZ52fZXZgyb5paQCOLJAJHOTedQjsgZNPJYSYJrabAMG2Qg%40mail.gmail.com.

--

L.Klein

[Jean-Luc Vanhulst]

The code will only replace ${whereIn} you template says ${“whereIn”} with quotes which is a different string.  Make it without quotes in the template 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKyj8xAMh%3DRrD1-AWYEv%2BBpC5nTrXhj1KsJh7nFFNH5RnTy4nA%40mail.gmail.com.

[Lissa Klein]

I am so frustrated. I am back where I was before. Only the first value is being substituted.  I really appreciate your help.

Template:

Code:

Log:

Head	formResponse	Trigger	Oct 4, 2020, 9:00:43 AM	1.089 s	Completed  
Stackdriver logs Oct 4, 2020, 9:00:44 AM Debug the park Oct 4, 2020, 9:00:44 AM Debug today Oct 4, 2020, 9:00:44 AM Debug Greetings: Your form was received at 9:00:43 AM. You indicated you wanted to go to ${whereIn} ${whenIn}. We will get back to you when polling is complete. The Staff Oct 4, 2020, 9:00:44 AM Debug Greetings: Your form was received at 9:00:43 AM. You indicated you wanted to go to ${whereIn} ${whenIn}. We will get back to you when polling is complete. The Staff Oct 4, 2020, 9:00:44 AM Debug Greetings: Your form was received at 9:00:43 AM. You indicated you wanted to go to ${whereIn} ${whenIn}. We will get back to you when polling is complete. The Staff REFRESH

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKTLZ50EMVOy5c9nAaxNiyB3uOed24shmKi6RHbrqP2y%2BzqZbQ%40mail.gmail.com.

[Clay Smith]

It looks like you are testing for equality with the double == in your code but what you are intending is to assign the replaced string. 

Try chaining your replacements like below. 

  var tempstring = mergeTemplate.toString().replace("${timeIn}", timeIn).replace("${whereIn}", whereIn).replace("${whenIn}", whenIn);
  console.log(tempstring);

[Laurie Nason]

Hi Lissa,

I think your problem may be that you have a "==" where you should have an "=" 

tempstring == tempstring.replace("${whereIn}", whereIn);
  console.log(tempstring);
  tempstring == tempstring.replace("${whenIn}", whenIn);

The double equals checks for equivalency - the single equals assigns the values.

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKyj8xDTUZYkr_2jKtVOkkjA4repDz9daXxpRTdZypJcT1pf9Q%40mail.gmail.com.

--

Laurie Nason 

The KAUST School – Operational Analyst

Information Technology Support
Deep in the Bowels of the School
Landline: +966-12-808-6853

Mobile: +966-54-564-3698

[Lissa Klein]

I tried with == and =. Only the first value was substituted. 

  var tempstring = mergeTemplate.toString().replace("${timeIn}",

       timeIn).replace("${whereIn}", whereIn).replace("${whenIn}", whenIn);

Someone suggested the above and it populates all three fields.

My original idea below only populates the first field.
  // var tempstring = mergeTemplate.toString().replace("${timeIn}", timeIn);
  // console.log(tempstring);
  // tempstring == tempstring.replace("${whereIn}", whereIn);
  // console.log(tempstring);
  // tempstring == tempstring.replace("${whenIn}", whenIn);

I find this mysterious but am glad to move on. I am a retired office worker now teacher English to adults and trying to learn to build tools.

Thanks for your response.

L.Klein

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BA7ZWLD6UBYqbLSY6pBfbe74-g%2Bv6uuLgi1_mhe9U6gRu8uAQ%40mail.gmail.com.