Tags in Transactions - multiselect and data validated, but errors in the Tags Report

[kjlawless]

Hi all,
I have what I think is a scripting issue I’d love some eyes on… Admittedly, I’m very new to script editing, so I’d appreciate any help with this.

I’ve used the Tags column in my Transactions spreadsheet with data validation for some time now, and I’d like to add-on to it the ability to allow multiple tags (as opposed to just one).

I’ve used a very nice tutorial to add the multi-select feature (without repetition and for the tags column in my sheet). It works very well in the Transactions sheet. But, when I go to look at the Tags Report, something is awry. I can select a tag to filter on, and the transactions display for a brief moment and then they disappear, leaving behind a strange ‘undefined’ note. I’m wondering if there’s some conflicting code in the base template that may be causing the issue, or if the script I’ve added is faulty in some way.

Steps I’ve already taken to troubleshoot:

1. I tried restoring previous versions (and removing the script entirely) to make sure the Tags Report worked OK before I added the script. Result: Tags Report worked fine before (without the script in place).
2. Renamed the script file in Apps Script to something other than code.gs. Result: Tags Report works, but the multiselect in the Transactions sheet does not. (Yes, I did change file filename back to ‘code.gs’ and get the same result as before where the Tags Report doesn’t work, but the multiselect in Transactions does.)
3. Ran the script in the Apps Script. Result: I received an error that refers to line 7 in the script (newValue=e.value;). Error reads:
   TypeError: Cannot read property ‘value’ of undefined
   onEdit @ code.gs:7

I’m attaching a screenshot of my current Transactions sheet, the code I’ve added to the Apps Script and a screenshot of that, plus a screenshot of the strange error I’m seeing on the Tags Report.

Thanks for taking a look!

You’ll see the multiselect data validated tags in row 10 are working! Since the multiselect feature is in place, it conflicts a little with the data validation so we see a red triangle error message saying it’s an invalid entry. This is to be expected, though, per the tutorial.

Note: at first when I select a tag in the Tags Report, everything looks normal and there’s only one tag listed in the filter list. “undefined” only appears a moment later and the transactions tagged with that tag disappear. This happens for all the tags, not just one or two specific ones.

Code:

function onEdit(e) {
  var oldValue;
  var newValue;
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = ss.getActiveCell();
  if(activeCell.getColumn() == 5 && ss.getActiveSheet().getName()=="Transactions")
    newValue=e.value;
    oldValue=e.oldValue;
    if(!e.value) {
      activeCell.setValue("");
    }
    else {
    if (!e.oldValue) {
      activeCell.setValue(newValue);
    }
    else {
      if(oldValue.indexOf(newValue) <0) {
        activeCell.setValue(oldValue+','+newValue);
      }
      else {
        activeCell.setValue(oldValue);
      }
    }
  }
}

[kjlawless]

Update: I just filled sheets and ran autocat on the transactions sheet. The autocat categories stuck, but when I try to input other categories, they appear and then disappear a moment later.

[jpfieber]

Strange, sounds as if the onEdit script is running on it, though looking at the code I can’t see why unless your columns got moved around.

[kjlawless]

Columns are in the same place. Might have to abandon this, despite how much multiselect would be really helpful…

[jpfieber]

Since I use multiple tags on occasion I thought I’d try this out, and using your code, I had the exact same response. Things worked fine in the Transactions sheet (thanks, cool addition!), but the Tags Report switched up tags and added ‘,undefined’ to them. I played around with the code for a while, and looked at the original page you linked, and found the error.
Your code:
if(activeCell.getColumn() == 5 && ss.getActiveSheet().getName()=="Transactions")
Simplified version of their code:
if(activeCell.getColumn() == 3 && ss.getActiveSheet().getName()=="Sheet1") {

Your ‘IF’ statement that checks the column number and the sheet name doesn’t have brackets to enclose what to do if the condition is true. I’m surprised the editor didn’t throw an error when saving the code. Here’s what the full code should look like:

function onEdit(e) {
  var oldValue;
  var newValue;
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var activeCell = ss.getActiveCell();
  if(activeCell.getColumn() == 5 && ss.getActiveSheet().getName()=="Transactions") {
      newValue=e.value;
      oldValue=e.oldValue;
      if(!e.value) {
          activeCell.setValue("");
      } else {
          if (!e.oldValue) {
              activeCell.setValue(newValue);
          } else {
              if(oldValue.indexOf(newValue) <0) {
                  activeCell.setValue(oldValue+','+newValue);
              } else {
                  activeCell.setValue(oldValue);
              }
          }
      }
  }
}

After adding the brackets, the Tags Report works as it should, and I can multi-select tags. Thanks for pointing out this option, and putting together the code, it was much easier to add than I anticipated!

[kjlawless]

@jpfieber - I’m sorry for the slow response, as I’m just now seeing yours. Thank you so much for taking a look! I updated the code and it works perfectly. I should have known… with code it usually comes down to simple errors in syntax (granted I’m not used to writing scripts like this).

@heather and @randy - Tagging you both in case you might be interested in this quick and easy solution for adding multiple data-validated tags to transactions (despite the invalid flag in the cell)… and finding the tagged transactions easily/as expected in the Tags Report, too.