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?