1

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.

Google Sheets stuck loading

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?

0

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

  • Wow, that really is somewhat unfortunate, as that does weaken the data model of Sheets quite considerably. Thanks for the information. I have to wonder why sum and counta functions work; in what ways do they satisfy the Formula value expected return type? I think I may attempt array formulas instead. Thanks – user205834 Jan 9 at 3:42
  • @TylerGubala SUM and COUNTA are built-in functions not custom functions, – Rubén Jan 9 at 3:57
  • Right, just musing about what, at an implementation level, causes there to be a distinction between built-in functions and custom functions. – user205834 Jan 9 at 4:05
  • 1
    @TylerGubala Built-in functions can ran when the spreadsheet is off-line but custom function doesn't. – Rubén Jan 9 at 4:08
  • That makes sense. Thanks! – user205834 Jan 9 at 4:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy