Custom Function Working Inconistently or Returns "Unknown Function"

[Wes Lydon]

I had a lot of help years ago creating a function that would help my classroom library database work (I am a teacher). The function takes a range and creates new records when one row has a more complex set of data. Here is the Script:

function BreakBorrow(range) {

  delimiter = ";"

  targetColumn = 1

  var output2 = [];

  for(var i=0, iLen=range.length; i<iLen; i++) {

    var s = range[i][targetColumn].split(delimiter);    

    for(var j=0, jLen=s.length; j<jLen; j++) {

      var output1 = []; 

      for(var k=0, kLen=range[0].length; k<kLen; k++) {

        if(k == targetColumn) {

          output1.push(s[j]);

        } else {

          output1.push(range[i][k]);

        }

      }

      output2.push(output1);

    }    

  }

  return output2;

}

For confidentiality reasons, I can't share the database itself, but an example record looks similar to below. In the actual sheet, these are Columns B, C, D, and E — Row A is actually titled "UniqueID". The sheet is called "BooksOut":

The script takes the data in Row A and, if there is a semicolon, splits the result to look like this:

Or it would, if it were working. 

I've been using this script for years at this point. Each year I copy the database, including the scripts, and start fresh with new records. Each academic year has its own record of borrows. This is the first year I've had this problem.

A1 in that second image looks like this: =BreakBorrow(BooksOut!A1:E300)

If anyone can help, I'd really appreciate it. I'm not sure what is wrong. I've tried refreshing, removing the script and adding it again, removing the function from the sheet then adding it again, and making nonsense changes, saving, and saving hoping that any of these things my reset to what was working just fine a week ago.

It worked for about 3 minutes when I opened it this morning, then returned to perpetually Loading.

Thanks!

[Tanaike]

When I saw your showing script and your sample Spreadsheet, when "targetColumn = 1" is used, I think that no process occurs. In the case of the Spreadsheet of your image, shouldn't "targetColumn = 1" be "targetColumn = 0"? Is this related to your current issue? If I misunderstood your question, I apologize.

[Wes Lydon]

After I made my example version of the two sheets, I realized that I had omitted the "UniqueID" column in Row A which is usually hidden. (I made a mention of it above, but it was easily overlooked in the post).

The columns of each sheet are as follows:

A: UniqueID

B: Currently Out To

C: Author First

D: Author Last

E: Book Title.

So 1 is the correct target, as this is where it looks for the semicolon within "Currently Out To."

Thanks for responding. Gives me some hope that there might be help.

[Tanaike]

Thank you for replying. From your reply, how about the following sample script?

function BreakBorrow(range) {
  delimiter = ";"
  targetColumn = 1

  return range.flatMap(r =>
    r[targetColumn].split(delimiter).filter(String).map(e => {
      const temp = r.slice();
      temp[targetColumn] = e;
      return temp;
    })
  );
}

In this sample script, column "B" is split by "targetColumn = 1". If I misunderstood your expected result, I apologize.

[Wes Lydon]

In the sample sheet I created to test it, that script worked perfectly, but for some reason when I applied it to my original database, it also gives me the same problem. Continuously says "Loading..." and when I hover over A1, I get the above. Clearly something is going on besides a bad script. I tried the original script in my test sheet, and it TOO works. Crap. 

(I am very impressed by how short your version is compared to the original while still producing the correct result. Very cool.)

So... yeah. I'm not sure what the hell is going on now.

[Wes Lydon]

Okay, a new wrinkle. I seem to be able to make it have this error on purpose.

BooksOutResult is the sheet that runs BreakBorrow in A1. I take the result and create a new range from G to M (BooksOutResult!G1:M300), which includes a BorrowID in G, the "Currently Out To" data gets split across H, I, and J, then the author and book information finish out K, L, and M. Here is what this looks like on the actual sheet, with student names redacted:

On a different sheet, I have a query that uses BooksOutResult!G1:M300 as its source.  

=query(BooksOutResult!G1:M300, "select * Order by J DESC,H ASC, L ASC",1)

What is really odd is that I've created a second sheet that replicates the original BooksOutResult; the new one is titled BooksOutBreak and is exactly the same. If the query points to BooksOutResult, the BreakBorrow function on BooksOutBreak works just fine. If the query points to BooksOutBreak, the BreakBorrow function on BooksOutResult works just fine.

What the heck is happening?

[Wes Lydon]

Well... nevermind. Neither BooksOutResult nor BooksOutBreak have the "BreakBorrow" function working correctly now, and I haven't changed anything. I don't understand what is up.

[Tanaike]

Thank you for replying. My proposed script is for your initial sample situation. I apologize for this. But, unfortunately, from your 3 replies, I cannot understand your situation. By this, I cannot modify my proposed script. I apologize for this, again.

[Wes Lydon]

I'm going to go ahead and share the google sheet link. I do have student names, but because they are only first name last initial, it shouldn't be a privacy issue. If anyone can help, I've set it so that anyone that has the link can view the workbook.

https://docs.google.com/spreadsheets/d/1cEmKrDsTuhTW_sVNIX5JfP7cHqQCFG6XyEo7bl7zbbM/edit?usp=sharing

[Wes Lydon]

Here is the previous year's version, if anyone would like to compare. It is working fine here.

https://docs.google.com/spreadsheets/d/1200VAxyXfXQjHZq19vN6GyJgJICryYYKgMyv8Uee3R4/edit?usp=sharing

[Wes Lydon]

I think I figured it out? I was using BreakBorrow on a range that included headers. It used to work (despite this being poor design). I moved the BreakBorrow function to A2 and changed the range it is grabbing to A2:M and things seem to function again.

I'll follow up if this is just the thing working temporarily, as has happened a few times.

[Tanaike]

Thank you for replying. Now, I cannot understand your current question. I apologize for this. Can I ask you the detail of your current question?