CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Pavel K
Pavel K

Reputation: 23

Replace cell value in Google Script Apps

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("");
    
    
}
}

Upvotes: 2

Views: 654

Answers (2)

Pavel K
Pavel K

Reputation: 23

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 =)

Upvotes: 0

Marios
Marios

Reputation: 27390

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.

Upvotes: 3

Related Questions