Replace cell value in Google Script Apps

Question

I am trying to write a script that, when a cell in a sheet changes "Негатив отсутствует", will replace the value of a cell in another sheet with the value "Нерелевант". Help please, what did I do wrong?

function ChangeTone(event) { 
  if (event.source.getActiveRange().getValue()=="Негатив отсутствует" && event.source.getActiveSheet()=="Разметка ТОП100 по суду"){
    var sheet = SpreadsheetApp.getActiveSheet();
    var currRow = sheet.getActiveCell().getRow();
    var value = sheet.getRange(currRow, 1).getDisplayValue();
    var pasteSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Тональность");
    var data = pasteSheet.getDataRange().getValues();
//    if(currRow > 2){
//      sheet.deleteRow(currRow);
//    }
    for(var i = 1; i<data.length;i++){
          if(data[i][1] == value){
            pasteSheet.getRange((i), 2).clear({contentsOnly: true});
            pasteSheet.getRange((i), 2).setValue('Нерелевант');
            break;
          }
  };
//    sheet.getActiveCell().setValue("");
    
    
}
}

Marios · Accepted Answer · 2020-12-23 12:20:10Z

Explanation / Issues:

Issue:

There is a clear issue with your code and in particular here:

event.source.getActiveSheet()=="Разметка ТОП100 по суду"

You are comparing a sheet object with a string and this will always return false. The correct way to do it would be:

event.source.getActiveSheet().getName()=="Разметка ТОП100 по суду"

but here I also tried to optimize your code because it is quite inefficient.

Optimization:

You don't take full advantage of the event object.

SpreadsheetApp.getActiveSpreadsheet() can be replaced by e.source.

You also define the same variables multiple times when you only need to do that once:

For event.source.getActiveSheet() and var sheet = SpreadsheetApp.getActiveSheet(); you can define a single variable to store the active sheet object and call it whenever you need it.

Last but not least. I am not quite sure about your logic regarding the for loop since you haven't mentioned it in your question.

But I see you use a for loop, an if statement and a break line to escape the for loop as soon as there is a match between the source value and the value in the paste sheet.

Instead of using a for loop, you can use findIndex to find the value that matches the criterion data[i][1] == value.
Also the full getDataRange() is not needed if you intend to use only one column, therefore I change that part too.

Solution:

function onEdit(e){
 const ss = e.source;
 const ar = e.range;
 const activeSheet = ss.getActiveSheet();
 const pasteSheet = ss.getSheetByName("Тональность");
  if (ar.getValue()=="Негатив отсутствует" && activeSheet.getName()=="Разметка ТОП100 по суду"){
    const value = activeSheet.getRange(ar.getRow(), 1).getValue();
    const data = pasteSheet.getRange('B1:B'+pasteSheet.getLastRow()).getValues().flat();
    const indx = data.findIndex((element) => element == value);
    if (indx>-1){
      const pasteRng = pasteSheet.getRange(indx+1,2);
      pasteRng.clearContent();
      pasteRng.setValue('Нерелевант');   
  }
  }
}

Let me know if that worked for you, otherwise I would like to modify it so it does.

Pavel K · Accepted Answer · 2020-12-23 16:04:06Z

Thank you very much! The script works =) I also made a working script before. But this is my first script, so it is much slower and not so ... concise. Also it was fired with a trigger, and yours works as a simple event. My old version:

function ChangeTone(event) { 
  if (event.source.getActiveRange().getValue()=="Негатив отсутствует" && event.source.getActiveSheet().getName() == "Разметка ТОП100 СУД"){
    var sheet = SpreadsheetApp.getActiveSheet();
    var currRow = sheet.getActiveCell().getRow();
    sheet.getRange("A"+currRow+":F"+currRow).setBackground('#ff5a5a');
    var value = sheet.getRange(currRow, 1).getDisplayValue();
    var pasteSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Тональность");
    var data = pasteSheet.getDataRange().getValues();
    for(var i = 1; i<data.length;i++){
          if(data[i][0] == value){
           pasteSheet.getRange((i+1), 2).setValue('Нерелевант');
           sheet.getRange("C"+currRow+":F"+currRow).deleteCells(SpreadsheetApp.Dimension.ROWS);
          }          
  };
    sheet.getRange("A"+currRow+":B"+currRow).setBackground('#ffffff'); 
}
}

So took your code and added row deletion after replacing cell value. Final version:

function onEdit(e){
 const ss = e.source;
 const ar = e.range;
 const arRow = ar.getRow();
 const activeSheet = ss.getActiveSheet();
 const pasteSheet = ss.getSheetByName("Тональность");
  if (ar.getValue()=="Негатив отсутствует" && activeSheet.getName()=="Разметка ТОП100 СУД"){
    const value = activeSheet.getRange(arRow, 1).getValue();
    const data = pasteSheet.getRange('A1:A'+pasteSheet.getLastRow()).getValues().flat();
    const indx = data.findIndex((element) => element == value);
    if (indx>-1){
      const pasteRng = pasteSheet.getRange(indx+1,2);
      pasteRng.clearContent();
      pasteRng.setValue('Нерелевант');
      activeSheet.getRange("C"+arRow+":F"+arRow).deleteCells(SpreadsheetApp.Dimension.ROWS);
  }
  }
}

Thanks again for your help =)

Collectives™ on Stack Overflow

Replace cell value in Google Script Apps

2 Answers 2

Explanation / Issues:

Solution:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Explanation / Issues:

Solution:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related