Custom Script in Pivot Table Value Formula = Error Loading Data forever

Question

I am very new to the Google Sheets platform, but I am not new to programming, and am comfortable creating and managing javascript code, and reading API documentation.

I am working on a side project, the goal is to use Google Sheets alongside existing microservices to generate a comprehensive Dungeons and Dragons Fifth Edition spreadsheet. It's more of a labor of love than anything. Criticism very welcome.

I am learning a lot about how information is represented in Google Sheets, and have found it relatively lacking in comparison to Microsoft Excel, until I learned about how data could be transformed using Pivot Tables.

Generally, I have an idea about how I will represent my data, I place just enough information in spreadsheet rows and columns to perform my query, and then I would like to retrieve my data from the API endpoint using a pivot table to construct the query url, and parse the JSON into the table from there.

Here is the very nice REST API that I found:

http://www.dnd5eapi.co/

REST API Example return value

And here is the script that I used to get started importing JSON from the API:

https://www.chicagocomputerclasses.com/google-sheets-import-json-importjson-function/

My Issue

When using the IMPORTJSON custom function inside of a spreadsheet, the query works fine, returning the rows of data that I would normally come to expect from the jsonpath-ish string that I pass into the function. However, when using the IMPORTJSON function in the formula for a value, the pivot table is just stuck with "Loading" and "Error: Loading Data..." forever.

I found this Stackoverflow post but the prescribed fixes didn't work for me (unless I am misunderstanding something):

https://stackoverflow.com/questions/20718931/new-google-sheets-custom-functions-sometimes-display-loading-indefinitely

I also found this question to be not very helpful, considering the type of data that I am attempting to retrieve is a string:

https://stackoverflow.com/questions/22799055/error-in-google-app-script-custom-function

What I have done so far

1. Copied the example IMPORTJSON code into google
2. Set up a couple of the initial queries and pivot tables

Here is the sheet in its current state for reference or viewing purposes (stackoverflow copy, do what you will):

https://docs.google.com/spreadsheets/d/18Gs53Enr8ckfa7bhwpbTccgOpMBlBhKHwBtUKGkuT_s/edit?usp=sharing

MY true source of confusion is that the IMPORTJSON function works for spreadsheets, I have not have had a single issue with it using it to populate a spreadsheet, the only time I have an issue with it is when I try to populate a Pivot Table Value using the IMPORTJSON function as the formula. If it works in one place, it should work in the other in theory, right?

Answer 1

Custom function doesn't work on features like Pivot Table, conditional formatting, etc. They work only when applied directly as cell formulas.

The workaround is to add an auxiliary column on the Pivot Table source data and extend the reference accordingly but it's highly recommend to optimize IMPORTJASON first in order to avoid having to repeat this custom function several times on the spreadsheet to avoid performance issues.

Related

• Can I use custom function in the 'custom formula is:' field?
• How to use user-defined functions for custom formatting?