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:
And here is the script that I used to get started importing JSON from the API:
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):
I also found this question to be not very helpful, considering the type of data that I am attempting to retrieve is a string:
What I have done so far
- Copied the example
IMPORTJSONcode into google
- 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):
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?