0

I'm currently working on a Google Sheet and want to be able to set the value of cell C1 to be the value of cell D1, and then clear cell D1 afterward.

My issue is that once I clear cell D1, cell C1 is also cleared. I've tried using SpreadsheetApp.flush() to perform these operations one by one to see if that will fix my issue, but to no avail.

I can't seem to find a solution online, so any help is very much appreciated!

Below is a snippet of my code:

function onEdit(e){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Check if the edit happened on a valid sheet.
  if (sheet.getName() == "Sheet2") {
    // Get info about the edited cell.
    var actRng = sheet.getActiveRange();
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();

    // Get the column headers from row 1.
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();

    // Set the column (by name) that will have the new sell set.
    var baseSellCol = headers[0].indexOf("Base Sell") + 1; // C1
    var newSellCol = headers[0].indexOf("Suggested Sell") +1; //D1

    // Set the "trigger" column that the user adjusts to trigger the new sell.
    var activityCol = headers[0].indexOf("Accept?") + 1; // E1

    // if E1 == "Yes" then C1 = D1 and D1.clear()
    if (baseSellCol > 0 && rowIndex > 1 && editColumn == activityCol && e.value == "Yes") {
      var base_sell = sheet.getRange(rowIndex, baseSellCol);
      var new_sell = sheet.getRange(rowIndex, newSellCol);
      var new_sell_value = new_sell.getValue();

      // Set Base Sell = Suggested Sell (C1 = D1)
      base_sell.setValue(new_sell_value);

      new_sell.clear(); // HELP: why is base_sell getting cleared when new_sell is cleared
      SpreadsheetApp.flush();
    }
    
  }
}

Here is a short video clip of how my code executes in Google Sheets: https://gyazo.com/db5038fde4e4c3c0f391ef613769d9b8

And here's a link to a copy of the sheet: https://docs.google.com/spreadsheets/d/1gbEimdeGvMXe7rTbmPJRv-iEjay0vdXMC66BfdZaGX8/edit?usp=sharing

3
  • Welcome to Web Applications Stack Exchange. I cannot reproduce the behavior you describe in a copy of your sample spreadsheet. What I see is this: put Yes in E2. The value in C2 changes to the same as that of D2. Cell D2 is then cleared of content and formatting. The value in C2 stays put. Make sure you do not have another script project with a trigger that clears C2. Jul 25 at 18:43
  • @doubleunary After reading your comment, I took your advice and took a look at both sheets to see any differences between the two sheets. Sure enough, I added a manual Trigger on the original sheet that I had forgotten about. After deleting it, my original sheet operates just like the copy I linked. Thank you!!
    – Jenna
    Jul 25 at 19:36
  • OK. Adding that as an answer. See What should I do when someone answers my question? Jul 25 at 21:54

1 Answer 1

0

The code you show does not clear cell C2, so chances are that have another script project (not shown) with a trigger that clears that cell. Remove the other trigger to sort out the issue. See installable triggers.

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.