Error When Running Running Envelope Migrator

Good Afternoon —

I am checking out the Savings Budget (Prototype) sheet and I am enjoying many of the updates so far. I am having a problem running the migration script for the Budget Journal, however.

The categories tab migrated successfully, and I authorized the envelope migrator script as instructed. Does anyone know why it gives this error?

I’m excited you’re taking this new template for a spin, @cculber2. I can see that .toFixed() is called in line 36 of the script to render rollover values and rollover-tos into the Budget Journal sheet.

The error message reads as though what is being passed into .toFixed() is not a number. Can you scan your Budgets History sheet in the Rollover Mods and Rollovers sections to see if there are any non-numbers in those sections? (It is possible that an empty— i.e. in lieu of zero— cell is causing the issue.)

If you run the script, reach the error, then open Tools / Script Editor and click View / Execution transcript, can you send me the last line in the dialog box that appears?

We will get to the bottom of this.

Thanks for the quick reply, @randy!

I did a quick check of the Rollover and Rollover Mods sections, and I don’t see any non-numeric values, although there are some empty cells in the Rollover Mods section. Per your suggestion, I did a regex replace of empty cells in that range with 0 and re-ran the script successfully. The output contained many $0-change entries in addition to the expected changes (Note = Blank rollover-to clears rollover), but they are easy enough to ignore.

For reference, this was the execution transcript from when I was receiving the error:

[20-09-14 14:53:11:432 PDT] Starting execution
[20-09-14 14:53:11:452 PDT] SpreadsheetApp.getUi() [0 seconds]
[20-09-14 14:53:11:452 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[20-09-14 14:53:11:596 PDT] SpreadsheetApp.Spreadsheet.getSheetByName([Instructions]) [0.143 seconds]
[20-09-14 14:53:11:597 PDT] SpreadsheetApp.Sheet.getRange([E1:E2]) [0 seconds]
[20-09-14 14:53:11:722 PDT] SpreadsheetApp.Range.getValues() [0.125 seconds]
[20-09-14 14:53:11:723 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[20-09-14 14:53:11:723 PDT] SpreadsheetApp.Spreadsheet.getSheetByName([Copy of Budgets History]) [0 seconds]
[20-09-14 14:53:11:964 PDT] SpreadsheetApp.Sheet.getDataRange() [0.241 seconds]
[20-09-14 14:53:12:190 PDT] SpreadsheetApp.Range.getValues() [0.225 seconds]
[20-09-14 14:53:12:199 PDT] SpreadsheetApp.Spreadsheet.getSheetByName([Budget Journal Migrator]) [0 seconds]
[20-09-14 14:53:12:441 PDT] SpreadsheetApp.Sheet.getDataRange() [0.242 seconds]
[20-09-14 14:53:12:554 PDT] SpreadsheetApp.Range.getValues() [0.111 seconds]
[20-09-14 14:53:12:785 PDT] Execution failed: TypeError: Cannot find function toFixed in object 00. (line 36, file “SavingsBudgetMigration”) [1.12 seconds total runtime]

I think that squares away that issue. Thanks again for the nudge in the right direction!

I’d like to fix this bug today (before the webinar tomorrow).

So I’m clear, were all of the problematic empty cells only in the Rollover Mods section, @cculber2?
I.e. that wasn’t an issue in the Rollover section?
Thanks.

The Rollover section wasn’t an issue, @randy. At the time I had all categories set to roll over (I’ve since toned things down) so there were zeroes in every field that didn’t have an actual rollover, but I did a test and cleared out some of the zeroes and the script still ran successfully, so I believe the bug is only in the Rollover Mods section.

I also made some enhancements to my copy of the migrator template to respect the Hide From Reports and Track columns that you might want to consider including.

On the Instructions tab I added thee new entries in columns D and E below the other Categories column lookups.

Categories/Type, =iferror(char(64+match("Type",INDIRECT("'"&$B$12&"'!$1:$1"),0)))

Categories/Hide From Reports, =iferror(char(64+match("Hide From Reports",INDIRECT("'"&$B$12&"'!$1:$1"),0)))

Categories/Rollover To, =iferror(char(64+match("Rollover To",INDIRECT("'"&$B$12&"'!$1:$1"),0)))

On the Categories Migrator tab, I added a Track column in column E and set D1 and E1 to the following formulas.

={"Hide From Reports";ARRAYFORMULA(IF(ISBLANK($A2:$A),IFERROR(1/0),IFERROR(VLOOKUP($A2:$A,{INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E5&"$2:$"&Instructions!E5&""),INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E7&"$2:$"&Instructions!E7&"")},2,false))))}

={ "Track"; ARRAYFORMULA( IF(ISBLANK($A2:$A), IFERROR(1/0), IFERROR( IF(ISTEXT(VLOOKUP($A2:$A, { INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E5&"$2:$"&Instructions!E5&""), INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E8&"$2:$"&Instructions!E8&"") }, 2, false )),"Savings",IFERROR(1/0)) ) ) ) }

I also modified the Category formula in A1 to grab transfers after all of the budgeted categories.

={"Category";transpose(INDIRECT("'"&Instructions!B10&"'!F2:2"));QUERY(INDIRECT("'"&Instructions!B12&"'!A2:C"),"SELECT A WHERE C='Transfer'")}

These modifications set the Hide From Reports column based on what is in Copy of Categories, set the Track column to Savings whenever a rollover category is found, and append the transfer categories at the bottom.

Finally, I removed the Type formula from C2 and added a new formula in C1 based on the Group formula in B1.

={"Type";ARRAYFORMULA(IF(ISBLANK($A2:$A),IFERROR(1/0),IFERROR(VLOOKUP($A2:$A,{INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E5&"$2:$"&Instructions!E5&""),INDIRECT("'"&Instructions!B12&"'!$"&Instructions!E7&"$2:$"&Instructions!E7&"")},2,false))))}

With these modifications I can do a single copy/paste of the entire tab into the Savings Budget (Prototype) Categories tab. Before the modifications I needed to do 3 copy/paste operations and manually set Hide From Reports and Savings; one to grab A:D, one to grab F:~ (due to the missing Track column) and one to copy the transfer categories.

I hope this helps others get on board with the new template!

1 Like

Thanks for your feedback on the migrator script crash. I made the script a little smarter when handling non-number/empty-cell content. I think the crash issue should be resolved. If it is easy to rerun your dataset (with a new copy of the migrator sheet), could you give that a try, @cculber2? If it is a hassle, no worries.

I implemented all of your changes to the Categories sheet. They are insightful, well-documented, and match our best-practices. Thanks so much for all the time you put into this, @cculber2.

I’m excited to have the migration tool on a better footing prior to tomorrow’s webinar.

1 Like

You’re welcome, @randy!

I did a quick test with the new version of the migrator and it works perfectly with no errors, even with the blanks in the Rollover Mods section. I’m glad I was able to give a little back to help make things better.

1 Like

Great news. Thanks for taking a second look at the scripts & formulas.