1

I'm making a property asset management database. I have a Units sheet which allows me to view and edit unit/tenant information. A function called Unit_SaveUpdate() saves any changes in my database on another sheet.

I originally assigned Unit_SaveUpdate() to a drawing of a button but abandoned this method because if the User is editing a cell and presses the button before hitting "Enter" on the edited cell, it does not update the database. It's also not mobile compatible.

In its place, I used the checkbox method described by @TheMaster here via an installable onEdit trigger.

It turns out I'm having the exact same problem as before. If I hit "Enter" on the cell I'm editing before I click the "Save/Update" checkbox, it works perfectly. However, if I don't hit "Enter" on the cell I'm editing, and then click the "Save/Update" checkbox, nothing happens. The code doesn't execute at all. Please see code and two videos demonstrating the problem below.

I thought the checkbox method was supposed to address this exact issue. Can anyone figure out what I'm doing wrong? Thank you!

Example 1: Function Fires if User Hits "Enter" Before Checking Box:

<iframe class="embeddedObject shadow resizable" name="embedded_content" scrolling="no" frameborder="0" type="text/html" 
        style="overflow:hidden;" src="https://www.screencast.com/users/KimHopkins2598/folders/Capture/media/14cb741f-2fbb-4157-ba47-c09a7e508839/embed" height="340" width="1426" webkitallowfullscreen mozallowfullscreen allowfullscreen></iframe>

Example 2: Function Does NOT Fire if User Does NOT Hit "Enter" Before Checking Box:

<!-- copy and paste. Modify height and width if desired. -->
<iframe class="embeddedObject shadow resizable" name="embedded_content" scrolling="no" frameborder="0" type="text/html" 
        style="overflow:hidden;" src="https://www.screencast.com/users/KimHopkins2598/folders/Capture/media/3ce7b5af-b600-4f3f-8044-abe63119769e/embed" height="334" width="1420" webkitallowfullscreen mozallowfullscreen allowfullscreen></iframe>

Code:

function installedonEdit(event){
  var app = SpreadsheetApp; 
  var activeSheet = app.getActiveSpreadsheet();
  var sheet = event.source.getActiveSheet().getName();
  var editedCell = event.range.getSheet().getActiveCell();

  if(sheet == "Units"){
    var sheetUnits = activeSheet.getSheetByName('Units');
    const rg = event.range;
    const rangeA1 = rg.getA1Notation();

    //Button to Save/Update
    var SaveUpdateA1 = sheetUnits.getRange('O8').getA1Notation(); //A1 notation of Save/Update button
   

    if(rangeA1 === SaveUpdateA1 && rg.isChecked()){
      //sheetUnits.getRange('P2').setValue("Wow");
      Unit_SaveUpdate();
      rg.uncheck();
    }
  } 
}
Kim Hopkins
  • 59
  • 7
  • IMHO it's a bad idea to use code snippets to "embed" videos. The code is uncomplete as a [mcve] as it doesn't include the `Unit_SaveUpdate()` function declaration. Suggestions: 1. Remove the stack snippets, 2. Add [tag:google-sheets] and [tag:triggers] tags 3. Add a [mcve] including the execution logs and a link to a demo spreadsheet to make it easier to others to reproduce the behavior. – Rubén Oct 25 '21 at 19:42
  • Looking again, This is probably due to `rangeA1 === SaveUpdateA1` returning `false`. Try `console.log({rangeA1, SaveUpdateA1})` before checking equality. – TheMaster Oct 25 '21 at 21:16

1 Answers1

2

This works reliably with just a click on the checkbox:

function onMyEdit(e) {
  e.source.toast('entry');
  Logger.log(JSON.stringify(e));
  const sh = e.range.getSheet();
  if(sh.getName() == "Sheet0" && e.range.columnStart == 1 && e.range.rowStart == 1 && e.value == "TRUE") {
    e.range.setValue("FALSE")
    e.source.toast('flag');
  }
 }

Learn More

Cooper
  • 59,616
  • 6
  • 23
  • 54