I have a problem (like everyone here, obviosly). In the sheet in the column E from row 2 (row 1 is the heading) I have to choose some text: Text1, Text2... Depending on the choice of value (it should be onEdit):
1. For Text1 for A2: E2 the font should change and in column D2 should be inserted the today's unchanging date .
2. For Text2 for A2: E2 only the font should change.
3. A third condition is also added if someone would like to withdraw the changes in the cell from column E

My script has this problem:
1. onEdit does not work
2. neither the date nor the font is inserted
3. there should be a validation of the Text in column D so that the change (onEdit) only applies to the row in which I change the cell. For example, today I choose Text1 in E2 (the font changes and sets today's date in D2), tomorrow I choose Text1 in column E3 and should be inserted today in D3 but without changing the date in earlier cells (D2 remains yesterday)

function onEdit(e) {

  var app = SpreadsheetApp;
  var activeSheet = app.getActiveSpreadsheet().getActiveSheet();

  for(var i=2;i<=10;i++) 
  for(var j=1;j<=10;j++)

    var workingCell = activeSheet.getRange(i, 5).getValue();
    var dateCell = e.activeSheet.getRange(i, 4);
    var time = new Date();
    var Cell = activeSheet.getActiveCell();

    if(workingCell === "Text1"){
    activeSheet.getRange(i, j).setFontColor("#705770");
    dateCell.setValue(new Date()).setNumberFormat("YYYY/MM/DD");

    else if(workingCell === "Text2"){
    activeSheet.getRange(i, j).setBackground("yellow");    

    activeSheet.getRange(i, j).setBackground("white");
    activeSheet.getRange(i, j).setFontColor("black");



Your Answer

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

Browse other questions tagged or ask your own question.