Migration Helper Crashing on Sorting Transactions Due to Checkboxes

HI there - I am working to move to the new budget sheet but have had some problems with the migration helper. It never seems to complete, it sticks on “sorting transactions” - I’ve let it run all night. It pulls the categories and transactions, but there are no categories in the populated transaction sheet and it never sets up the autocat tab like documented. I do have an extra column in my previous transaction sheet that is a checkbox - it seems to work around that and pull the transactions, but something is obviously hanging it. Are there any troubleshooting steps I can follow? Thanks!

@swhowiedash,

Thanks for letting us know. I’m not sure specifically of where it might be hanging, but there are a lot of configuration setting options for the migration. Can you please share a screenshot of the configuration you’re using in the Tiller Community Solutions add-on sidebar and perhaps that will help shed some light?


Hi there - thanks for the reply. Here is a screenshot of the settings. I got 5269 of 5345 transactions. The transactions have no categories. The autocat tab never gets built in the new file. I also have no balance history tab. Hopefully that helps :slight_smile:

@swhowiedash it’s really hard to say why those few transactions didn’t come through. My guess is that they have some special character the migrator doesn’t understand how to pull over or something along those lines.

The Balance History sheet is there, it’s just hidden and I believe it will only pull over manual balance history entries vs automated ones.

You can run it again and just uncheck the transactions and balance history sheets and see if it will pull over Categories and AutoCat

You could try unchecking the transactions box

I checked the crash logs, @swhowiedash. Nothing I could see there pointed to an easy resolution.

Three weeks ago there was a crash in the migration helper from trying to delete frozen rows, but I think this is too long ago to be you.

Another possibility, a user had an issue with invalid data in ~row 15. Unfortunately, the logs don’t say which sheet is having the issue or what is invalid about the data. This could be you… :man_shrugging:

I think Heather is correct that trying one sheet at a time could help isolate the problem. Can you try a few executions with just one sheet checked at a time and let us know which sheet drives the crash. For a normal sized sheet, the process should not need to run more than a minute or so… so no need to run it overnight. If it doesn’t finish after a few minutes, it has crashed.

Also, if you are using Chrome, right click in the sidebar and then open the “Inspect” window. At the top you should be able to click “Console”. Open this before running the migration helper. Before long, you will probably see a red logging message appear in the console sidebar. Please send us whatever that message says.

Thanks and sorry about the hassle.
Randy

Excellent suggestions from both of you - thanks, I will give a more granular migration a try tonight.

An update: I ran the migration helper for each section independently. I got autocat to come across, however all the categories are marked red and trying to bring the dropdown just times out before populating a list. The category sheet seems to be fine. The transaction sheet still hangs - I’ve tried about every combination of setting in there. I don’t get the top 51 rows. It still gets stuck on “sorting” - only during the transaction import. Hope that helps :slight_smile:

@swhowiedash,

Give the transactions another try, but before you do it make sure that the date column in both the source and destination are formatted as “Date” and make sure there aren’t any non-date entries in either sheet in the date column. If it’s getting stuck on sort, I’m wondering if there is some character/entry it can’t figure out how to sort in that column.

As for AutoCat if you pulled it over before you pulled over the Categories sheet, that’s why there are red triangles in the Category column since the reference for that dropdown was missing. You could try again and do the Categories sheet first, then AutoCat.

Hopefully we can get to the bottom of this soon!

Thanks - I reset all the cells in the date column to date field to confirm. I check and don’t see any aberrant dates. I deleted the autocat tab and reapplied that part of the migration - same result there. Transactions are stuck again on sorting.

Good job isolating the problem to the Transactions sheet, @swhowiedash.

As heather said, @swhowiedash, I think red triangles in AutoCat and Categories are due to data validation not updating after your one-off migrations. You will probably need to select the Category column in the AutoCat and Transactions sheet and manually set it to validate on the range of your Category column in the Categories sheet— essentially the range references in those sheets broke when you updated the sheets.

Any luck running the Inspect/Console sidebar in Chrome for any crash/debug messages?

I just rechecked the logs and did see another one of these errors: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid requests[0].deleteDimension: Sorry, it is not possible to delete all non-frozen rows. You might consider removing frozen headers in all the sheets you are working with. I can’t tell which user this happened too but it caused a crash for someone in the migrator workflow in the past 24 hours.

We are getting closer on this…
Randy

Ideally this shouldn’t happen @swhowiedash and I’m perplexed as to why it did. But if you need help resetting those ranges the help guide below has some more details (though not specific to this exact use case)

OK, I did have the top row frozen on both transaction sheets. I honestly don’t remember if they were that way - I like to do that, so was likely me. Restarted the transaction migration and it is different now: it is stuck on “Updating transactions sheet.” It added 40 blank rows and that’s where it has been for a while. Progress! :slight_smile:

Small progress. We might have been the ones to set the header frozen in our template masters. Sometimes the script code can choke when deleting all but the frozen header row. You can re freeze the header as soon as we complete the migration.

I can see a new error in the logs from the past 24 hours…
API call to sheets.spreadsheets.batchUpdate failed with error: Invalid value at 'requests[0].update_cells.rows[0].values[14].user_entered_value.string_value' (TYPE_STRING), true Invalid value at 'requests[0].update_cells.rows[1].values[14].user_entered_value.string_value' (TYPE_STRING), true Invalid value at 'requests[0].update_cells.rows[2].values[14].user_entered_value.string_value' (TYPE_STRING), true Invalid value at 'requests[0].update_cells.rows[3].values[14].user_entered_value.string_value' (TYPE_STRING), true Invalid value at 'requests[0].update_cells.rows[4].values[14].user_entered_value.string_value' (TYPE_STRING), true Invalid value at 'requests[0].update_cells.rows[5].values[14].user_entered_value.string_value' (TYPE_STRING), true Invalid value at 'requests[0].update_cells.rows[6].values[14].user_entered_value.string_value' (TYPE_STRING), true Invalid value at 'requests[0].update_cells.rows[7].values[14].user_entered_value.string_value' (TYPE_STRING), true ...

And it goes on…

I think there is an illegal value in column 14 (probably 0 index… so essentially 15) which is probably O. Can you let me know what the Transaction sheet headers are in the O column and on either side of O?

Hi there, if I understand correctly, that should be column O. Just to be safe, I’m attaching a screenshot of the top few transactions

It was actually pretty trivial to copy the transactions manually, which was my biggest priority. However, when I do that, the dropdowns in the category column are gone. I can post this as a separate question, but is there a way to either repair a transaction sheet as I see no mention of versions/updates in the sidebar for this sheet? Or, is there a way to get that column restored with the dropdown?

Sorry for going rogue on the migration - I’m willing to retry for more testing.

Peace,

Steve

One more thing of interest - I just realized that I have modified the old categories sheet with essentially a blank row at the top that I use to see if I have a positive cash flow for that month. It’s cumbersome to have to constantly switch tabs to see this information when you are entering/adjusting your budget. A snapshot is attached.

I noticed one other thing from a manual copy - check number seems to be character as they were left justified on the new sheet, while new transactions are right justified as numeric. Don’t know if the migration deals with that, but it’s one of the adjacent columns referenced above, so might be useful.

I think the outcome here was that there was a checkbox column in the Transactions sheet that was preventing the migration from succeeding?

Yes, @heather, I believe the Migration Helper workflow was choking on checkboxes in the Transactions sheet. I’ve added it to the improvements list but for now it is best to avoid checkboxes in the source sheets.